Three variable "data table"

[DMT]

I would like to know if there is a way to create a 3 variable data table. Excel allows a 2-way table (i.e. inputs in rows and columns). However, I would like to know if I can do the same thing, but change one more variable. I'm not sure if there is a way with vlookup or offset or an if statement to do this.

Thanks,

 

[Excelestial]

Hmmmmm...a third dimension? Care to provide a sample or speak in specifics?

 

[DMT]

Here is an example,

I want to pull in different line items from a table. Across the top of my table is dates. It is down the left hand side that I am struggling. I want to do 2 things (hence the 3 variable table). I want to look up a certain row number and do this based on a certain scenario. I have thought about using Excel's Scenario tool, but it's not what I'm after.

To be more clear, the value to be calculated in my two-dimensional data table is based on the hlookup formula. The hlookup looks up a certain column (forced by Excel Data Table) and then would look up a certain row (based on the left side variables in the Data Table). This is fine, but I want to do this also for three scenarios.

I don't know if I'm making any sense...

 

[ddadmin2009]

Can you provide a snapshot of the data you have. Looks like your problem can be solved using either couple of formulas or pivot tables.

 

[DMT]

Here is snapshot...

0.0 2009 2010 2011
81 $0 $0 $0
82 $0 $0 $0


The 0.0 is the number that the data table is calculating. Across the top are years and down the left are row numbers. The 0.0 is hlookup(year, array, row). I want the 0.0 to be a certain line item (either row 81 or row 82 from a table above, which hlookup takes care of) based on either scenario 1 or scenario 2. I don't know how to incorporate the two scenarios into this 2x2 data table.

Thanks.

 

[mikerickson]

Get two Option Buttons from the Forms menu.
Label them Scenario1 and Scenario2.
Link them to a cell. (AA1 for example)
Use formulas similar to
=CHOOSE($AA$1, formulaForScenario1, formulaforScenario2)