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))
Riny, You helped me back in 2020 with a linking issue. I have a new one that has me puzzled. I have created a link (concatenated path etc) for the various locations of workbooks stacked underneath what I would refer to as a root directory.
Proposed (root Directory)
Employee contracts (sub directory)
_Contract Details.xlsx (Workbook)
Current (Sheet1)
Business contracts
_Maintenance Details.xlsx
Office Leases
_Collier Leases.xlsx
In the example above I have concatenated the following C:\users\gerryp\documents\proposed\
What I want to do is to make a link that incorporates the concatenated link above and adds the subdirectory of Employee Contracts and adds to that the file name _Contract Details.xlsx and the sheet reference Current and the cell which I want to retrieve the value from c2.
If I could create a dynamic link it would help accomplish the ability to grab all documents in the folder "Proposed" and move it in its entirety to a thumb drive or from a thumb drive to a different hard drive location and only need to update one path in the master spreadsheet and have that change cascade for all linked workbooks inside my master spreadsheet.
I had previously just link the normal method but soon discovered that when copying everything to a thumb drive that my data links were no longer valid due to the absolute values which were created to a prior location.
Additionally if the thumb drive registered as drive E on one PC and as drive F on a different PC there was a lot of work to keep changing the locations of the linked workbooks.
Would you have any creative ideas on how to accomplish my madness????
Gerry Poppe Sorry, need to refresh my memory, Will get back later.
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.
- Thanks!!
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 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.
- To your knowledge is it possible to create a formula for an open workbook?
Gerry Poppe Sorry for being slow in understanding. INDIRECT doesn't work with references to closed workbooks, so you can't do what I believe you have in mind. Can't think of any way how to achieve what you need.
- Sorry for my poor explanation. Let me try this again. In my example of the directory tree that exists on a thumb drive I want to be able to retrieve data from the workbooks stacked under the root "proposed". I have concatenated the path to those specific files. I want to be able to create a master file in the root directory ("proposed") where I can retrieve various elements from the various files under the root. I want my formula to be able to be drug and the cell reference update as you drag the cell down or across. I cannot seem to be able to write the formula that will use my concatenated path and worksheet reference to enable me to retrieve the data from one of those stacked files below the root. The reason I want it dynamic is to eliminate the need to remap file locations when the thumb drive is moved from computer to computer.
Gerry Poppe Went through your follow-up question several times but am confused. You start by explaining that you want to "grab all documents" from one folder and move them.
Towards the end you mention that files may be in the E drive on one computer and perhaps in F on another computer. That suggests you are talking about links.
I trust you are able to concatenate the entire path (drive, folders, filename), similar to concatenating cell references withing a workbook. One of the cells referenced should hold the drive letter, where the user should enter the correct letter assigned to 'thumb' drive. You ask for "creative ideas". Not sure what I can do for you.
- Thanks,
Hopefully I have explained the current issues with the dynamic linking that I am trying to accomplish