Excel - INDEX Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I want to dynamic access a sheet/cell from a closed file using INDEX. But when I try to "&A3&" for sheet name I forces me to select a file and then populates the sheet name instead of making it dynamically INDEX('/Users/me/Desktop/["&A2&"]"&A3&"'!A:A,1,1)
Solved by G. H. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 25/05/2018 - 04:43
Hi
User 25/05/2018 - 04:43
hello
Excelchat Expert 25/05/2018 - 04:43
Welcome to Got it Pro
Excelchat Expert 25/05/2018 - 04:43
Can you tell more about the requirement so that i can help better
User 25/05/2018 - 04:43
MY QUESTION IS ABOUT: Fixing Formulas DESCRIPTION: I want to dynamic access a sheet/cell from a closed file using INDEX. But when I try to "&A3&" for sheet name I forces me to select a file and then populates the sheet name instead of making it dynamically INDEX('/Users/me/Desktop/["&A2&"]"&A3&"'!A:A,1,1)
Excelchat Expert 25/05/2018 - 04:44
Can you share the excel that you are using so that i can understand the requirement
User 25/05/2018 - 04:44
update master sheet with data from closed files/sheets
User 25/05/2018 - 04:44
2 files
Excelchat Expert 25/05/2018 - 04:45
"&A3&"
Excelchat Expert 25/05/2018 - 04:45
what is this..why are you using this?
User 25/05/2018 - 04:45
yes that is the issue
User 25/05/2018 - 04:45
the sheet name changes
User 25/05/2018 - 04:45
in the master sheet
Excelchat Expert 25/05/2018 - 04:46
Is dynamic sheet name changes supported in excel?
User 25/05/2018 - 04:46
it is if you use INDIRECT but that doesn't work on closed files
Excelchat Expert 25/05/2018 - 04:47
Yes indirect wont work
Excelchat Expert 25/05/2018 - 04:47
I haven't come across any such case as of now
User 25/05/2018 - 04:47
Will INDEX work?
Excelchat Expert 25/05/2018 - 04:48
https://exceljet.net/formula/dynamic-worksheet-reference
Excelchat Expert 25/05/2018 - 04:48
Can you use something like this?
User 25/05/2018 - 04:49
No, the sheets need to remain closed. INDIRECT doesn't work
User 25/05/2018 - 04:51
Laster
Excelchat Expert 25/05/2018 - 04:52
hmm
Excelchat Expert 25/05/2018 - 04:52
but index works in open and same sheet as well

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.