Excel INDIRECT with references to changing cell.

[Legacy 366135]

Hi guys, I'm trying to use the INDIRECT function to auto-update a formula based on a pull-down menu from cell B9 for a given table name. This table is a list of TAB-NAMES .

key:: B9 [Pull down menu] = staff name
A table of "names" on a separate sheet 'Main' from Column A4 - A99
C3 = Year [2016]
C4 = Wk, num. [12]
C9 = =MAX(DATE(C3,1,1),DATE(C3,1,1)-WEEKDAY(DATE(C3,1,1),2)+(C4-1)*7+1)
D9 = TAB-Names = formula [=TEXT(C9,"mmm dd yyyy")]
Note that I have blank spaces with in the Tab-Names
Where a Tab-Name is something like "Mar 18 2016"

Please note the formula I'm trying to use in cell F9::

=IF(ISNA(VLOOKUP($B9,INDIRECT("'"&$D$9&"'!$A:$L"),2,FALSE)),"",VLOOKUP($B9,INDIRECT("'"&$D$9&"'!$A:$L"),2,FALSE))


whereby; the working formula would read as::

=IF(ISNA(VLOOKUP($B9,'Jan 04 2016'!$A$4:$L$76,2,FALSE)),"",VLOOKUP($B9,'Jan 04 2016'!$A$4:$L$76,2,FALSE))

However, with he working formula, I need to manually change the tab name to get the information I require.

Sorry, I'm unable to attach an image of the actual display.

 

[etaf]

that works for me
at lease the section
C9
D9
and the Vlookup works
However ISNA maybe returning an Ref error
so try

=IFERROR(VLOOKUP($B9,INDIRECT("'"&$D$9&"'!$A:$L"),2,FALSE),"")

Are you sure the Date is correctly formatted in D9 ?
have you tried just typing the value direct into D9 and see if the vlookup indirect then works ?

 

[Legacy 366135]

Perfect, the shorter form actually works.

Thank you very much...

 

[etaf]

your welcome