Forum Discussion
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 have to divide up the list into different sections (tab B) on other sheets/tabs. When an email address changes, I don't want to have to edit the info in both sheets, is there a way to link the cell or row data together so that I only have to change the email address on tab A as the master list?
Thanks a ton in advance!
1 Reply
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
