Forum Discussion
Dynamic cell reference to workbooks using IF statement
First of all, I would include the single quotes and the exclamation mark when you concatenate the sheet name you want to call with the INDIRECT function. For example =CONCAT("'"&B1&" "&B2&"'!") to produce something like 'Sheet Name'! (where B1 contains "Sheet" and B2 contains "Name"). Better to do that once than every time you use INDIRECT. Then, the formula itself seems overly complicated for what you want to do.
Your first VLOOKUP could be replaced by:
IF(INDIRECT($E$1&"A28")=1
and your second one by:
IF(INDIRECT($E$1&"A28")=100
Similarly, the parts directly after each IF statement can be simplified to: INDIRECT($E$1&"B28")
But then, the logic of the formula says: If a value equals 1 then return the value in 'Sheet Name'!B28. If it is not then see if it equals 100 and if so, then also also return the value from 'Sheet Name'!B28". Otherwise, return 0.
The same logic would also be achieved by this:
=IF(OR(INDIRECT(E1&"A28")=1,INDIRECT(E1&"A28")=100),INDIRECT(E1&"B28"),0)If you insist to work off your original formula (with some small modifications), this one will work as well and it will have eliminated the #NA! error.
=IF(IFERROR(VLOOKUP(1,(INDIRECT("'"&$E$1&"'!A28")),1,FALSE),0)=1,INDIRECT("'"&$E$1&"'!b28"),IF(IFERROR(VLOOKUP(100,INDIRECT("'"&$E$1&"'!A28"),1,FALSE),0)=100,INDIRECT("'"&$E$1&"'!b28"),0))
Gerry Poppe Not sure if this is helpful. but let's say Sheet2 has data like in the picture below:
Then you could us a formula, any where else in the workbook to dynamically pick-up these numbers with this formula:
=INDIRECT("Sheet2!"&ADDRESS(ROW(A1),COLUMN(A1)))The ADDRESS function basically returns the representation of "A1" to INDIRECT. Drag the formula down and it will become A2. Drag to the right and it will become B1 and so on.
In another sheet I entered the formula in D6 (dragged down and across) and it looks like this:
Just put the concatenated file and sheet name of the open workbook in stead of Sheet2! and it should work.
Here is what I have for my concatenated formula
=(CONCATENATE('READ ME FIRST (INDEX)'!$A$7,"\",'Employee Contracts'!$F$4,'Employee Contracts'!F6,"\Current!"))
This is what the concatenation generates
C:\Users\gerryp\Documents\proposed\Employee Contracts\_Contract Details.xlsx\Current!
I created a Named value "DATA" for the cell where the above value was placed.
I then created, following your example, the following formula.
=INDIRECT("DATA" &ADDRESS(ROW(A2),COLUMN(C3)))
With the file "_Contract Details" open in Excel I was expecting results from cell C2 in the "Current" worksheet.
Is the placement of the ! mark in the formula an issue? Or is an issue with the file path being concatenated together with the file name?
Gerry Poppe But if the workbook is open you would NOT include the entire file path. And now I understand that "DATA" is a named range in the targeted workbook. Perhaps I've lost you completely, but again, INDIRECT works only on open workbooks. Create a link to the "DATA" in the other (open) workbook and replicate what you see in that link in text which you can use with an INDIRECT function.
I noted that when I am in edit mode that it appears to be referencing cells in my worksheet even though the reference to the "DATA" is to an open external workbook.
C:\Users\gerryp\Documents\proposed\Employee Contracts\_Contract Details.xlsx\Current! is named "DATA" Gerry Poppe If DATA is a named range you should not use the quote marks around it. With them you just create a path like "DATAC2". So it should work if remover the quote marks. And yes the ! has to be in the right place is important.