Excel - INDEX Function Problem - Expert Solution

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.

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.