# Get a value from a different worksheet based on the value of a cell

#### Tyron

##### Active Member
Hey Guys,

Got a tough one for you.

I have been looking all morning trying to solve this problem to no avail.

I guess most people hardcode sheet references instead of making there worksheet formulas dynamic.

Here is my situation. I have a two worksheets. The first is called TestScores. The following worksheet is called John. I plan to add a worksheet for each student.

In A2:A20 of the TestScores the value of the cell will be the name or number of the students(ex: John, Mary, ChrisC, ChrisR, 01, 02, etc.)

I would like to use the value in column A to determine the worksheet to look into while in column B it will show the value of their test score.

So in the TestScores worksheet it should look like the following:
 A B C 1 Name Score 2 01- 95 3 02- 65 4 03- 22 5 04- 18 6 05-

<tbody>
</tbody>

And in 1's worksheet it would look like:
 A B 1 Test 1 95 2 3 4 5 6

<tbody>
</tbody>

The formula I was going to use in B2:B20 was going to be something along the lines of:
Code:
``=IF((LEFT(A2,2)&"'"&!B2)=0, "Unknown",(LEFT(A2,2)&"'"!B2)``

The reason for the LEFT is to strip out the "-". I understand that the 01 is a special format, but the additional worksheet tab names are 1, 2, 3, .....10, 11, etc.

later

Ty

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### VBA Geek

##### MrExcel MVP

Excel 2013
AB
1Test120
1

Excel 2013
AB
1NameScore
201-20
Sheet1

<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B2</th><td style="text-align:left">=INDIRECT(<font color="Blue">"'" & --SUBSTITUTE(<font color="Red">A2,"-",""</font>) & "'!b1"</font>)</td></tr></tbody></table></td></tr></table><br />

#### Tyron

##### Active Member
Hey VBA Geek,

Thanks for the help. I just started using it and it works good. The only problem is if I refer to a worksheet that does not exist yet.

I was trying to use an IF statement to have it check and put the word "Unknown" in the cell when that happens, but it doesn't seem to be working out to well. Here is my formula:
Code:
``=IF(INDIRECT("'" & --SUBSTITUTE(A11, "-", "") & "'!I232")=0, "Unknown", INDIRECT("'" & --SUBSTITUTE(A11, "-", "") & "'!I232"))``

What did I do wrong? I tried also putting " ="" " and " =#REF! " for the check, but it doesn't seem to work either.

Also, Looking at your formula I see "--" before the SUBSTITUE function. Could you please tell me what it does?

My initial guess would be that it changes the value into a number?

Ty

Last edited:

#### VBA Geek

##### MrExcel MVP
Also, Looking at your formula I see "--" before the SUBSTITUE function. Could you please tell me what it does?

My initial guess would be that it changes the value into a number?

yes that is correct

Thanks for the help. I just started using it and it works good. The only problem is if I refer to a worksheet that does not exist yet.

I was trying to use an IF statement to have it check and put the word "Unknown" in the cell when that happens, but it doesn't seem to be working out to well.

trying using ISREF function

if this part returns FALSE then the sheet doesn't exist

Code:
``=ISREF("'" & --SUBSTITUTE(A2,"-","") & "'!b1")``

#### Tyron

##### Active Member
yes that is correct

trying using ISREF function

if this part returns FALSE then the sheet doesn't exist

Code:
``=ISREF("'" & --SUBSTITUTE(A2,"-","") & "'!b1")``

Hey VBA Geek,

So does that mean I could use something like the following:
Code:
``=IF(ISREF(INDIRECT("'" & --SUBSTITUTE(A2, "-", "") & "'!B1")), "Unknown", INDIRECT("'" & --SUBSTITUTE(A2, "-", "") & "'!B1"))``

I still need to return the text word "Unknown" in the event that the worksheet does not exist rather than a "0" or a "#REF!".

Ty

#### VBA Geek

##### MrExcel MVP
you need to invert the TRUE and FALSE, because if ISREF returns TRUE then the sheet exists, also you need to remove the INDIRECT function nested inside the ISREF

Hey VBA Geek,

So does that mean I could use something like the following:
Code:
``=IF(ISREF(INDIRECT("'" & --SUBSTITUTE(A2, "-", "") & "'!B1")), "Unknown", INDIRECT("'" & --SUBSTITUTE(A2, "-", "") & "'!B1"))``

I still need to return the text word "Unknown" in the event that the worksheet does not exist rather than a "0" or a "#REF!".

Ty

#### Tyron

##### Active Member
Hey VBA Geek,

Okay. So I have altered my formula and these are the results:

Code:
``=IF(ISREF(INDIRECT("'" & --SUBSTITUTE(A2, "-", "") & "'!B1")), INDIRECT("'" & --SUBSTITUTE(A2, "-", "") & "'!B1"), "Unknown")``

The above formula exists in B2:B4 at the moment and has the following desired effect:
Sheet1(TestScores) - Main Worksheet
 A B C 1 Student Score 2 01- 20 3 02- 0 4 03- Unknown 5

<tbody>
</tbody>

Sheet2(1) - Student 1:Exists and has test score
 A B 1 Test1 20 2 3

<tbody>
</tbody>

Sheet3(2) - Student 2: Exists and has NO test score
 A B 1 Test2 2

<tbody>
</tbody>

Sheet4(3) - Student 3: Worksheet does NOT Exist.

I guess it was just the fact that the "Unknown" needed to go at the end of the formula because, as you said, ISREF is trying to be TRUE first.
The indirect needs to remain as it is looking for a cell value more than a sheet. I tried taking out the INDIRECT and the formula was unsuccessful.

Thank you for all the help. This Excel Question has been SOLVED.

later

Ty

Replies
4
Views
591
Replies
1
Views
1K
Replies
8
Views
847
Replies
2
Views
175
Replies
0
Views
719

1,196,027
Messages
6,012,954
Members
441,740
Latest member
abaz21

### 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.

### Which adblocker are you using?

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

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