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? – cybernetic.nomad Mar 29 '18 at 20:23

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:


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.