Feeding through to a Summary Tab

[Excel_77]

Hi All,

I have a "Summary" tab that records a list of subject in column B2 onwards, in Column C1-E1 there are heading with peoples names that relate to my three sub tabs, the sub are named "David," "Mike" and "Susan".

So in C2 of the "Summary" tab, I want to look at the subject in column B, then go to the tab called "David", match the subject in column G and return the corresponding value in column H of tab "David".

Is this possible and if so how?

Thanks in advance

 

[FormR]

Hi, you could try:

=VLOOKUP($B2,INDIRECT("'"&C$1&"'!G:H"),2,0)

 

[Excel_77]

Hi, you could try:

=VLOOKUP($B2,INDIRECT("'"&C$1&"'!G:H"),2,0)

Currently returns an error, what does the term "Indirect" do?

 

[FormR]

Hi,

Currently returns an error

What is the error?

what does the term "Indirect" do?

It turns the text passed to it into a range.

https://support.office.com/en-gb/article/INDIRECT-function-474b3a3a-8a26-4f44-b491-92b6306fa261

 

[Excel_77]

Hi,



What is the error?



It turns the text passed to it into a range.

https://support.office.com/en-gb/article/INDIRECT-function-474b3a3a-8a26-4f44-b491-92b6306fa261

#Ref ! error message, I think the issue is how does it know to go to the tab called "David" or indeed the tab matching the name in the column?

 

[FormR]

how does it know to go to the tab called "David"

in Column C1-E1 there are heading with peoples names that relate to my three sub tabs, the sub are named "David," "Mike" and "Susan".

=VLOOKUP($B2,INDIRECT("'"&C$1&"'!G:H"),2,0)

Are you sure the value in C1 is exactly the same as the name of your sheet?

 

[Excel_77]

Are you sure the value in C1 is exactly the same as the name of your sheet?

In the summary tab I have moved the name "David" to cell F2, the remaining names will be input to G2 and H2 respectively.

The first tab after "Summary" is called "David".

Within the summary tab in column B are the list of subjects that I want found in column B of each tab and then the value in column C returned.

I hope this simplified version makes things clearer.

 

[FormR]

I hope this simplified version makes things clearer.

Hi, if you are having difficulty updating the formula already suggested to your set-up then it would probably work best if you described the actual cells involved rather than a simplified version.

 

[Excel_77]

Hi, if you are having difficulty updating the formula already suggested to your set-up then it would probably work best if you described the actual cells involved rather than a simplified version.

OK I'll start again, the error is on my side in how I have described things.

1. In a "Summary" tab I have a list of subjects in column B, the heading for column B "Subject" resides in B2.

2. Also within the "Summary" tab I have a row of three names in cells F2-H2.

3. I have three sub tabs each with a name, each name matches with the names in cells F2-H2 of the summary tab.

4. In column B of each of the sub tabs is a list of subjects, these will match what is in column B of the "Summary" tab, however there maybe less of them and they maybe in a different order.

What I need a formula for columns F-H of the summary tab is to find the subject listed in B2 and then locate the name in cells F-H, find the tab that has the corresponding name, find the subject name in column B and then return the value in column C, if the value is missing return "error".

 

[FormR]

Hi, the adapted formula in F3 is copied down and across as needed.

Summary Sheet:

Excel 2013/2016
	B	C	D	E	F	G	H
2	Subject				David	Mike	Susan
3	Maths				3	7	6
4	Science				1	8	Error
5	Art				2	9	5
Summary

Cell Formulas
Range	Formula
F3	=IFERROR(VLOOKUP($B3,INDIRECT("'"&F$2&"'!B:C"),2,0),"Error")

David Sheet:

Excel 2013/2016
	B	C
1
2	Science	1
3
4	Art	2
5	Maths	3
David

Susan Sheet:

Excel 2013/2016
	B	C
1
2
3
4	Maths	6
5
6
7
8
9
10	Art	5
Susan

Mike Sheet:

Excel 2013/2016
	B	C
1
2
3	Science	8
4	Maths	7
5	Art	9
6
Mike