Help! Keep formulas from updating refs when copied??

[Ed]

This is getting frustrating! I've got a series of summary tables on one
page. Each table is taking data from the exact same locations on other
sheets (that is, one table will use Sheet1!B3, and the next table
Sheet2!B3). But each time I try to copy the summary table down the page,
hoping to simply change Sheet1! to Sheet2!, the B3 reference will
automatically update the number of rows I've copied down, and B3 is now B7!
I tried Copy>>Paste Special (Formulas), but I keep getting the same update
frustration! Any help?

Ed

[HansM]

This is where you use the function key F4 or manually insert the absolute
mark $ in front and after the cell letter reference.

Ie. what used to be A1 becomes $A$1 and now won't change the 1 when you
copy.

Experiment with using a $ mark on one side or the other to see how that can
also be helpful sometimes.
--
Hans
"Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
news:[email protected]...
> This is getting frustrating! I've got a series of summary tables on one
> page. Each table is taking data from the exact same locations on other
> sheets (that is, one table will use Sheet1!B3, and the next table
> Sheet2!B3). But each time I try to copy the summary table down the page,
> hoping to simply change Sheet1! to Sheet2!, the B3 reference will
> automatically update the number of rows I've copied down, and B3 is now
> B7!
> I tried Copy>>Paste Special (Formulas), but I keep getting the same update
> frustration! Any help?
>
> Ed
>
>

[Ed]

Thanks for the reply, Hans. I understand the $ - I didn't want to have to
go back in and change every reference!! I looked up the F4 key in Help and
it simply defined it as "Absolute/Relative". But I don't understand how to
use it. Can you explain a bit more?

Ed

"HansM" <[email protected]> wrote in message
news:[email protected]...
> This is where you use the function key F4 or manually insert the absolute
> mark $ in front and after the cell letter reference.
>
> Ie. what used to be A1 becomes $A$1 and now won't change the 1 when you
> copy.
>
> Experiment with using a $ mark on one side or the other to see how that
can
> also be helpful sometimes.
> --
> Hans
> "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> news:[email protected]...
> > This is getting frustrating! I've got a series of summary tables on one
> > page. Each table is taking data from the exact same locations on other
> > sheets (that is, one table will use Sheet1!B3, and the next table
> > Sheet2!B3). But each time I try to copy the summary table down the
page,
> > hoping to simply change Sheet1! to Sheet2!, the B3 reference will
> > automatically update the number of rows I've copied down, and B3 is now
> > B7!
> > I tried Copy>>Paste Special (Formulas), but I keep getting the same
update
> > frustration! Any help?
> >
> > Ed
> >
> >
>
>

[Guillermo]

The problem is that sometimes you don't really want the $ sign, because you
actaully need the values to change while constructing the table, and then
the only thing you need is to make an exact copy of your table.
I asked that before, and I really wish there was a "Paste exactly the same"
option in paste special, just like what happens when you cut and paste, but
leaving a copy in its original place. I have excel 2000 and I don't think
they've put something like that in all the newer versions...

a trick that works is:
select your range
edit/replace...... replace all "=" with something uncommon like #####
then you copy and paste (the formulas are no longer formulas so they won't
change)
then edit/replace #### with "=" again


guillermo

"HansM" <[email protected]> wrote in message
news:[email protected]...
> This is where you use the function key F4 or manually insert the absolute
> mark $ in front and after the cell letter reference.
>
> Ie. what used to be A1 becomes $A$1 and now won't change the 1 when you
> copy.
>
> Experiment with using a $ mark on one side or the other to see how that
can
> also be helpful sometimes.
> --
> Hans
> "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> news:[email protected]...
> > This is getting frustrating! I've got a series of summary tables on one
> > page. Each table is taking data from the exact same locations on other
> > sheets (that is, one table will use Sheet1!B3, and the next table
> > Sheet2!B3). But each time I try to copy the summary table down the
page,
> > hoping to simply change Sheet1! to Sheet2!, the B3 reference will
> > automatically update the number of rows I've copied down, and B3 is now
> > B7!
> > I tried Copy>>Paste Special (Formulas), but I keep getting the same
update
> > frustration! Any help?
> >
> > Ed
> >
> >
>
>

[Ken Wright]

John Walkenbach has that very option in his PUP6 addin, as well as a hell of
a lot more:-

http://j-walk.com/ss/pup/pup6/index.htm

There is a trial version on that link as well.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Guillermo" <[email protected]> wrote in message
news:[email protected]...
> The problem is that sometimes you don't really want the $ sign, because
you
> actaully need the values to change while constructing the table, and then
> the only thing you need is to make an exact copy of your table.
> I asked that before, and I really wish there was a "Paste exactly the
same"
> option in paste special, just like what happens when you cut and paste,
but
> leaving a copy in its original place. I have excel 2000 and I don't think
> they've put something like that in all the newer versions...
>
> a trick that works is:
> select your range
> edit/replace...... replace all "=" with something uncommon like #####
> then you copy and paste (the formulas are no longer formulas so they won't
> change)
> then edit/replace #### with "=" again
>
>
> guillermo
>
> "HansM" <[email protected]> wrote in message
> news:[email protected]...
> > This is where you use the function key F4 or manually insert the
absolute
> > mark $ in front and after the cell letter reference.
> >
> > Ie. what used to be A1 becomes $A$1 and now won't change the 1 when you
> > copy.
> >
> > Experiment with using a $ mark on one side or the other to see how that
> can
> > also be helpful sometimes.
> > --
> > Hans
> > "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> > news:[email protected]...
> > > This is getting frustrating! I've got a series of summary tables on
one
> > > page. Each table is taking data from the exact same locations on
other
> > > sheets (that is, one table will use Sheet1!B3, and the next table
> > > Sheet2!B3). But each time I try to copy the summary table down the
> page,
> > > hoping to simply change Sheet1! to Sheet2!, the B3 reference will
> > > automatically update the number of rows I've copied down, and B3 is
now
> > > B7!
> > > I tried Copy>>Paste Special (Formulas), but I keep getting the same
> update
> > > frustration! Any help?
> > >
> > > Ed
> > >
> > >
> >
> >
>
>

[Ken Wright]

<snip>

[Ed]

That sounds like an excellent idea, Guillermo. Thanks for the tip!

Ed

"Guillermo" <[email protected]> wrote in message
news:[email protected]...
> The problem is that sometimes you don't really want the $ sign, because
you
> actaully need the values to change while constructing the table, and then
> the only thing you need is to make an exact copy of your table.
> I asked that before, and I really wish there was a "Paste exactly the
same"
> option in paste special, just like what happens when you cut and paste,
but
> leaving a copy in its original place. I have excel 2000 and I don't think
> they've put something like that in all the newer versions...
>
> a trick that works is:
> select your range
> edit/replace...... replace all "=" with something uncommon like #####
> then you copy and paste (the formulas are no longer formulas so they won't
> change)
> then edit/replace #### with "=" again
>
>
> guillermo
>
> "HansM" <[email protected]> wrote in message
> news:[email protected]...
> > This is where you use the function key F4 or manually insert the
absolute
> > mark $ in front and after the cell letter reference.
> >
> > Ie. what used to be A1 becomes $A$1 and now won't change the 1 when you
> > copy.
> >
> > Experiment with using a $ mark on one side or the other to see how that
> can
> > also be helpful sometimes.
> > --
> > Hans
> > "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> > news:[email protected]...
> > > This is getting frustrating! I've got a series of summary tables on
one
> > > page. Each table is taking data from the exact same locations on
other
> > > sheets (that is, one table will use Sheet1!B3, and the next table
> > > Sheet2!B3). But each time I try to copy the summary table down the
> page,
> > > hoping to simply change Sheet1! to Sheet2!, the B3 reference will
> > > automatically update the number of rows I've copied down, and B3 is
now
> > > B7!
> > > I tried Copy>>Paste Special (Formulas), but I keep getting the same
> update
> > > frustration! Any help?
> > >
> > > Ed
> > >
> > >
> >
> >
>
>

[Bill Ridgeway]

One further point on the use of absolute references.

If the two worksheets (pages) are in the same spreadsheet (file) you can
move the source cell and the absolute reference will change accordingly. If
however they are in different spreadsheets this will not, of course occur.

The moral of the story is to keep such cross references in the same file and
you will reduce the possibility of errors occurring.

Regards.

Bill Ridgeway
Computer Solutions

"HansM" <[email protected]> wrote in message
news:[email protected]...
> This is where you use the function key F4 or manually insert the absolute
> mark $ in front and after the cell letter reference.
>
> Ie. what used to be A1 becomes $A$1 and now won't change the 1 when you
> copy.
>
> Experiment with using a $ mark on one side or the other to see how that
> can also be helpful sometimes.
> --
> Hans
> "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> news:[email protected]...
>> This is getting frustrating! I've got a series of summary tables on one
>> page. Each table is taking data from the exact same locations on other
>> sheets (that is, one table will use Sheet1!B3, and the next table
>> Sheet2!B3). But each time I try to copy the summary table down the page,
>> hoping to simply change Sheet1! to Sheet2!, the B3 reference will
>> automatically update the number of rows I've copied down, and B3 is now
>> B7!
>> I tried Copy>>Paste Special (Formulas), but I keep getting the same
>> update
>> frustration! Any help?
>>
>> Ed
>>
>>
>
>