Dynamic Charts and Tables

[HelloWorld]

Hi,


I'm trying to use the OFFSET() and MATCH() formulas together to create a dynamic table (i.e. the data in the table will change depending on what's selected in the drop-down list).


The table only shows data for one selection in the drop-down list and for the rest of the selections the data remains at 0%.


This is my current formula:

=OFFSET(Sheet1!C4,MATCH($C$3,Sheet1!$C$3:$Z$3,0),0)


The data is in the "Sheet1" tab and the dynamic data table is in the "Summary tab."

The data in "Sheet1" starts at B3 and ends at Z7.

The drop-down list is in the "Summary Tab" in cell C3.


I tried using the nested IF statements to extract the data from "Sheet1" to display it in the dynamic table under the "Summary" tab, and it works just fine. But, that seems inefficient. The formula for that is:


=IF($C$3="NCR",Sheet1!C5,IF(Summary!$C$3="SCR",Sheet1!F5,

IF(Summary!$C$3="CO",Sheet1!I5,IF($C$3="GA",Sheet1!L5,

IF(Summary!$C$3="HI",Sheet1!O5,IF(Summary!$C$3="MAS",Sheet1!R5,

IF(Summary!$C$3="NW",Sheet1!U5,IF(Summary!$C$3="OH",Sheet1!X5, "-"))))))))


I can also upload the Excel workbook so that what I wrote above will be clearer, but I can't seem to find a way to upload it.

 

[Luke M]

Is the list of values (NC, SCR, CO, etc) in Sheet1!C3:Z3? If so, the following should do it:

=INDEX(Sheet1!C5:Z5,MATCH($C$3,Sheet1!C3:Z3,0))

or even:

=HLOOKUP($C$3,Sheet1!C3:Z5,3,FALSE)

 

[HelloWorld]

It works now!


Thanks so much Luke!

 

[Luke M]

Woot, glad I was able to help. Thanke for the feedback. =)