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

John Smith	Finance	Accounts

<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>
[TD="class: xl65, width: 69"]Staff code[/TD]
[TD="class: xl66, width: 113"]Name[/TD]
[TD="class: xl66, width: 82"]Department[/TD]
[TD="class: xl66, width: 96"]Subject Group[/TD]
[TD="class: xl66, width: 45"]Grade[/TD]
[TD="class: xl66, width: 57"]FTE[/TD]
[TD="class: xl66, width: 197"]Total workload hours available[/TD]
[TD="class: xl66, width: 86"]% allocation[/TD]
[TD="class: xl66, width: 166"]Research and Scholarship[/TD]
[TD="class: xl66, width: 130"]Teaching Allocation[/TD]
[TD="class: xl66, width: 182"]Teaching-related Allocation[/TD]
[TD="class: xl66, width: 154"]Academic Mgt & Admin[/TD]
[TD="class: xl67, width: 59"]Total[/TD]

[TD="class: xl68"]TBC[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]18.9[/TD]
[TD="align: right"]148[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]135[/TD]
[TD="align: right"]238[/TD]

</tbody>

this is the layout in the other worksheet:

[TABLE="width: 1436"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

<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>
[TD="class: xl65, width: 272"]Year[/TD]
[TD="class: xl66, width: 238"]18/19[/TD]
[TD="width: 203"][/TD]
[TD="class: xl67, width: 109"][/TD]
[TD="class: xl65, width: 182"]WLA summary[/TD]
[TD="class: xl68, width: 133"]JS1
[/TD]

[TD="class: xl69"]Name[/TD]
[TD="class: xl70"]John Smith[/TD]

[TD="class: xl67"][/TD]
[TD="class: xl71"]Research and Scholarship[/TD]
[TD="class: xl72, align: right"]148[/TD]

[TD="class: xl69"]Staff Code[/TD]
[TD="class: xl73"]JS1[/TD]

[TD="class: xl67"][/TD]
[TD="class: xl71"]Teaching Allocation[/TD]
[TD="class: xl74, align: right"]0.0[/TD]

[TD="class: xl69"]Department[/TD]
[TD="class: xl73"]Finance[/TD]

[TD="class: xl67"][/TD]
[TD="class: xl71"]Teaching-related Allocation[/TD]
[TD="class: xl74, align: right"]0.0[/TD]

[TD="class: xl69"]Subject Group[/TD]
[TD="class: xl75, width: 238"]Accounts[/TD]

[TD="class: xl67"][/TD]
[TD="class: xl71"]Academic Mgt & Admin[/TD]
[TD="class: xl76, align: right"]135.00[/TD]

[TD="class: xl69"]Grade[/TD]
[TD="class: xl73"]5[/TD]

[TD="class: xl67"][/TD]
[TD="class: xl77"]Total[/TD]
[TD="class: xl78, align: right"]283.0[/TD]

[TD="class: xl69"]FTE[/TD]
[TD="class: xl73"]1[/TD]

[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl69"]Total workload hours available[/TD]
[TD="class: xl73"]1500[/TD]

[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl79"]% allocation[/TD]
[TD="class: xl80"]18.9%[/TD]

[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl81"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl81"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl82"]Research & Scholarship[/TD]
[TD="class: xl83, align: right"]148.0[/TD]
[TD="class: xl67"][/TD]

[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl71"]Scholarship and External Engagement[/TD]
[TD="class: xl84, align: right"]148[/TD]

[TD="class: xl85, width: 272"]PG Cert HE[/TD]
[TD="class: xl86, align: right"]0[/TD]
[TD="class: xl87"][/TD]

[TD="class: xl71"]Research allocation[/TD]
[TD="class: xl84, align: right"]0[/TD]

[TD="class: xl71"]Staff PhD allocation[/TD]
[TD="class: xl84, align: right"]0[/TD]

[TD="class: xl88"]Mentoring[/TD]
[TD="class: xl89, align: right"]0[/TD]
[TD="class: xl94, width: 203"]Max combined total of 252[/TD]

[TD="class: xl90"][/TD]

[TD="class: xl91, width: 272"]Grading papers/REF duties[/TD]
[TD="class: xl84, align: right"]0[/TD]

[TD="class: xl90"][/TD]

[TD="class: xl92, width: 272"]Designated School-wide research role(s)[/TD]
[TD="class: xl93, align: right"]0[/TD]

[TD="class: xl90"][/TD]

</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))