Referencing a vlookup result as a formula argument

0
votes

Suppose the value of cell A1 is total sales $ at the Los Angeles location, year to date. The value is pulled from a pivot table on a different sheet. The formula for A1 is:

=SUM(GETPIVOTDATA("Amount",'Transactions(Pivot)'!$A$75,"Location","Los Angeles","Months",{7,8,9,10,11,12}))

Currently, every month when I run this report I need to manually update the month arrays in the formulas; for example next month I will need to manually update {7,8,9,10,11,12} to {7,8,9,10,11,12,1}. There are many cells and this is a very tedious task.

My question is how can I automate the task of updating the month array in the GETPIVOTTABLE formula?

What I've attempted with no success: I set up a 2-column lookup table on a different sheet where column A is the current month and column B contains the month array value to be used in the GETPIVOTTABLE formula. For example:

7 {7}
8 {7,8}
9 {7,8,9}

etc...

Then I tried updating my GETPIVOTTABLE formula to use a VLOOKUP to get the month array:

=SUM(GETPIVOTDATA("Amount",'Transactions(Pivot)'!$A$75,"Location","Los Angeles","Months",VLOOKUP($B$2,$L$9:$M$20,2,FALSE))

But it didn't work - I get a #REF! error.

Thanks in advance for your help.

1
excelexcel-formula
"It didn't work" - what happened? Do you get an error? Does it return an unexpected value? I think it's that you're trying to insert an array via vlookup, and Excel may not like that. – BruceWayne
I get a #REF! error when I attempt to use VLOOKUP to get the array value into the formula. – djacobs920

1 Answers

0
votes

You can give a range instead of the vlookup and enter the numbers 7,8,9 . . . . in that range for example your formula 

=SUM(GETPIVOTDATA("Amount",'Transactions(Pivot)'!$A$75,"Location","Los Angeles","Months",{7,8,9,10,11,12}))

will become

=SUM(GETPIVOTDATA("Amount",'Transactions(Pivot)'!$A$75,"Location","Los Angeles","Months",A1:A7))

and A1 to A7 contains the months.

after entering this formula press Ctrl+Shift+Enter