Auto linking data without multiple vlookups

[Machalas]

Apologies if this has been posted before but I have been looking and can't seem to find the answer or if it is possible.


What I need to know if it's possible with one formula to autofill all of the various cells in the summary sheet (copy attached) with the corresponding information in the WLA sheet without having to put individual formulas in each cell in the summary sheet - i.e. to almost read across the headers in the summary sheet and find the corresponding cell in the WLA sheet. All the referencing will be within the same spreadsheet so hopefully this will make it much easier

Thanks

 

[steve the fish]

Hi. Do you mean get the column index number via matching the headers? Yes you could use index match match or vlookup match. Cant really be more specific without you being more specific on your layout.

 

[Machalas]

Hi, thanks for your reply - the picture on the left is the summary, the picture on the right is WLA. I have headers in the summary that appear in various places in the WLA. what I didnt want to do was put loads of VLookups in and wanted to know for example if I enter the staff code can I get each of the headers to autofill in the summary sheet with the data that is located in the WLA. There are going to be lots of tabs for the WLA one for each member of staff that will pull in data from various other sheets but I wanted to know if it's possible for me to create a summary with just one formula i.e. look for the corresponding data, thanks

 

[steve the fish]

There isnt a picture. Just copy the data in excel or a representative example and paste it here.

 

[Machalas]

Hi Apologies it showed in my screen - these are the headers (examples entered) but the layout isn't the same in the other worksheet

Staff code

Name

Department

Subject Group

Grade

FTE

Total workload hours available

% allocation

Research and Scholarship

Teaching Allocation

Teaching-related Allocation

Academic Mgt & Admin

Total

TBC

John Smith

Finance

Accounts

5

1

1500

18.9

148

0

0

135

238

<colgroup><col style="mso-width-source:userset;mso-width-alt:2523;width:52pt" width="69"> <col style="mso-width-source:userset;mso-width-alt:4132;width:85pt" width="113"> <col style="mso-width-source:userset;mso-width-alt:2998;width:62pt" width="82"> <col style="mso-width-source:userset;mso-width-alt:3510;width:72pt" width="96"> <col style="mso-width-source:userset;mso-width-alt:1645;width:34pt" width="45"> <col style="mso-width-source:userset;mso-width-alt:2084;width:43pt" width="57"> <col style="mso-width-source:userset;mso-width-alt:7204;width:148pt" width="197"> <col style="mso-width-source:userset;mso-width-alt:3145;width:65pt" width="86"> <col style="mso-width-source:userset;mso-width-alt:6070;width:125pt" width="166"> <col style="mso-width-source:userset;mso-width-alt:4754;width:98pt" width="130"> <col style="mso-width-source:userset;mso-width-alt:6656;width:137pt" width="182"> <col style="mso-width-source:userset;mso-width-alt:5632;width:116pt" width="154"> <col style="mso-width-source:userset;mso-width-alt:2157;width:44pt" width="59"> </colgroup><tbody>
</tbody>

this is the layout in the other worksheet:

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Year	18/19			WLA summary	JS1
Name	John Smith			Research and Scholarship	148
Staff Code	JS1			Teaching Allocation	0.0
Department	Finance			Teaching-related Allocation	0.0
Subject Group	Accounts			Academic Mgt & Admin	135.00
Grade	5			Total	283.0
FTE	1
Total workload hours available	1500
% allocation	18.9%
Research & Scholarship	148.0
Scholarship and External Engagement	148
PG Cert HE	0
Research allocation	0
Staff PhD allocation	0
Mentoring	0	Max combined total of 252
Grading papers/REF duties	0
Designated School-wide research role(s)	0

<colgroup><col style="mso-width-source:userset;mso-width-alt:9947;width:204pt" width="272"> <col style="mso-width-source:userset;mso-width-alt:8704;width:179pt" width="238"> <col style="mso-width-source:userset;mso-width-alt:7424;width:152pt" width="203"> <col style="mso-width-source:userset;mso-width-alt:3986;width:82pt" width="109"> <col style="mso-width-source:userset;mso-width-alt:6656;width:137pt" width="182"> <col style="mso-width-source:userset;mso-width-alt:4864;width:100pt" width="133"> </colgroup><tbody>
</tbody>

thanks

 

[steve the fish]

Thats a strange layout. Is there a sheet for each person??

 

[Machalas]

Hi, yes there is a sheet per person - I was given the layout as the larger spreadsheet has already been created. I would have created it somewhat differently

 

[steve the fish]

Ok so in this instance the sheet is called 'John Smith'?

 

[Machalas]

Yes - sorry just put a basic copy on, there is the summary sheet and WLA is "John Smith"

 

[steve the fish]

So based on John Smith being typed into cell B2 of the summary you can use this. Be aware these type of formulas are slow.

=IF(ISERROR(MATCH(A$1,INDIRECT("'"&$B2&"'!$A:$A"),0)),VLOOKUP(A$1,INDIRECT("'"&$B2&"'!$E:$F"),2,0),VLOOKUP(A$1,INDIRECT("'"&$B2&"'!$A:$B"),2,0))