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,

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.


"Mary" wrote:

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,

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.

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" wrote in message
oups.com...
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.

Good call.

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" wrote:

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" wrote in message
oups.com...
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.

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





"Mary" wrote in message
...
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" wrote:

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" wrote in message
oups.com...
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.