[SOLVED] How/can you create absolute values for cell references that have sum(a1:a4)?

[Marc]

Many of my references link to another work sheet and "paste special" gives
me absolute values but you can't at least that I know of use these easily in
"sum".

Marc

Hi
If I understand you correctly (forgive me if I don't!) you can use Copy and
Paste Special Values on sums in exactly the same way as references. An
alternative is to select the cell(s) and hit F2, F9, Enter.

--
Andy.


"Marc" <mcneer(N_O-S_P_A_M)@mindspring.com> wrote in message
news:F7rbe.13061$sp3.5102@newsread3.news.atl.earthlink.net...
> Many of my references link to another work sheet and "paste special" gives
> me absolute values but you can't at least that I know of use these easily
> in
> "sum".
>
> Marc
>
>

[Marc]

I'm working on formula on one sheet that reference another sheet. If I use
sum and I drag the cells I get a1:a4. I can click one each cell
independently and get a1+a2+a3+a4 but none of these are absolute. I've
tried using paste special but it kicks me out of the original formula. I
have spent to much time trying to make sure the values are absolute.

Marc

<Andy B> wrote in message news:%23puyQNnSFHA.164@TK2MSFTNGP12.phx.gbl...
> Hi
> If I understand you correctly (forgive me if I don't!) you can use Copy
and
> Paste Special Values on sums in exactly the same way as references. An
> alternative is to select the cell(s) and hit F2, F9, Enter.
>
> --
> Andy.
>
>
> "Marc" <mcneer(N_O-S_P_A_M)@mindspring.com> wrote in message
> news:F7rbe.13061$sp3.5102@newsread3.news.atl.earthlink.net...
> > Many of my references link to another work sheet and "paste special"
gives
> > me absolute values but you can't at least that I know of use these
easily
> > in
> > "sum".
> >
> > Marc
> >
> >
>
>

[Bernie Deitrick]

Marc,

Press F4 when your cursor is on (or next to) the range address in the
formula bar.

Subsequent presses of F4 will rotate through the available reference styles.

HTH,
Bernie
MS Excel MVP


"Marc" <mcneer(N_O-S_P_A_M)@mindspring.com> wrote in message
news:lktbe.15181$go4.5891@newsread2.news.atl.earthlink.net...
> I'm working on formula on one sheet that reference another sheet. If I
use
> sum and I drag the cells I get a1:a4. I can click one each cell
> independently and get a1+a2+a3+a4 but none of these are absolute. I've
> tried using paste special but it kicks me out of the original formula. I
> have spent to much time trying to make sure the values are absolute.
>
> Marc
>
> <Andy B> wrote in message news:%23puyQNnSFHA.164@TK2MSFTNGP12.phx.gbl...
> > Hi
> > If I understand you correctly (forgive me if I don't!) you can use Copy
> and
> > Paste Special Values on sums in exactly the same way as references. An
> > alternative is to select the cell(s) and hit F2, F9, Enter.
> >
> > --
> > Andy.
> >
> >
> > "Marc" <mcneer(N_O-S_P_A_M)@mindspring.com> wrote in message
> > news:F7rbe.13061$sp3.5102@newsread3.news.atl.earthlink.net...
> > > Many of my references link to another work sheet and "paste special"
> gives
> > > me absolute values but you can't at least that I know of use these
> easily
> > > in
> > > "sum".
> > >
> > > Marc
> > >
> > >
> >
> >
>
>

[Marc]

THANK YOU SO VERY MUCH. That has been drving me crazy!!!

Marc

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:eOPjienSFHA.3184@TK2MSFTNGP09.phx.gbl...
> Marc,
>
> Press F4 when your cursor is on (or next to) the range address in the
> formula bar.
>
> Subsequent presses of F4 will rotate through the available reference
styles.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Marc" <mcneer(N_O-S_P_A_M)@mindspring.com> wrote in message
> news:lktbe.15181$go4.5891@newsread2.news.atl.earthlink.net...
> > I'm working on formula on one sheet that reference another sheet. If I
> use
> > sum and I drag the cells I get a1:a4. I can click one each cell
> > independently and get a1+a2+a3+a4 but none of these are absolute. I've
> > tried using paste special but it kicks me out of the original formula.
I
> > have spent to much time trying to make sure the values are absolute.
> >
> > Marc
> >
> > <Andy B> wrote in message news:%23puyQNnSFHA.164@TK2MSFTNGP12.phx.gbl...
> > > Hi
> > > If I understand you correctly (forgive me if I don't!) you can use
Copy
> > and
> > > Paste Special Values on sums in exactly the same way as references. An
> > > alternative is to select the cell(s) and hit F2, F9, Enter.
> > >
> > > --
> > > Andy.
> > >
> > >
> > > "Marc" <mcneer(N_O-S_P_A_M)@mindspring.com> wrote in message
> > > news:F7rbe.13061$sp3.5102@newsread3.news.atl.earthlink.net...
> > > > Many of my references link to another work sheet and "paste special"
> > gives
> > > > me absolute values but you can't at least that I know of use these
> > easily
> > > > in
> > > > "sum".
> > > >
> > > > Marc
> > > >
> > > >
> > >
> > >
> >
> >
>
>

[zackb]

Note that it is dependent upon only where your cursor is located. If you
select two addresses (either a Union or an Intersect) and press F4 (even
partial selections count) then all said ranges will be converted to the
cycle of Absolute/Relative (Relative | Absolute | Row Absolute | Col
Absolute).

Regards,
Zack Barresse



"Marc" <mcneer(N_O-S_P_A_M)@mindspring.com> wrote in message
news:D7ube.17056$44.12431@newsread1.news.atl.earthlink.net...
> THANK YOU SO VERY MUCH. That has been drving me crazy!!!
>
> Marc
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:eOPjienSFHA.3184@TK2MSFTNGP09.phx.gbl...
>> Marc,
>>
>> Press F4 when your cursor is on (or next to) the range address in the
>> formula bar.
>>
>> Subsequent presses of F4 will rotate through the available reference
> styles.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "Marc" <mcneer(N_O-S_P_A_M)@mindspring.com> wrote in message
>> news:lktbe.15181$go4.5891@newsread2.news.atl.earthlink.net...
>> > I'm working on formula on one sheet that reference another sheet. If I
>> use
>> > sum and I drag the cells I get a1:a4. I can click one each cell
>> > independently and get a1+a2+a3+a4 but none of these are absolute. I've
>> > tried using paste special but it kicks me out of the original formula.
> I
>> > have spent to much time trying to make sure the values are absolute.
>> >
>> > Marc
>> >
>> > <Andy B> wrote in message
>> > news:%23puyQNnSFHA.164@TK2MSFTNGP12.phx.gbl...
>> > > Hi
>> > > If I understand you correctly (forgive me if I don't!) you can use
> Copy
>> > and
>> > > Paste Special Values on sums in exactly the same way as references.
>> > > An
>> > > alternative is to select the cell(s) and hit F2, F9, Enter.
>> > >
>> > > --
>> > > Andy.
>> > >
>> > >
>> > > "Marc" <mcneer(N_O-S_P_A_M)@mindspring.com> wrote in message
>> > > news:F7rbe.13061$sp3.5102@newsread3.news.atl.earthlink.net...
>> > > > Many of my references link to another work sheet and "paste
>> > > > special"
>> > gives
>> > > > me absolute values but you can't at least that I know of use these
>> > easily
>> > > > in
>> > > > "sum".
>> > > >
>> > > > Marc
>> > > >
>> > > >
>> > >
>> > >
>> >
>> >
>>
>>
>
>