TURN VLOOKUP INTO INDIRECT VLOOKUP

[JuicyMusic]

Hi, I've tried and tried and it just doesn't work. I'm certain that what I need is extremely simple. Please help me adjust my existing formula.
I will show you one formula that works great, and the other will be the one that I need to tweak.

I need the second formula to work even though the tab name (example: Thursday120320) does not exist. ISREF as well please.
FYI - Sometimes the tab that the VLOOKUP is looking for exists or does not exist because there was no labor performed on that day. The first formula works even if tab "Thursday120320" does not exist within the workbook.

Here is my excellent formula as a reference:

=IF(ISREF(INDIRECT("'"&K$8&"'!$A1")),IF(ISNA(VLOOKUP("*"&$C9&"*",INDIRECT("'"&K$8&"'!m6:m3056"),1,0)=FALSE),"Off/Vacation",$C9),"--")

Here is my formula that I need to tweak in one column to the right of the 1st formula:

=IF(OR(K9="Off/Vacation",K9="--"),"--",
VLOOKUP("*"&$C9&"*",Thursday120320!$A:$B,2,0))

Thank so much, Juicy!

 

[Fluff]

In what way doesn't the 2nd formula work?

 

[JuicyMusic]

Hello Fluff, thank you for responding. I tried to install the XLLB thing, but my company computer security system blocked it. I'm sorry.

I should let you know that this is a monthly spreadsheet that will eventually have 30 or 31 tabs. The tab names will change every month.
For example January tabs will be named like this: Monday011121, Tuesday011221, Wednesday011321, Thursday011421, and so on to the last day of that month.

The 2nd formula in my December file DOES work if the tab named Thursday120320 exists or not. (I'm only giving you this one date but there are 31 columns that will have this formula)
The problem is when it is a new month and the past dated tabs (December tabs) are deleted from the workbook - and then the current month daily tabs are inserted into the workbook.

The 2nd formula will give me back a "--" if an employee name is not found because it actually is still looking for a tab named Thursday120320.

The 1st formula actually looks for a tab that has the same name as the column header - See the K$8 portion of the formula. K8 is the header name of the column. I've circled it in red text for you on the images uploaded.

=IF(ISREF(INDIRECT("'"&[COLOR=rgb(184, 49, 47)]K$8[/COLOR]&"'!$A1")),IF(ISNA(VLOOKUP("*"&$C9&"*",INDIRECT("'"&[COLOR=rgb(184, 49, 47)]K$8[/COLOR]&"'!m6:m3056"),1,0)=FALSE),"Off/Vacation",$C9),"--")

Is it possible for my 2nd formula to look at the same header name (K8) AND look for a tab with the same name while keeping the second part of the VLOOKUP intact?
Meaning keeping the $A:$B,2,0 portion as it is.

=IF(OR(K9="Off/Vacation",K9="--"),"--", VLOOKUP("*"&$C9&"*",Thursday120320![COLOR=rgb(184, 49, 47)]$A:$B,2,0[/COLOR]))

 

[Fluff]

You can use indirect on it like

=IF(OR(K9="Off/Vacation",K9="--"),"--",VLOOKUP("*"&$C9&"*",INDIRECT(K8&"!$A:$B"),2,0))

 

[JuicyMusic]

SOLVED, of course......It just took adding "INDIRECT(K8&" ? I knew it would be simple, but not THAT simple. ?
Sorry for gushing over you...….I thank you. I learn everything you've shown me on all my posts for future projects. Thank you!!

 

[Fluff]

You're welcome & thanks for the feedback.