Dynamically change data for different scenarios?

[rrahman]

Hi - I'm trying to create a way for different scenarios for an income statement (table in worksheet 1). I would ideally like to have a drop down list that calls data from worksheet 2 and populates worksheet 1. Help?

The source of scenario data is layed out as follows (table in worksheet 2).

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Scenario 1[/TD]
[TD]Scenario 2[/TD]
[/TR]
[TR]
[TD]Revenue[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Costs[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I then need the data to feed into another income statement (table in worksheet 1) that looks like this
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Scenario that I can choose[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Revenue[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Costs[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jorismoerings]

hi,

try this:

Book1
	A	B	C	D
1		Scenario 1	Scenario 2	Scenario 3
2	Revenue	10	15	20
3	Costs	5	6	7
Sheet2

Book1
	A	B
1		Scenario 1
2	Revenue	10
3	Costs	5
Sheet1

Cell Formulas
Range	Formula
B2	=INDEX(Sheet2!$B$2:$D$3,MATCH($A2,Sheet2!$A$2:$A$3,0),MATCH(B$1,Sheet2!$B$1:$D$1,0))
B3	=INDEX(Sheet2!$B$2:$D$3,MATCH($A3,Sheet2!$A$2:$A$3,0),MATCH(B$1,Sheet2!$B$1:$D$1,0))