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

[Tyron]

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:

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

Thanks in advance for your help

later

Ty

 

[VBA Geek]

Excel 2013
	A	B
1	Test1	20
1

Excel 2013
	A	B
1	Name	Score
2	01-	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]

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:

=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?

thanks in advance

Ty

 

[VBA Geek]

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

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

 

[Tyron]

ADVERTISEMENT

yes that is correct


trying using ISREF function

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

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

Hey VBA Geek,

So does that mean I could use something like the following:

=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!".

thanks in advance

Ty

 

[VBA Geek]

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:

=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!".

thanks in advance

Ty

 

[Tyron]

Hey VBA Geek,

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

=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