如何自动更新GETPIVOTDATA公式的月份数组？VLOOKUP引用遇#REF!错误

Why Your Original Approach Failed #

The VLOOKUP returns a text string like "{7,8,9,10,11,12}", but GETPIVOTDATA requires an actual numeric array to filter months. Excel doesn’t automatically convert text-based array strings into usable arrays in this context, which triggers the #REF! error.

Recommended Method (Excel 365/2021 with Dynamic Arrays) #

This approach generates the correct month array dynamically based on your current month cell ($B$2) without needing a lookup table:

1. Store your current month as a number (1-12) in cell $B$2 (e.g., 1 for January, 7 for July).
2. Replace your static formula with this dynamic version:

   =SUM(GETPIVOTDATA("Amount",'Transactions(Pivot)'!$A$75,"Location","Los Angeles","Months",IF($B$2>=7,SEQUENCE($B$2-7+1,1,7),VSTACK(SEQUENCE(6,1,7),SEQUENCE($B$2,1,1)))))
   

How It Works: #

• If current month is July or later (>=7): SEQUENCE($B$2-7+1,1,7) creates an array starting at 7 and ending at your current month. For example, if $B$2=12, it generates {7,8,9,10,11,12}.
• If current month is before July (<7): VSTACK(SEQUENCE(6,1,7),SEQUENCE($B$2,1,1)) combines two sequences: one from 7 to 12, and another from 1 to your current month. For example, if $B$2=1, it generates {7,8,9,10,11,12,1}.

Alternative Method (Older Excel Versions Without Dynamic Arrays) #

If you’re using an older Excel version that doesn’t support SEQUENCE or VSTACK, use a named range with EVALUATE:

1. Keep your existing lookup table (Column A: month numbers, Column B: text arrays like {7}, {7,8}, etc.).
2. Create a named range:
   • Go to Formulas > Define Name.
   • Name it DynamicMonthArray.
   • In the "Refers to" box, enter: =EVALUATE(VLOOKUP($B$2,$L$9:$M$20,2,FALSE))
3. Update your formula to use the named range:

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

This works because EVALUATE converts the text string from your lookup table into an actual array that GETPIVOTDATA can process.

内容的提问来源于stack exchange，提问作者djacobs920