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.
