Dynamic INDIRECT formula with range

2
votes

I have some dynamic input, in my case, the name of the month in cell I25. Based on the month the function in cell H32 should reference a sheet with the name of the month and cell A18 within that sheet. Now this I can handle and have made it possible through the INDIRECT function.

The issue I'm having is with dynamic range. For example, I would like cell H33 to reference cell A19 within the worksheet "February". The closest I got to it was =INDIRECT($I$25"&"!A18:A200"). And it seems to be working, but for some strange reason it starts referencing the cells contents from A36 onwards, which I don't get. Suggestions?

Any help would be greatly appreciated.

1
exceldynamicexcel-formulaexcel-indirect

1 Answers

4
votes

Use this one in H32 and drag it down:

=INDIRECT("'" & $I$25 & "'!" & CELL("address",A18))

Notes:

  1. I've changed $I$25 & "!" to "'" & $I$25 & "'!" in formula to make it more reliable (for the case when sheet name contains spaces you should include your sheet name in single quotes like this: 'My sheet'!A18)
  2. In H32 this formula evaluates to =Feb!A18 (where Feb is your sheet name), in H33 to =Feb!A19 and so on.