Forum Discussion
Changing a workbook search function based on a cell value
Hi Everyone. I am trying to create a tracked for some of our reports where I need to pull our different bits of information. As we use a standard spreadsheet, all the info is on the same location...
Jaybo121 I think you will need to use a VBA macro to do this.
The INDIRECT function will let you construct the reference, but I don't think it will actually get you a result. I think it will give a #REF! error.
To try with INDIRECT you would do something like this:
=INDIRECT("[file name " & A1 & ".xlsx]Sheet1'!B5"), assuming that the file number is in cell A1.
To use VBA, you could have a macro that would take the value from A1 (the file number), and then it would create the formula to get the value from B5 of that file.
There are some simple examples in this article - Range.Formula
The other option is to just change the link using the Links feature of Excel. This article has the information you need - Manage Links.
Look at the section called "Change the source workbook". If you have an older version of Excel, you might need to look at the section called "the Edit Links dialog box" which shows how you can pick a different source file and it will update the link.
Of course, if you're only getting one value from the other workbook, you can just manually update the formula each time.
