updating formulas

Hi,

From the attachment, I am trying to find a way of being able to automatically update the master sheet with customer information from the customer 1 and 2 tabs.

I have tried to use pivot tables and the get pivot function but the problem is when a new date with new information is added to the customers tabs the master sheet is not automatically updated with the most current information. Am I using grt pivot data incorrectly or is there another way?

Any help would be great fully appreciated.

Hi

I don't think that you have the best layout for your data. I think it should be transposed, as the number of Products will likely remain relatively "fixed", but the number of transactions will constantly increase.
I the attached workbook, I have transposed the data on the the 2 customer sheets, and created tables using Insert > Table and I named them Customer_1 and Customer_2 respectively because table names cannot contain spaces.
I then changed your headings in B2 and C2 on sheet Master to coincide.

Then the simple formula of
=SUM(INDIRECT(B$2&"["&$A3&"]"))
copied across and down provided your result.

As tables are automatically dynamic, they will grow as you add more data (rows) or if you add more products (columns)

Hiya,

Many thanks for your reply, almost there, however I didnt want to sum up the products, I wanted to be able to show on the "master" sheet the latest figure for the latest date on the customers tabs, Otherwise I wouldnt have the need for a master sheet. The only way I can think of doing it is to use a pivot table and then using the =getpivotdata formula but the problem is being able to update the pivot tables automatically.

I dont know if there is another way or if you have any other suggestions?

Thank you for your response

Hi

You can find the last item in a numeric range, by carrying out a Lookup, for a large value (one that will not be found within your data).
Lookup then returns the last value it found whilst searching for your value.
There are many different options for choosing a large number, I normally use 99 to the power of 99 ( 99^99)
So just change the formula I gave you in my file to

=LOOKUP(99^99,INDIRECT(B$2&"["&$A3&"]")) in cell B3, and copy across and down

Hi,

Many thanks but I keep getting a #REF! error.

Hi
Then I can only suggest that you have not copied the formula correctly.

=LOOKUP(99^99,INDIRECT(B$2&"["&$A3&"]"))

Take a look at the attached file.

Absolute magic, you are awesome!