Help Please, Need A Formula to Capture Ever Moving Range of Data

[trele6]

Hello,

So I have an excel sheet with two tabs of data. The first sheet(Scorecard) is basically a summary tab linking certain data from a second sheet (SC Data) in a more presentable format. My data on the second sheet (SC Data) is updated every month through a linked table from SAP. Every time a new month of data is added a new column is created for that month.

My dilemma - On my front page "presentation" sheet (Scorecard) I would like a formula that will automatically move to pull the data I need when I change the month cell. Currently I am using the following formulas to pull my data

{=INDEX('SC Data'!$P$7:$P$190,MATCH(Scorecard!B$170&Scorecard!$A171,'SC Data'!$A$7:$A$190&'SC Data'!$B$7:$B$190,0))}

SC Data range P7:P190 is all of my March data, I am then matching to cell B170 and A171 on the Scorecard sheet which are in the corresponding ranges of A7:A170 and B7:B190 on the SC data sheet

My date is in cell A170 on the scorecard and is displayed as Mar-17 and the value in the actual cell is 03/01/2017. This value ties to a column header I have on my SC data sheet which is P6.

I assume I would need some kind of offset formula but admit I am weak on those, or maybe it is something completely different.

Basically once I change the date in cell A170 to 04/01/2017 I would like my INDEX formula to automatically pull data from Q7:Q190 instead of P7:P190.

Any help please?

Thanks,

 

[Tetra201]

See if this works for you (note: adjust AZ as needed):

=INDEX(INDEX('SC Data'!$A$7:$AZ$190,0,MATCH(Scorecard!A170,'SC Data'!$6:$6,0)),MATCH(Scorecard!B$170&Scorecard!$A171,'SC Data'!$A$7:$A$190&'SC Data'!$B$7:$B$190,0))

 

[trele6]

See if this works for you (note: adjust AZ as needed):

=INDEX(INDEX('SC Data'!$A$7:$AZ$190,0,MATCH(Scorecard!A170,'SC Data'!$6:$6,0)),MATCH(Scorecard!B$170&Scorecard!$A171,'SC Data'!$A$7:$A$190&'SC Data'!$B$7:$B$190,0))

That worked!

Thank you very much.

 

[Tetra201]

You are welcome.