Changing a workbook search function based on a cell value

Copper Contributor

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.


For example,


I want to retrieve the value from b5 from file name 165. Now I want to change the cell number from 165 to 187 to read from work ool 187.


Is this possible?


Thanks I'm advance :)

1 Reply

@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.