Dragging INDIRECT and

0
votes

I thought I would take you through my thinking as there may be a better way than using INDIRECT to achieve the desired result but if not then a solution with indirect would be very handy.

I had a simple formula

=SUMPRODUCT($E$10:$E20,J10:J20) Dragged across a great number of columns ending somewhere in the CC's

However I found when I inserted a row it would change my 10 and $10 to 11 and $11, so i placed an indirect in there.

=SUMPRODUCT(INDIRECT("$E$10:$E20"),INDIRECT("J10:J20"))

I now realize that I'm referring to a string and if I drag this across to the right, the cell references won't update.

So initially my first question was how could I go about updating these so that I can drag the INDIRECT across the columns to the right and update the cell references so that my formula would resemble or at least work in the same way when dragged into column K and so on.

=SUMPRODUCT(INDIRECT("$E$10:$E20"),INDIRECT("K10:K20"))

HOWEVER

If There is a better solution than the indirect to keep my orignial formula when rows are inserted or deleted that would be very helpful too.

1
exceldelete-rowexcel-indirect
a) Where are you inserting the row? b) Should $E$10:$E10 be $E$10:$E20 (in two places)? – user4039065
apologies, you are absolutely correct, that was supposed to be $E$10:$E$20, my mistake – Paul
I'm not sure why you used $E$10:$E20 and not $E$10:$E$20 in the original formula. Was row 20 intended to expand to 21 when a row was inserted while row 10 remained static? – user4039065
Hi Jeeped, I think that was a result of to much coffee, and staring unsuccessfully at excel for the last couple of hours, I just missed the $20 You have been a great help and saved me from a major case of defenestration – Paul

1 Answers

2
votes

You have turned both rows and columns into static string representations when you really want to only guard the row designations from changing on a row insert. Replacing your INDIRECT functions with INDEX functions will allow you to make column E absolute with a conventional $ and leave column J relative as well as hard-code the referenced rows so they will not change on a row insert.

=SUMPRODUCT((INDEX($E:$E, 10):INDEX($E:$E, 20)), (INDEX(J:J, 10):INDEX(J:J, 20)))

Just don't insert any columns and everything should work as planned.

On a related note, referring to "$E$10:$E20" as a string in INDIRECT may help visually identify its purpose but as you've noted, the text is not going to change despite dragging to a new location or inserting rows. There is no difference if you use INDIRECT("E10:E20").