Help: I have a master tab and want other tabs to automatically update

[creative_destruction]

Hello,

I have a workbook that has a master tab with all of our clients as well as certain details. For example, one of the columns is "Partner", i.e. which partner is over the client. So when we add a new client to the master tab, I enter the company name and which partner is over the client. I have separate tabs for each partner, and so I have to manually copy and paste the row of data to the corresponding tab.

I am wondering if there is a way to have the corresponding tab automatically update with that row of data. So lets say I have a new client called Company X and Peter is over Company X. When I enter Company X's information onto the master tab, I would like Peter's tab to automatically update with Company X's information as well.

Is that something that can be done in Excel?

[JBeaucaire]

You're working harder than you need to. The "partner" sheets aren't needed.

On your MAIN sheet, click on the row that has the titles for all your columns, usually that's row 1.

Now click on DATA > FILTER and drop downs will appear in all your title cells.

Click on the PARTNER column and select only ONE partner....

Voila! Now you're looking at only that partner's rows. Edit them, print the page, whatever.

Then turn on a different partner, or Select All.

The AutoFilter is one of Excel's most useful tools for dealing with a large dataset in place, no additional sheets necessary.

[creative_destruction]

Hi JBeaucaire,

Thanks for your response. I should have been more clear. On each tab, I have a formula in a separate cell that calculates a score. So on the master tab, that score is calculated based on the values for all of the clients. If I just sort so I can only see Peter's clients, the formula doesn't change and it still has all of the other values included in its calculation.

That's why I have them on separate tabs. I have a cell with the score on Peter's tab and so on and so forth. But as you said, I may be working too hard. Maybe there's a way that the formula can reflect the values that show when a filter is applied? That would be much easier.

Here is the formula I have to calculate the score: =ROUNDUP((100*((COUNTIF(A:A,">8")-COUNTIF(A:A,"<7"))/COUNT(A:A))),0)

[JBeaucaire]

That may still be fixable. Many formulas can be updated to only calculate on the visible rows.

It would be easiest to test ideas on this regard if you posted a small, desensitized version of this workbook with just enough fake data in there that we can test updating this formula for you.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[creative_destruction]

Thanks again for responding. Okay, I've attached a spreadsheet with some obviously fake data (we don't actually count Warren Buffett or LeBron James as clients). The purpose of this spreadsheet is to calculate the Net Promoter Score, and I have a formula in cell I2 that calculates that based on the numbers that are entered into column A.

It would be ideal if there was any way I could filter the partner column so it just showed Brooke's clients, for example, and the NPS formula in I2 updated to reflect only the data that was visible.

[JBeaucaire]

We can squeeze this all into one formula, but first I thought I'd let you see the pieces. Try these formulas:

Get a "count" of the visible numeric cells in column A
K1: =SUBTOTAL(102,A:A )

Use SUBTOTAL to get a sum of visible cells >8 and subtract a sum of visible cells less than 7
L1: =SUMPRODUCT(SUBTOTAL(9,OFFSET(A1, ROW(A1:A15) - ROW(A1), 0)), (A1:A15>8)+0) - SUMPRODUCT(SUBTOTAL(9,OFFSET(A1, ROW(A1:A15) - ROW(A1), 0)), (A1:A15<7)+0)

Figure the score:
J1: =CEILING(L1/K1*10,1)



Now try your various filters.