Indirect Function in Excel

2
votes
 =Indirect("'App Summary'!"&$D$4&"18")

I have two worksheets, Sheet 1 and App Summary. On Sheet 1 in cell D4 I have a column lookup based on a value typed in cell B2. So for instance, if I type in July in cell B2 of Sheet 1, it will place H in cell D4 of Sheet 1. If I type in June in cell B2 of Sheet 1, then it will place G in cell D4 of Sheet 1. Using the above indirect formula, I look up a value in App Summary using the column reference in cell D4 on Sheet 1.

My question is the following. Is there a way to make the above formula dynamic so that if I add a row to the App Summary sheet it will automatically adjust the row in the formula? For instance in the above example lets say I am referencing H18 in the app summary sheet. If I add a row in the app summary sheet just before H18, how do I get the formula on sheet 1 =Indirect("'App Summary'!"&$D$4&"18") to update to =Indirect("'App Summary'!"&$D$4&"19") since the row it was referencing shifted down one?

2
excelexcel-indirect

2 Answers

2
votes

Assuming from I am referencing H6 in another sheet , the other sheet is App Summary use

=INDIRECT("'App Summary'!"&D3&ROW('App Summary'!$A$6))
0
votes

If you can be certain that any additional rows in App Summary will be added as right-click and Insert row, then you could reference the a cell in the last row with an absolute reference (using dollar signs to fix the reference).

Another approach would be to convert your App Summary to an Excel table (ctrl + t) and then use structured referencing. That way, you are not vulnerable to the changing structure of the App Summary sheet (except if you change column headings).