Using an Indirect formula within a IF Statement

1. Using an Indirect formula within a IF Statement

So I have workbook with sheets named for each employee in my company, and then have a sheet summarizing the data. In the summary sheet I want to say IF a certain cell in the summary matches a certain cell in the employee sheet, give me 1, if it doesn't give me 0. (I aim to replace the 1 with a hlookup afterwards, however one thing at a time). The current formula I'm using is:

=IF(INDIRECT(B6&"!D26")=E5,1,0) B6 is the employee's name, D26 is a cell in the employee sheet that will either match E5 or won't.

This however returns the #REF! error.

I have also tried:

=IF(INDIRECT("'"&\$B6&"'!D26")=E5,1,0)

Which returns the same error.

Does anyone have any ideas?

2. Re: Using an Indirect formula within a IF Statement

this works

=IF(INDIRECT("'"&\$B6&"'!D26")=E5,1,0)

so error is in B6 or Sheet named in B6 does not exist

Check for extraneous blanks in B6

3. Re: Using an Indirect formula within a IF Statement

B6 is definitely matches a sheet because this works in another cell:

=IFERROR(HLOOKUP(\$C\$2,INDIRECT("'"&\$B6&"'!\$10:\$34"),12,FALSE),0)

4. Re: Using an Indirect formula within a IF Statement

I have found the issue, thanks for your help

5. Re: Using an Indirect formula within a IF Statement

You will only get a reference error if B6 is incorrect

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

There are currently 1 users browsing this thread. (0 members and 1 guests)