BLANK CELL IF DATED TAB DOES NOT EXIST

JuicyMusic

Board Regular
Joined
Jun 13, 2020
Messages
116
Office Version
  1. 365
Platform
  1. Windows
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:
Excel 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
 

Attachments

  • Tab Named same as column header.PNG
    Tab Named same as column header.PNG
    11.7 KB · Views: 4
  • Tab Named same as column header_2.PNG
    Tab Named same as column header_2.PNG
    4.9 KB · Views: 4

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

JuicyMusic

Board Regular
Joined
Jun 13, 2020
Messages
116
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 5, 2018
Messages
1,449
Office Version
  1. 2016
Platform
  1. Windows
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),"--")
 
Solution

JuicyMusic

Board Regular
Joined
Jun 13, 2020
Messages
116
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jun 13, 2020
Messages
116
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Thank you!
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,449
Office Version
  1. 2016
Platform
  1. Windows
Toadstool,
Could you explain the "!$A1" portion of this formula (without the quotations). I would just like to understand:sneaky:

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,689
Messages
5,637,837
Members
416,985
Latest member
mrindira

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top