Dynamic List of names?

[RJJ]

I have a calendar on a sheet. Under each date of the month, there are various
names that are updated and changed frequently (vacation schedule). The names
change from day to day or week to week as needed. From a different workbook
(linked of course), I need a matching list of names for the day in question.
"TODAY" would be the day in question. In other words, I want to see the
people who are on vacation the same day that I am viewing this other workbook
(happens to be a daily schedule). The point here is that I don't want to open
the other workbook each day to see who is scheduled off.

Really appreciate any help.

 

[Luke M]

One way to do it:

Complicated explaination, but I'll try to take it step by step.
Example formula:
=OFFSET($B$2,1,MATCH(A3,C2:E2,0),COUNTA(OFFSET($B$2,1,MATCH(A3,C2:E2,0),10,1)),1)

$B$2 - This is the cell that is just to the left of your first date heading
in your reference sheet
A3 - This is where you have today's date [or replace with TODAY() ]
C2:E2 - This should be the range of your date headings
10 - Change this number to max number of employees that can be off

Once you have your formula changed to what it needs to be, select an range
of cells equal in in length to max number of employees that can be off. Now,
with those cells still selected input your formula, but use Ctrl+Shift+Enter
to confirm (not just enter). The formula should now be entered as an array
across all the cells you selected. The extra spaces will display N/A.

 

[RJJ]

Luke,

You refer to date headings as C2:E2. On my sheet, (5/1 is F3), (5/2 is G3),
(5/3 is A11), (5/4 is B11), (5/10 is A19), (5/17 is A27), (5/24 is A36), and
5/31 is A44). In addition, each month is on a different sheet (JanFeb, Mar),
and so on. I realize this only complicates things but I again appreciate your
help. Also, we are only allowed 7 people maximum to be scheduled on any day.
That is the reason for the current spacing between the days.

Richard

One way to do it:

Complicated explaination, but I'll try to take it step by step.
Example formula:
=OFFSET($B$2,1,MATCH(A3,C2:E2,0),COUNTA(OFFSET($B$2,1,MATCH(A3,C2:E2,0),10,1)),1)

$B$2 - This is the cell that is just to the left of your first date heading
in your reference sheet
A3 - This is where you have today's date [or replace with TODAY() ]
C2:E2 - This should be the range of your date headings
10 - Change this number to max number of employees that can be off

Once you have your formula changed to what it needs to be, select an range
of cells equal in in length to max number of employees that can be off. Now,
with those cells still selected input your formula, but use Ctrl+Shift+Enter
to confirm (not just enter). The formula should now be entered as an array
across all the cells you selected. The extra spaces will display N/A.


--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

I have a calendar on a sheet. Under each date of the month, there are various
names that are updated and changed frequently (vacation schedule). The names
change from day to day or week to week as needed. From a different workbook
(linked of course), I need a matching list of names for the day in question.
"TODAY" would be the day in question. In other words, I want to see the
people who are on vacation the same day that I am viewing this other workbook
(happens to be a daily schedule). The point here is that I don't want to open
the other workbook each day to see who is scheduled off.

Really appreciate any help.