Excel Formula help (INDIRECT)

AmDaMan · 10 Jul 2008 at 11:37

Hi chaps, quick question.

If I have the formula;

=SUM(INDIRECT("I16:J16"))

How can I make it possible to drag the formulae to drag it to drag the formula so it auto fils to other cells.

eg;

J17, j18 etc.

The reason i'm using indirect is so I don't get #ref! when I delete a cell, which I sometimes have to do.

Thanks,

MarcLister · 10 Jul 2008 at 12:00

Just drag it down?

AmDaMan · 10 Jul 2008 at 12:10

lol

That's the problem mate, it won't just drag down with this formula, i think you have to tie it in with another formula.

Cheers for the help though

MarcLister · 10 Jul 2008 at 13:23

I don't know INDIRECT but it could be worth trying ISERROR.

aa6972 · 10 Jul 2008 at 13:50

This is from Google

Here
--------------------------------------
Another useful feature of the INDIRECT function is that since it takes string argument, you can use it to work with cell references that you don't want Excel to automatically change when you insert or delete rows. Normally, Excel will change cell references when you insert or delete rows or columns, even when you use absolute referencing. If you have the formula =SUM($A$1:$A$10), and then insert a row at row 5, Excel will convert the formula to =SUM($A$1:$A$11). If you don't want this to happen, use the INDIRECT function to change a text string to a reference:

=SUM(INDIRECT("A1:A10"))

Since Excel sees "A1:A10" as a text string rather than a range reference, it will not change it when rows or columns are deleted or inserted.
---------------------------------------------

So i guess it means don't use indirect if u want it to change....

AmDaMan · 10 Jul 2008 at 14:50

I've seen that page and that's the reason i've used indirect because when I delete a cell i don't want it to display #ref! or anything. I want it to stay absolute to say 'K20' Even if I delete a column.

The problem is I can't drag the formula down so it applies to the cell underneath it for example.

There must be a way though!