pointing to a different worksheet

[erniepoe]

Hi all,

So I've got a table of data, and the user chooses the month based on the drop down menu at the top of the page. I have 12 hidden tabs, one for each month. When the user selects Jan from the drop down menu, I want the formulas to point to the Jan worksheet, and so on and so forth.


Rather than making a clunky 12-sided if statement (my formulas are already fairly long), is there an easier way to have the formulas point to the correct month and go to the proper worksheet based off what the user selects?

Thanks,
Ernie

 

[Comfy]

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Jan</td><td style="text-align: right;;">23</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">23</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=INDIRECT(<font color="Blue">A1&"!A1"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=Jan!A1</td></tr></tbody></table></td></tr></table><br />

 

[erniepoe]

Thanks Comfy,

i see what you're going for but just not able to get it to work. The name of the month is stored on a tab called "Controls" in cell d55. Here's the relevant part of my formula right now.


IF(L$19=2,Aug!C409....


If the user were to select July, I'd automatically want the Aug changed to Jul.


So now I have


IF(L$19=2,Indirect((C409&"!ControlsD55"))

Which I know is wrong, but I think it's close. Can anyone steer me in the right direction?

Thanks.

 

[erniepoe]

So to clarify, the name of the month I'm after is listed on the Controls tab in cell d55, and the cell with the actual data is located in cell 409 on that particular month's worksheet. It's that number I am after.

Thanks