Dynamic Lookup Formula
Posted by Jeremy Kenton on August 04, 2000 10:19 AM
I have two sheets. One is a pivot table containing a list of people and how much they bought, broken out by months. The other sheet has the same list of people. I need a formula that will sum up the amount bought a person during a three month period from the pivot table and enter that value into the second sheet. The catch is the formula needs to be dynamic. So once a new month comes around, the formula needs to sum the three most recent months. For example, for August it would be summing July, June, and May. For September it would need to sum August, July and June. I appreciate any help you can give. Thanks in advance.
Posted by Jeremy on August 07, 0100 9:40 AM
Re: Correction
Basically, I need a formula that will sum the three columns to the left of the grand total in the pivot table. Each month a new column will be added to the pivot table. Does this help? Thank you for all your assistance.
Posted by Celia on August 04, 0100 4:49 PM
Jeremy
It may be possible to do it by using the INDIRECT worksheet function, but it might depend upon the format of your sheets and how the data is updated each month.
For example, assuming the following :-
Sheet1 contains the data by month. The data starts in row 2, the persons names are in column A, and the three months to be summed are always in columns B:D.
Sheet2 contains the 3-month totals. The names are in column A and are exactly the same (number and sequence) as the Sheet1 names. The formula for the 3-month totals is in column B.
Put the following formula in cell B2 of Sheet2 and fill down :-
=SUM(INDIRECT("Sheet1!B2:D2"))
This formula will always return the sum of columns B:D even if columns are inserted or deleted in Sheet1.
Celia
Posted by Celia on August 07, 0100 5:30 PM
Re: Correction
Jeremy
Did you receive my e-mail with the formula entered? This formula works with the pivot table format in the workbook you sent to me - the three most recent months were not immediately to the left of the grand total.
If you want to format the pivot table with the most recent months to the left of the grand total and insert a column each month, I can't think of a worksheet formula method of doing what you want. It may well be possible but I think it would be very complex - would need to spend a lot of time trying to work it out without any guarantee of success.
It could be done with VBA but the simplest way is to reformat your pivot table and then the formula structure that I sent with your workbook does what you need.
Instead of having the pivot table columns like this:-
JAN/FEB/MAR/APR/G.TOTAL
Have them like this:-
G.TOTAL/APR/MAR/FEB/JAN
Columns for subsequent months would then be inserted immediately to the right of the grand total.
Celia
Posted by Celia on August 04, 0100 6:06 PM
Correction
Correction :-
Sorry, I don't think the above works since the formula cannot be filled down the column.
Will advise if I think of something else.
Celia
Posted by Celia on August 04, 0100 6:43 PM
Revised formula
Jeremy
Try this formula. It should be possible to fill down :-
=SUM(OFFSET(INDIRECT("Sheet1!A2"),ROW()-2,COLUMN()-1):OFFSET(INDIRECT("Sheet1!A2"),ROW()-2,COLUMN()+1))
Celia
