Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic List of names?
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic List of names?
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!* "RJJ" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic List of names?
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 "Luke M" wrote: 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!* "RJJ" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
return a list of names, from a large list of repeated names. | Excel Worksheet Functions | |||
Sorting and matching rows of names with Socials with master list and eliminating the extra names | Excel Worksheet Functions | |||
create a random list of names from a list of names in EXCEL | Excel Worksheet Functions | |||
filter 400 names from list 1 from list 2 with 4000 names | Excel Worksheet Functions | |||
find names on list 1 in list 2. list 1 4000 names list 2 400 name | Excel Worksheet Functions |