Call Worksheet Based on Another Cell

jbiehl

Board Regular
Joined
Jul 30, 2020
Messages
59
Office Version
  1. 365
Platform
  1. Windows
Good evening!
I'm averaging numbers that meet certain criteria in another worksheet. I would like a formula to reference that worksheet, without typing the name. I'm using the *&LEFT(QH$1,FIND(" ",QH$1)-1)&"*" formula to reference the cell in worksheet "EOY_Scores" that includes the string of text before the first space, which is "L.8.6". This allows me to drag the formula through the row without having to change the code that I want it to pull.

Here's where I'm stumped. I'd like to use something similar to this to reference that worksheet. For example, RIGHT(QH1,LEN(QH1)-SEARCH(" ",QH1)) would give "EOY_Scores", but is there a way to indicate that is the name of the worksheet?

1597435916702.png


Thanks in advance!
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Have you tried:
RIGHT(QH1,LEN(QH1)-SEARCH(" ",QH1)) &"!"
 
Upvote 0
Solution
Have you tried:
RIGHT(QH1,LEN(QH1)-SEARCH(" ",QH1)) &"!"
Thanks for the quick reply. I hadn't tried that, but I just did, and it didn't work. The error is highlighting the "$1. Do you think I should put something before the row designation ($1:$1)?
I tried & but it didn't work either.
1597591519682.png
 
Upvote 0
Thanks for the quick reply. I hadn't tried that, but I just did, and it didn't work. The error is highlighting the "$1. Do you think I should put something before the row designation ($1:$1)?
I tried & but it didn't work either.
View attachment 20491

Also - after you mentioned this, I also tried adding "'" at the beginning, and at the begging/and end, and that didn't work.
So to recap, the following haven't worked.
RIGHT(QH1,LEN(QH1)-SEARCH(" ",QH1)) &"!"
RIGHT("'"&QH1,LEN(QH1)-SEARCH(" ",QH1)) &"!"
RIGHT("'"&QH1,LEN(QH1)-SEARCH(" ",QH1))&"'"&"!"
 
Upvote 0
I found the solution:
INDIRECT("'"&LEFT(SUBSTITUTE(MID(QH$1,FIND(" ",QH$1)+1)
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top