Cell Reference

[Mary]

Hello,
I have a master worksheet on which I have referenced cells from other
worksheets in my file. When I delete these other worksheets, I get a Ref#
error. How can I program the master worksheet to keep the reference intact so
that when worksheets are added back in, the reference is valid?
Thank you in advance for your reply,

 

[Barb Reinhardt]

Let's say your formula is:

=Sheet3!B9

It can be changed to
=INDIRECT("Sheet3!B9")

and you can delete Sheet3. THis will give you a place to start.

 

[Brian Taylor]

The only bummer about that is that you can't drag down the formula and
have it stay relative (B9,B10,B1,etc.) If you have a spreadsheet that
is hundreds of rows long that is a REAL bummer. Sometimes when working
with Indirects to get around this I also use the row() and column()
functions so you can drag it still. Something like:

=Indirect("Sheet3!B" & row())

Or row plus some amount if you need to tweak it.

 

[Peo Sjoblom]

Or the more robust

=INDIRECT("Sheet3!"&CELL("address",B9))


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



that way you won't get an erroneous result if there is a row inserted above
the formula

 

[Brian Taylor]

Good call.

 

[Mary]

Hello Peo,
What is address?
Would this be an accurate example of your formula:
=INDIRECT("Sheet3!"&CELL("c:\filename.xls",B9)) ?
Thank you!!
m

 

[Peo Sjoblom]

Actually "address" is part of the function so it is not the path of the
file, in this case it's just a way of being able to copy down/across a
formula and having the row/column to increase. You cannot use INDIRECT with
a closed file like in your example, the other workbook needs to be open


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com