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.
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").