Forum Discussion

BSchwarzenbach's avatar
BSchwarzenbach
Copper Contributor
May 16, 2023

Automatically changing data on multiple sheets

Hi all, I'm creating a participant roster for an online course of mine which mainly consists of names & email addresses. I have a sheet/tab with the master list (tab A) of all participants and then h...
excel
SnowMan55's avatar
SnowMan55
Bronze Contributor
May 17, 2023

BSchwarzenbach 

The usual technique is to use one of Excel's lookup features:

  • XLOOKUP
  • VLOOKUP
  • LOOKUP
  • INDEX & MATCH

in formulas on the non-master worksheets to look up the email address that corresponds to a participant's name on the master worksheet.  (Potential problem: What if two participants – current or past – have the same name?  You may need to include an additional, unique identifier in the worksheets, and use that as a lookup value instead of name.)

 

I suggest trying the XLOOKUP function, if your version of Excel supports it, as it is the most flexible.   Assuming that your master worksheet stores the data in a range (not an Excel table)…  Note that the examples at that link do not show looking up a value in a range on a different worksheet.  Your formulas might look like:

=XLOOKUP( C7, 'Master Sheet'!A$1:A$999, 'Master Sheet'!B$1:B$999, "no match" )

if the master worksheet has participant names in column A and emails in column B, and the participant name you are doing the lookup for is in C7.  (I added some spaces in the formula to improve readability; except to match the worksheet name, they are not required.)

 

There are alternatives that I will just mention: named ranges, and Excel tables

 

Resources