Background: every month, the user dumps sales data into the 'Data' sheet, then refreshes a pivot table on the 'PT - Store' sheet. On a third sheet, they want to see the item categories with the top sales (in order), and then the top 7 stores within each category (in order), along with the $ amounts. The formulas below are on that third summary sheet. I have the formulas to find the top categories and the top 7 amounts, but can't get the formula for the store # to work.
The original formula works fine:
{=LARGE(IF('PT - Store'!$A:$A=$B$6,IF('PT - Store'!$C:$C=$F6,'PT - Store'!$B:$B,"")),1)}
However, the IF('PT - Store'!$C:$C=$F8 should change based on the month entered on the sheet (January = column C, Feb = column D, March = column E, etc). I've got a cell (C1) in the sheet that calculates the column that should be entered in the formula, but when I try to use the INDIRECT method to insert it into the formula, it gives a #REF! error:
{=LARGE(IF('PT - Store'!$A:$A=$B$6,INDIRECT("IF('PT - Store'!" & $C$1 & ":" & $C$1 & "=$F6,'PT - Store'!$B:$B,"")")),1)}
{=LARGE(IF('PT - Store'!$A:$A=$B$6,IF(INDIRECT("'PT - Store'!" & C1 & ":" & C1 & "=$F6"),'PT - Store'!$B:$B,"")),1)}
Anyone have any ideas?