Question description:
This user has given permission to use the problem statement for this
blog.
Trying to get the sheet name in the formula below to change based on the text in a specific cell. So in the example below, if the text in cell A1 is currently "Sheet 1" and i change it to "New Sheet Name", the formula below would read that and replace 'Sheet 1'! with 'New Sheet Name'!
=IFERROR(INDEX('Sheet 1'!$G$8:$G$2000, MATCH(0,COUNTIF(O$2:O3, 'Sheet 1'!$G$8:$G$2000), 0)),"")
Solved by E. W. in 31 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
15/06/2018 - 01:55
Hi…Welcome to Got It Pro
User
15/06/2018 - 01:55
Hi, thanks
Excelchat Expert
15/06/2018 - 01:56
So your sheet name is stored in a cell ?
User
15/06/2018 - 01:57
yes
Excelchat Expert
15/06/2018 - 01:57
Is cell B1 where you are storing sheet name?
User
15/06/2018 - 01:58
yes
Excelchat Expert
15/06/2018 - 01:58
Got you...let me amend your formula
User
15/06/2018 - 02:01
I pasted the formula in B5
Excelchat Expert
15/06/2018 - 02:01
o9k
Excelchat Expert
15/06/2018 - 02:01
ok
User
15/06/2018 - 02:03
instead of saying sheet 1 in the formula, i want it to reference whatever is in B1
Excelchat Expert
15/06/2018 - 02:03
yes
Excelchat Expert
15/06/2018 - 02:08
=IFERROR(INDEX(INDIRECT("'"&$B$1&"'!"&"$G$8:$G$2000"), MATCH(0,COUNTIF(O$2:O3, INDIRECT("'"&$B$1&"'!"&"$G$8:$G$2000")), 0)),"")
Excelchat Expert
15/06/2018 - 02:08
Please test this formula in your original sheet.
User
15/06/2018 - 02:11
give me a minute
Excelchat Expert
15/06/2018 - 02:12
sure
User
15/06/2018 - 02:19
not working for me
Excelchat Expert
15/06/2018 - 02:20
Is it giving any error?
Excelchat Expert
15/06/2018 - 02:21
please note your formula is an array formula
Excelchat Expert
15/06/2018 - 02:21
So you will have to hit Control+Shift+Enter
User
15/06/2018 - 02:22
yes. on seconde
User
15/06/2018 - 02:25
it works
User
15/06/2018 - 02:25
you're awesome
Excelchat Expert
15/06/2018 - 02:25
Perfect.
Excelchat Expert
15/06/2018 - 02:25
Is there anything else I can help you with this query?
User
15/06/2018 - 02:26
that was it for today. great service
Excelchat Expert
15/06/2018 - 02:26
Please do give me a good feedback for my service.
Excelchat Expert
15/06/2018 - 02:26
Thanks for your time. Please do come back for any new question. Have a great weekend!
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.
