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 Answer

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:

```
=SUM(INDIRECT("I5")*Z5)
```