Controlling formula ranges with DropDown Menu

[AHFoddeR]

Hey guys,

I've got lots data on different tabs. For complicated reasons, I have to have one week of data on each tab. Each tab has identical structure, columns, rows etc. The tabs are W1, W2, W3, etc.

I've got a ComboBox controlling a cell and outputting a number. E.g. 1 for Week 1.

Example:

Please Login or Register  to view this content.

What I want is a master Week Tab (same structure) which looks at the ComboBox output and if it's 1, It gets ='W1'!A9, if it's 2 it gets, ='W2'!A9, if it's 3 it gets ='W3'!A9 etc.

What formula do I need to put to dynamically change the tab reference?

Let me know if you need more information.

Cheers!

[dredwolf]

Try something like this :
(assuming the cell to get the sheet # from is A1)
=INDIRECT("'W"&A1&"'!A9")

Hope this helps

[AHFoddeR]

Awesome Dred. It worked.

The next step, however, is if I drag that formula down one cell, I want it to bring in A10 from W1 tab. If I drag it across one cell, I want it to bring in B9 from W1 tab. Is this possible?

[dredwolf]

Okay, thats a little more complicated, and need to know if you want to use the same dropdown,but as I expected that may be the next question , I did think about it..
(This assumes you are starting the table transfer in A2, if your starting it somewhere else, change the $A$2 and A2 references in the rows() & columns() functions to the starting cell):
Revised (3 times!!.... )

Please Login or Register  to view this content.

Hope this helps

EDIT-
Missed a ')'
Edit2-
Missed the column reference as being column 1, see revised again
AND MISSED the Absolute referencing.($)....grr...

[AHFoddeR]

Nailed IT! Thanks dred

[dredwolf]

You are welcome

Please remember to mark the thread as solved if you are satisfied with your solution :
To mark thread "Solved", go to the top of the thread,click "Thread Tools",click "Mark as Solved"