Hello & Greetings!
This is my first post & introduction to the forum.
I'm making a spreadsheet with the following data:
B1= File Path
B2= File Name
B3= Sheet Name
B4= Reference Cell No.
=INDEX('File Path[File Name.xlsx]Sheet Name'!$C31,1,1)
When I use INDIRECT function with these cell references, it works fine until the file is open. Hence I've changed it to INDEX function. It works fine if I manually enter an 'entire path with the cell reference' as array & 1,1 as row and columns.
Can anyone will be able to suggest how I can introduce cell references in the formula with any of the function (e.g. INDEX, INDIRECT, SUM, MATCH) to work when the file is closed.
Thanks,
Vivek Khandekar
India
Hi Vivek,
This is a topic I am not that familiar with, I simply avoid to reference to other files unless working with Power Query.
I found this article though, that should give you some guidance, I hope.
Br,
Anders
Hi Vivek
As far as I am aware, you cannot use INDIRECT with a closed file.
You will need a macro that auto open the required file (when you open your source file), grab the data and then close that file.
Attached is an example.
It uses a Workbook Open event.
Hope this helps.
Sunny
