Excel: Get Column values joined by Comma matching a user count

user_13315788 0
Viewed 45

I have a worksheet named "days" which has a Column C having workingdays of a particular month ( 1 to 31 ). enter image description here

I have another worksheet sheet named "allowance" where user enters the Days count. For example if count 24 ,Get first 24 values from Column C in days sheet and join them by Comma . Like below :

enter image description here

I am able to generate them manually by using TEXTJOIN to join values

=TEXTJOIN(",",TRUE,days!C2:days!C25) -------Get First 24 Values from Column C and join by Comma ..

How can i get the list generated when user changes the count in Column ..

If user puts count as 15 .. i want =TEXTJOIN(",",TRUE,days!C2:days!C16)

Thanks ..

excel-formula excel
2 Answers

Three things I'd like to mention:

  • The concatenated string seems to spill over into a next month's workdays. That would mean that column C:C does not hold the workdays for just a single month, right?
  • All working-days seem to be 5 days in a row with a weekend in-between. I'd like to assume that the given sequence of workdays equals monday-friday and is not some custom range of days;
  • If you have workdays in column C:C it would mean you have some sort of start-date you want to work from? In that case, I'd skip the idea of having these days seperate and generate the answer directly, see below:

enter image description here

Formula in D2:

=TEXTJOIN(",",,DAY(WORKDAY(B1,SEQUENCE(B2,,0))))

Note: Make sure the start-date is an actual working-day.

Edit: In case the above does not apply to you and you do just want to pick a certain amount of rows from a range, try either:

=TEXTJOIN(",",,CHOOSEROWS(<YourRange>,SEQUENCE(24)))

Or:

=TEXTJOIN(",",,INDEX(<YourRange>,SEQUENCE(24)))
user_97581940

THanks @jvDV . I am closing this thread as asked.

This worked for me .. =TEXTJOIN(",",,DAY(WORKDAY(B1,SEQUENCE(B2,,0))))

user_133157880
Related