I am trying to drag an INDIRECT formula down a column but can't figure out how to get it to change the reference for each row. It's a sum formula that multiples two columns. I am using the INDIRECT in it because this is a master template and columns get deleted. Previous to using the INDIRECT formula I would get a #REF error when deleting those columns. So the INDIRECT works great now - I just need to know how to drag it down the column so that I don't have to manually change each cell. The formula I am using is =SUM(INDIRECT("I4")*Z4). I know since I have the I4 in the " " - it keeps it static. So what is the work around for it to still work how it does but be able to drag the formula down the column?

  • 1
    It would be useful to see a sample of your data and the expected outcome. Why are you summing the result of a multiplication? Commented Mar 29, 2018 at 20:23

1 Answer 1


To make the INDIRECT() also adjust, use something like:

=SUM(INDIRECT("I" & ROWS($1:4))*Z4)

So dragging down one row will make a formula that behaves like:


You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .