Create list of Cell Data (column B) based on value in same row, dynamic column.

Hi Guys,

not sure how best to explain this, but here goes. There is an attaached sample worksheet with some made up data, however the formatting is correct.

On sheet 1, I want to enter a date (which is the monday of that specific week), then I'd like excel to use that date to find a column on 'Mgr Relief', search that column for all instances of the value "Free", and return the staff name/s from column B on Mgr Relief, to sheet 1, in a list form.

it doesn't need to be pretty, the data on Sheet 1 won't be stored, it will just be used to tell me which staff are available any given week. The end result would be find a vacancy (based on other software), open this spreadsheet, enter a monday-date, receive list of names that are available staff, fill vacancy, close spreadsheet without saving.

I'm aware I could just use 'hide' or 'filter' or just manually search my existing database (a portion of which is represented by the Mgr Relief sheet) but my actual database is huge, in both staff numbers and number of weeks present, making a manual search a pain, and I have some other formula's going on that don't like hide/filter. Also, I think this would be a much more efficient/elegant solution. Enter date, receive list of available staff, done and dusted.

Thanks guys!

Sample Workbook.xlsx

I'm about to duck into a meeting but I will try this when i get back.

my mistake regarding the "free". Normally the formula that populates the roster automatically fills non-manager-roster-codes with 'Free". For the purpose of the sample workbook I deleted the formula (for data sensitivity reasons, it contains url's etc), but forgot to manually enter "free" into the non-Mgr cells. They will normally be populated with the text "free".

If the formula matters, a data-scrubbed version of it can be seen here:

http://www.excelforum.com/excel-form...me-values.html

Thanks for the help Max! Mgr actually marks a staff member already working in a management role, and so are not available to fill a shift. blank (eventually cells with the value "free" in them) denote staff available to backfill management shifts.

Reply to post #5

Aha ... If that's the case, then use this instead in B3, copied down:
=IF(OFFSET('Mgr Relief'!$D$1,ROWS($1:1),MATCH($B$1,'Mgr Relief'!$E$1:$AI$1,0))="Free","Free","")
----------------------------------
Any good? Wave it, whack the little star at the bottom left of my responses

Thread marked as solved! thanks for the amazing help guys!

I ended up going with Benishiryo's solution, only because the database is for my upper management, who like things to look simple. They don't care how complicated it is underneath, only that its simple to look at it, and there were less columns on the visible page (front sheet) with Benishiryo's solution.

For anyone who stumbles across this post in years to come, Max's answer works too guys!

Thanks for the amazing help as always!

.. welcome, and thanks for the feedback