Double Indirect function?

[erniepoe]

On my Summary worksheet, I'm looking for a formula invokes a cell based off what month it is. I have a "Controls" worksheet which shows the month the user has chosen from a dropdown menu on the Summary page, and then 12 other worksheets, one for each month.

So the formula would always be looking at the 'controls' tab in cell d55 to get the current month, and then based on that, would go to the relevant worksheet from that month and grab cell C409.


I think this would require two indirect functions, but not quite sure how to write it. Any help?

Thanks

 

[Robert Mika]

=indirect(c55&"!"&"c409")

 

[T. Valko]

On my Summary worksheet, I'm looking for a formula invokes a cell based off what month it is. I have a "Controls" worksheet which shows the month the user has chosen from a dropdown menu on the Summary page, and then 12 other worksheets, one for each month.

So the formula would always be looking at the 'controls' tab in cell d55 to get the current month, and then based on that, would go to the relevant worksheet from that month and grab cell C409.


I think this would require two indirect functions, but not quite sure how to write it. Any help?

Thanks

Try it like this...

=INDIRECT(C55&"!C409")

Assuming the month is the TEXT month name like Jan or January.

 

[erniepoe]

thanks, guys.

But the formula would be on the summary sheet, where it would then look to the controls worksheet to get the month from cell D55, and from there go to the relevant month tab (if controlsD55 was Apr, the info would be from the Apr tab) to get the info from c409 of the month tab. I think your formulas are assuming I'm working off the "controls" tab.

 

[T. Valko]

thanks, guys.

But the formula would be on the summary sheet, where it would then look to the controls worksheet to get the month from cell D55, and from there go to the relevant month tab (if controlsD55 was Apr, the info would be from the Apr tab) to get the info from c409 of the month tab. I think your formulas are assuming I'm working off the "controls" tab.

OK, then just include the sheet name:

=INDIRECT(Controls!D55&"!C409")

 

[erniepoe]

That worked great. Thanks, T. Valko!

 

[T. Valko]

That worked great. Thanks, T. Valko!

You're welcome. Thanks for the feedback!

 

[erniepoe]

One more quick question:

Is there a way that I could drag that formula and have the cells change with it? I locked the controlsD55 cell with the $$, but if I drag the formula over, I'm hoping for the C409 to turn into D409, etc, while the ControlsD55 remains locked.

Is there a way to do this?

 

[T. Valko]

One more quick question:

Is there a way that I could drag that formula and have the cells change with it? I locked the controlsD55 cell with the $$, but if I drag the formula over, I'm hoping for the C409 to turn into D409, etc, while the ControlsD55 remains locked.

Is there a way to do this?

Try something like this...

Let's assume you enter the first formula in cell A2 and want to copy across the row.

=INDIRECT(ADDRESS(409,COLUMNS($A2:A2)+2,4,,Controls!$D55))