Mar 27 2020 12:24 PM
I have a workbook which contains multiple years of data. Each year is in a separate worksheet. I want to be able to use a dynamic link which would allow me to gather data from anyone of the worksheets based upon a value.
=IF((VLOOKUP(1,(INDIRECT("'"&$E$1&"'!A28")),1,FALSE))=1,INDIRECT("'"&$E$1&"'!b28"),IFERROR(IF(VLOOKUP(100,INDIRECT("'"&$E$1&"'!A28"),1,FALSE)=100,INDIRECT("'"&$E$1&"'!b28"),0),))
In the equation $E$1 is a concatenated field that changes based upon criteria. The concatenated value is the worksheet tab title.
In the above equation I can separate the two if statements into two stand alone statements and they will both work independently. Unfortunately when I place them into a single statement it fails and reports a #N/A error.
Possibly there is a better formula that will allow the lookup access. The other frustration is that to copy the formula down in the cells I manually have to adjust the rows as there does not appear to be a functional way to increment the row number when using the INDIRECT command.
Any guidance would be appreciated.
Mar 28 2020 12:37 AM
SolutionFirst 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))
Mar 30 2020 06:23 AM
Mar 30 2020 07:32 AM
Try this:
=IF(OR(OFFSET(INDIRECT($E$1&"A28"),ROW(A1)-1,0)=1,OFFSET(INDIRECT($E$1&"A28"),ROW(A1)-1,0)=100),OFFSET(INDIRECT($E$1&"B28"),ROW(A1)-1,0),0)
It becomes a bit less clear, but it works. We still look at the "fixed" cell A28, but offset it by 1 row down for every cell we copy down the formula. That's done by the "ROW(A1)-1" part in the formula.
But, be aware that extensive use of the OFFSET function may slow down responsiveness of Excel.
The attached workbook includes a working example. I trust it helps you in implementing it in your own sheet.
Mar 30 2020 01:43 PM
Mar 30 2020 09:56 PM
@Gerry Poppe Can't tell if you don't tell me the name of the sheet and show exactly the content of the cell that you call with INDIRECT. Can be a missing apostrophe (or one too many) or a space that should or should not be there. Or any other small typo. Can not imagine it to be specific to the name of one sheet alone. If Excel allowed you to (re)name it, you should be able call for it in any function.
Mar 31 2020 05:04 AM
Mar 31 2020 05:34 AM
@Gerry Poppe Tried, but could not replicate the problem. Have a look at the attached sheet and see if it resembles your formulae.
May 18 2022 11:38 AM
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????
May 18 2022 12:08 PM
@Gerry Poppe Sorry, need to refresh my memory, Will get back later.
May 18 2022 12:09 PM
May 18 2022 10:45 PM
@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.
May 19 2022 04:18 AM
May 19 2022 04:57 AM
@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.
May 19 2022 05:56 AM
May 19 2022 06:19 AM
@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.
May 19 2022 06:41 AM
May 19 2022 07:47 AM - edited May 19 2022 07:48 AM
@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.
May 19 2022 08:43 AM
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" |
May 19 2022 10:33 AM
@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.
Mar 28 2020 12:37 AM
SolutionFirst 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))