multiple sheets and naming cells

Bell_1515++** · 03-31-2016, 12:23 AM

If I have two worksheets that have the same data, eg membership details on various people and email addresses, how can I change information in one cell and it automatically changes in the next worksheet tab? One worksheet has more rows as it contains all members whilst the second worksheet only contains executive members, therefore, rows won't match as one worksheet has more rows. Can anyone help? I think it is to do with naming cells to link the same cells in each worksheet.

**Kevin#** · 03-31-2016, 01:38 AM

Not sure you need 2 sheets. If the information is identical, then the "Exec" is just a subset of the whole membership. Include a column with "E" in it for execs and "M" for other members. Then use auto filter and filter on "E" to get your execs list.
Your data needs to be in consecutive rows (no blanks) and no blank columns.
Go to cell A1 and then Data tab and Autofilter

**FDibbins** · 03-31-2016, 01:38 AM

Hi, welcome to the forum

You can do this in 1 direction with formulas, but not both ways, ie you can add/change data in sheet1 and sheet 2 will update, but you cannot also have it so that if you update sheet2, sheet1 also updates. the reason for this is that a cell can either contain data OR a formula, but not both.

To update sheet1 if sheet2 gets changed, take a look at using the VLOOKUP functions

**Kevin#** · 03-31-2016, 03:51 AM

The attached file illustrates both post#2 and post#3 suggestions

AutoFilter option
Click on sheet "MemberList" and then use the dropdown box at the top of column E to only select "E" for Exec

VLookup option
Sheet "Exec" contains only the Exec members - values in B:E derived via Vlookup

The formula in B2 is
=VLOOKUP($A2,MemberList!$A:$E,COLUMN(),0)

This formula matches value in column A of "Exec" and finds it in the first column of "MemberList", it then needs to know which column's values to use and the Zero at the end tells it to only go for an EXACT match.
By setting up the table starting at column A, and making the tables identical, it is possible to use the Excel function =Columns() which returns the cell column number (which is identical for both tables)

With Vlookup , the value you are looking up must be in the leftmost column of the lookup table and the value must be unique.
If you list has Member Names in the first column and you do not want to change that, there are ways to get around that