sara has attended:
Excel Advanced course
Excel Linking Columns
I have a spreadsheet, that is used like a database - ideally, it would be ace if I could stop typing in the same data each week ie: Janet is always allocated to work in Marks And Spencer and Phil always works for New Look - each week I have to update my spreadsheet and I wondered if there was there a way that when I type New Look in column G that it automatically allocates Phil's name in Column I etc
Thanks for any help or advice
RE: Excel Linking Columns
Hi Sara
Thanks for your question.
You should be able to set up a simple VLOOKUP function to autopopulate Column I based on Column G. You will have covered this function in the Excel Advanced course and should find more detail in your manual, however I can talk you through the basic steps.
1) Create a simple lookup table of locations and corresponding names (this can be on a different sheet that you can hide if necessary)
2) In column I use a VLOOKUP function, which will look something like:
=VLOOKUP(lookup cell,lookup table,column index number,range lookup)
=VLOOKUP(G1,Table of locations,2,0)
3) Copy the VLOOKUP function down column I - now when you type a location in column G it should automatically pull in the correct person to column I. If you need to change a person/location, just edit your lookup table and it will update all the fields in your function.
Let us know if you have any further questions, hope this helps!
Kind Regards,
Sarah
Excel Trainer
