BLANK CELL IF DATED TAB DOES NOT EXIST

[JuicyMusic]

Hello experts,

I would like to add a condition to an existing formula. My existing formula points at the header name in it's corresponding column - then looks for a tab with the same name within the workbook. If there is a tab within the workbook with the same name as the column, then the result in the cell will be an employee name. If it DOES find a tab with the same name BUT a particular employee name is found no where on that tab - then it returns the text "Off/Vacation".

The problem with this existing formula is that if the formula does not locate a tab named the same as the column header - then it will return the employees name (which is based on a vlookup). This is all good so far.

Then the formula will return the employee name in the cell if it doesn't not locate it's "corresponding" named tab. I need the "if tab name not found" portion of this formula to return back two dashes. Like this, "--" (without the quotation marks).

EXISTING FORMULA:

=IF(ISNA(VLOOKUP("*"&$C9&"*",INDIRECT("'"&E$8&"'!$d$6:$d$3056"),1,0)=FALSE),"Off/Vacation",$C9)

Additional information FYI: This spreadsheet will have 30 or 31 daily job tabs by the end of each month. It is to track what days field employees worked, or were out, or on vacation.

TAB NAME FORMAT EXAMPLES: Tuesday120120, Friday120420, Saturday120520, Sunday120620 (I skipped some days for this post). There will be 30 or 31 tabs by the end of each month.

EMPLOYEE NAMES: In column A thru C
DATED COLUMN HEADERS: Starts in column E thru AI, all on row 8. One column for each day. Header text format example: "Tuesday120120".


Thank you,
Juicy

 

[JuicyMusic]

The problem with this existing formula is that if the formula does not locate a tab named the same as the column header - then it will return the employees name (which is based on a vlookup). This is all good so far.

I didn't mean to write that "this is all good so far". The rest of my post is accurate.

 

[Toadstool]

Hi JuicyMusic,

How about

=IF(ISREF(INDIRECT("'"&E$8&"'!$A1")),IF(ISNA(VLOOKUP("*"&$C9&"*",INDIRECT("'"&E$8&"'!$d$6:$d$3056"),1,0)=FALSE),"Off/Vacation",$C9),"--")

 

[JuicyMusic]

The problem with this existing formula is that if the formula does not locate a tab named the same as the column header - then it will return the employees name (which is based on a vlookup). This is all good so far.

I didn't mean to write that "this is all good so far".

Hi JuicyMusic,

How about

=IF(ISREF(INDIRECT("'"&E$8&"'!$A1")),IF(ISNA(VLOOKUP("*"&$C9&"*",INDIRECT("'"&E$8&"'!$d$6:$d$3056"),1,0)=FALSE),"Off/Vacation",$C9),"--")

Toadstool, SOLVED! Thank you so much. What a good looking formula.

 

[JuicyMusic]

Toadstool,
Could you explain the "!$A1" portion of this formula (without the quotations). I would just like to understand

Thank you!

 

[Toadstool]

Toadstool,
Could you explain the "!$A1" portion of this formula (without the quotations). I would just like to understand

Thank you!

It's taking your INDIRECT logic and just looking into cell A1 of that sheet. If it exists then it performs your VLOOKUP but if it fails then it must be a #REF because the tab doesn't exist so you get the "--" returned.

 

[JuicyMusic]

Understood! Thank you.