Formula changes when adding rows

[cb95amc]

I have two separate spreadsheets that use an identical
formula....However in one file when I enter an additional row all of
the call references in the formula change change (even those above
where I have entered the row).


In the other file when I enter an additional row the formulas are
maintained....


The formula I use is :


=INDIRECT("'"&$B$4&"'!"&CELL("­Address",K$108))


However, even if I enter a line 20 or 30 rows below it will always
change to:


=INDIRECT("'"&$B$4&"'!"&CELL("­Address",K$109))


I cannot understand why this particular file is behaving like
this.....Anyone have any ideas ?


Thanks


cb

 

[Tom Ogilvy]

You are entering rows above row 108, so it is affected.

This behavior should be consistent. Perhaps the formulas are different in
terms of what cells they reference and in the case where they don't change,
you are inserting the row below all references.

In any event, using cell("address",K$108) seems silly.
try using

=INDIRECT("'"&$B$4&"'!K108"))

and perhaps that will solve all your problems.
--
Regards,
Tom Ogilvy

I have two separate spreadsheets that use an identical
formula....However in one file when I enter an additional row all of
the call references in the formula change change (even those above
where I have entered the row).


In the other file when I enter an additional row the formulas are
maintained....


The formula I use is :


=INDIRECT("'"&$B$4&"'!"&CELL("­Address",K$108))


However, even if I enter a line 20 or 30 rows below it will always
change to:


=INDIRECT("'"&$B$4&"'!"&CELL("­Address",K$109))


I cannot understand why this particular file is behaving like
this.....Anyone have any ideas ?


Thanks


cb

 

[cb95amc]

Tom,

Thanks for the reply....the reason I use the "Address" option is so
that I can copy the formula across other columns, and have the cell
reference change as appropriate.
If I do it as you suggest then the cell reference will always be K108.

As for the row changing, the function is referencing another sheet, so
adding a line to the current sheet should not affect the formula. As I
mention, I have another file which has the exact same function, and
when I add a row in this file the cell reference does not change.

I have also copied the formula to a new file, and again the reference
does not change when I add a row, so it must be something to do with
this particular file....Any ideas ?

Thanks

 

[Earl Kiosterud]

cb,

I can't explain why the references in one formula change, and do not in
another. That requires more investigation. Examine the formulas
carefully -- look carefully at quotes. Any cell reference will change if
the target cell moves. This includes inserting or deleting rows above the
cell (or columns to left). Making it absolute ($) has no effect -- that
controls copying behavior only, not moving.

You can use:

OFFSET(K1, 107,0,1,1)

to get a reference to K108. It will adjust when copied laterally, (and
vertically, though that probably isn't useful), and won't be affected by
inserts/deletes. I don't know if this will help.
--
Earl Kiosterud
www.smokeylake.com

I have two separate spreadsheets that use an identical
formula....However in one file when I enter an additional row all of
the call references in the formula change change (even those above
where I have entered the row).


In the other file when I enter an additional row the formulas are
maintained....


The formula I use is :


=INDIRECT("'"&$B$4&"'!"&CELL("­Address",K$108))


However, even if I enter a line 20 or 30 rows below it will always
change to:


=INDIRECT("'"&$B$4&"'!"&CELL("­Address",K$109))


I cannot understand why this particular file is behaving like
this.....Anyone have any ideas ?


Thanks


cb

 

[cb95amc]

Earl,

That is the strange thing......the cell reference is not actually
moving, as I am adding the row to a different sheet.
I have double checked the formulas on both files and they are
absolutely identical.

As I mentioned, I can copy the formula from the file that is behaving
strangely into the other file and it will not change when I add a row
in that file, but in the original file it will still change whenever I
add a row.....

Thanks

cb

 

[Earl Kiosterud]

cb,

That is the strange thing......the cell reference is not actually
moving, as I am adding the row to a different sheet.

What? Methinks you aren't telling all.

 

[cb95amc]

Earl,

I wish that were the case......I am starting to think that the file has
been corrupted somewhere along the line.......It just doesn't seem to
make any sense.

If it didn't contain loads of confidential data I would send you the
file so you could see for yourself.

Thanks

cb

 

[Earl Kiosterud]

cb,

When I said "What?" I was questioning your statement about "adding the row
to a different sheet."

Apparently, your requirements are that you want a variable sheet name (B4),
and want to refer to K108, and be able to copy the formula laterally and get
L108, etc. You don't want inserted or deleted rows above row 108 to change
the reference to K108. Try this:

=INDIRECT("'" & $B$4 & "'!" & CELL("Address",OFFSET(K1, 107, 0, 1, 1)))

 

[cb95amc]

Earl,

Great......Now I understand what you mean......Your solution worked
perfectly.

Many thanks

cb

 

[cb95amc]

Earl,

Great......Now I understand what you mean......Your solution worked
perfectly.

Many thanks

cb