Trouble linking workbooks in excel to stay constant

[$harebear]

I have a problem with my linking remaining constant between two work
books. I am linking 2 cells in 2 separate workbooks using the
following formula:

='[2009 Project Tracking ~ Sheila.xls]G5827'!$A$21

When I insert a row above A21 in the source workbook the formula
automatically updates to:

='[2009 Project Tracking ~ Sheila.xls]G5827'!$A$22

What I am trying to do is keep the cell constant and have the link
stay with that new row. The purpose is one workbook only contains the
most current update and the source workbook retains historical data.

Any help is greatly appreciated! Thank you!

 

[Dave Peterson]

If the receiving workbook is closed when you update the sending workbook, then
your formulas won't change.

You could use =indirect(), but that will fail if you close that sending
workbook.

But maybe you could use something like:
=index('[2009 Project Tracking ~ Sheila.xls]G5827'!a:a,21)

But the problem with that is the formula may fail with a "Cannot complete this
task with the available resources" if the sending workbook is closed.

=index('[2009 Project Tracking ~ Sheila.xls]G5827'!$a$1:$a$999,21)
(use just enough rows to cover the range)

I have a problem with my linking remaining constant between two work
books. I am linking 2 cells in 2 separate workbooks using the
following formula:

='[2009 Project Tracking ~ Sheila.xls]G5827'!$A$21

When I insert a row above A21 in the source workbook the formula
automatically updates to:

='[2009 Project Tracking ~ Sheila.xls]G5827'!$A$22

What I am trying to do is keep the cell constant and have the link
stay with that new row. The purpose is one workbook only contains the
most current update and the source workbook retains historical data.

Any help is greatly appreciated! Thank you!

 

[$harebear]

Closing the receiving workbook should work for my purposes. Thank
you!

If the receiving workbook is closed when you update the sending workbook,then
your formulas won't change.

You could use =indirect(), but that will fail if you close that sending
workbook.

But maybe you could use something like:
=index('[2009 Project Tracking ~ Sheila.xls]G5827'!a:a,21)

But the problem with that is the formula may fail with a "Cannot completethis
task with the available resources" if the sending workbook is closed.

=index('[2009 Project Tracking ~ Sheila.xls]G5827'!$a$1:$a$999,21)
(use just enough rows to cover the range)

I have a problem with my linking remaining constant between two work
books. I am linking 2 cells in 2 separate workbooks using the
following formula:

='[2009 Project Tracking ~ Sheila.xls]G5827'!$A$21

When I insert a row above A21 in the source workbook the formula
automatically updates to:

='[2009 Project Tracking ~ Sheila.xls]G5827'!$A$22

What I am trying to do is keep the cell constant and have the link
stay with that new row. The purpose is one workbook only contains the
most current update and the source workbook retains historical data.

Any help is greatly appreciated! Thank you!

--

Dave Peterson- Hide quoted text -

- Show quoted text -