software

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(quot;Sheet3!B9quot;)

and you can delete Sheet3. THis will give you a place to start.quot;Maryquot; wrote:

gt; Hello,
gt; I have a master worksheet on which I have referenced cells from other
gt; worksheets in my file. When I delete these other worksheets, I get a Ref#
gt; error. How can I program the master worksheet to keep the reference intact so
gt; that when worksheets are added back in, the reference is valid?
gt; 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(quot;Sheet3!Bquot; amp; row())

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

=INDIRECT(quot;Sheet3!quot;amp;CELL(quot;addressquot;,B9))--

Regards,

Peo Sjoblom

nwexcelsolutions.com
that way you won't get an erroneous result if there is a row inserted above
the formula
quot;Brian Taylorquot; gt; wrote in message oups.com...
gt; The only bummer about that is that you can't drag down the formula and
gt; have it stay relative (B9,B10,B1,etc.) If you have a spreadsheet that
gt; is hundreds of rows long that is a REAL bummer. Sometimes when working
gt; with Indirects to get around this I also use the row() and column()
gt; functions so you can drag it still. Something like:
gt;
gt; =Indirect(quot;Sheet3!Bquot; amp; row())
gt;
gt; Or row plus some amount if you need to tweak it.
gt;
Good call.Hello Peo,
What is address?
Would this be an accurate example of your formula:
=INDIRECT(quot;Sheet3!quot;amp;CELL(quot;c:\filename.xlsquot;,B9)) ?
Thank you!!
m

quot;Peo Sjoblomquot; wrote:

gt; Or the more robust
gt;
gt; =INDIRECT(quot;Sheet3!quot;amp;CELL(quot;addressquot;,B9))
gt;
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; nwexcelsolutions.com
gt;
gt;
gt;
gt; that way you won't get an erroneous result if there is a row inserted above
gt; the formula
gt; quot;Brian Taylorquot; gt; wrote in message
gt; oups.com...
gt; gt; The only bummer about that is that you can't drag down the formula and
gt; gt; have it stay relative (B9,B10,B1,etc.) If you have a spreadsheet that
gt; gt; is hundreds of rows long that is a REAL bummer. Sometimes when working
gt; gt; with Indirects to get around this I also use the row() and column()
gt; gt; functions so you can drag it still. Something like:
gt; gt;
gt; gt; =Indirect(quot;Sheet3!Bquot; amp; row())
gt; gt;
gt; gt; Or row plus some amount if you need to tweak it.
gt; gt;
gt;
gt;
gt;

Actually quot;addressquot; 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

nwexcelsolutions.comquot;Maryquot; gt; wrote in message
...
gt; Hello Peo,
gt; What is address?
gt; Would this be an accurate example of your formula:
gt; =INDIRECT(quot;Sheet3!quot;amp;CELL(quot;c:\filename.xlsquot;,B9)) ?
gt; Thank you!!
gt; m
gt;
gt; quot;Peo Sjoblomquot; wrote:
gt;
gt;gt; Or the more robust
gt;gt;
gt;gt; =INDIRECT(quot;Sheet3!quot;amp;CELL(quot;addressquot;,B9))
gt;gt;
gt;gt;
gt;gt; --
gt;gt;
gt;gt; Regards,
gt;gt;
gt;gt; Peo Sjoblom
gt;gt;
gt;gt; nwexcelsolutions.com
gt;gt;
gt;gt;
gt;gt;
gt;gt; that way you won't get an erroneous result if there is a row inserted
gt;gt; above
gt;gt; the formula
gt;gt; quot;Brian Taylorquot; gt; wrote in message
gt;gt; oups.com...
gt;gt; gt; The only bummer about that is that you can't drag down the formula and
gt;gt; gt; have it stay relative (B9,B10,B1,etc.) If you have a spreadsheet that
gt;gt; gt; is hundreds of rows long that is a REAL bummer. Sometimes when working
gt;gt; gt; with Indirects to get around this I also use the row() and column()
gt;gt; gt; functions so you can drag it still. Something like:
gt;gt; gt;
gt;gt; gt; =Indirect(quot;Sheet3!Bquot; amp; row())
gt;gt; gt;
gt;gt; gt; Or row plus some amount if you need to tweak it.
gt;gt; gt;
gt;gt;
gt;gt;
gt;gt;