I have searched the forum for a suitable technique to solve this problem and I can find nothing that helps.
I have a spreadsheet that tracks a large number of staff and changes to staff status.
worksheet 1 contains the core data and tracks changes on a week-by-week basis (each week starting on a Monday) as follows:
In worksheet 2, I want to duplicate/display the values from worksheet 1 columns A:E if the column (F:N) that represents today's date contains a value (ie isn't a blank cell). For example, if I open worksheet 2 on 6th Dec, I would expect the results to look as follows:
If I opened worksheet 2 on 27-Nov I would expect the results to look like this:
I have been able to get the worksheet 2 to find the correct date column in worksheet 1 via a combination of Today() and a simple formula and also use HLookup to get a column number but each way I've tried to then return values from the first basic info columns fails.
The overall objective is to provide admin staff with a simple "snapshot" list of staff as of "today". Any help would be appreciated.
I have a spreadsheet that tracks a large number of staff and changes to staff status.
worksheet 1 contains the core data and tracks changes on a week-by-week basis (each week starting on a Monday) as follows:
In worksheet 2, I want to duplicate/display the values from worksheet 1 columns A:E if the column (F:N) that represents today's date contains a value (ie isn't a blank cell). For example, if I open worksheet 2 on 6th Dec, I would expect the results to look as follows:
If I opened worksheet 2 on 27-Nov I would expect the results to look like this:
I have been able to get the worksheet 2 to find the correct date column in worksheet 1 via a combination of Today() and a simple formula and also use HLookup to get a column number but each way I've tried to then return values from the first basic info columns fails.
The overall objective is to provide admin staff with a simple "snapshot" list of staff as of "today". Any help would be appreciated.
