Excel Formula help (INDIRECT)

Soldato
Joined
18 Oct 2002
Posts
4,023
Location
Wellington, NZ
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,
 
lol :D

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 :)
 
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....
 
Last edited:
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!
 
Back
Top Bottom