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

 

[Excel_77]

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
Subject	David	Mike	Susan
Maths
Science	Error
Art

<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]5[/TD]

</tbody>

Summary​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($B3,INDIRECT("'"&F$2&"'!B:C"),2,0),"Error")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]




David Sheet:

Excel 2013/2016

	B	C
Science
Art
Maths

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]3[/TD]

</tbody>

David​

Susan Sheet:

Excel 2013/2016

	B	C
Maths
Art

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]6[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]5[/TD]

</tbody>

Susan​

Mike Sheet:

Excel 2013/2016

	B	C
Science
Maths
Art

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]8[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]7[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]9[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Mike​

Exactly what I want it do do, however it returned the #ref error message.

 

[FormR]

it returned the #ref error message.

Same question as post#6 - are you sure that the value in F2 exactly matches the name of your sheet?

 

[Excel_77]

Same question as post#6 - are you sure that the value in F2 exactly matches the name of your sheet?

For some reason this morning the code has decided to work and work perfectly.

I have one final query, if the cell being returned is blank I am currently getting "0", could this be changed to the word "No" instead?

 

[FormR]

I have one final query, if the cell being returned is blank I am currently getting "0", could this be changed to the word "No" instead?

Hi, what data type is the value that is being returned from column C - is it always a number or text or a combination of both?

 

[Excel_77]

Hi, what data type is the value that is being returned from column C - is it always a number or text or a combination of both?

It will always be text.

 

[FormR]

OK - then I think we'll need to go the long way:

=IFERROR(IF(VLOOKUP($B3,INDIRECT("'"&F$2&"'!B:C"),2,0)="","No",VLOOKUP($B3,INDIRECT("'"&F$2&"'!B:C"),2,0)),"Error")

 

[Excel_77]

OK - then I think we'll need to go the long way:

=IFERROR(IF(VLOOKUP($B3,INDIRECT("'"&F$2&"'!B:C"),2,0)="","No",VLOOKUP($B3,INDIRECT("'"&F$2&"'!B:C"),2,0)),"Error")

Final question, that works, however when I drag the formula across to cells G and H "Error" is returned, why would this be?

 

[FormR]

Final question, that works, however when I drag the formula across to cells G and H "Error" is returned, why would this be?

Maybe the values in G2, H2 etc don't exactly match the name of your sheets?

 

[Excel_77]

Maybe the values in G2, H2 etc don't exactly match the name of your sheets?

Just deleted and typed in again, both match but error is returned.

 

[FormR]

Just deleted and typed in again, both match but error is returned.

If you remove the IFERROR() wrapper what is the error that is being returned?

EDIT: Maybe if you wait until tomorrow it will start working