Question about copy/paste functions

Kevin · 05-27-2006, 01:40 PM

I'm trying to copy and paste contents of a cell to another cell in order to
complete an entire column (about 300 rows).

The contents of the cell is a function which acts on data on two separate
worksheets.

I want the copy/paste to update some of the arguments of the function
(arguments that change with each row) but not other parts (arguments from the
second worksheet that don't change)

The problem is that everytime I paste the function, it wants to
automatically update ALL the arguments of the function.

What I'm trying to copy/paste:

=SUM(PRODUCT(E1,AH!B2),PRODUCT(F1,AH!B3),PRODUCT(G1,AH!B4),PRODUCT(H1,AH!B5))

to then make rows like this:

=SUM(PRODUCT(E1,AH!B2),PRODUCT(F1,AH!B3),PRODUCT(G1,AH!B4),PRODUCT(H1,AH!B5))
=SUM(PRODUCT(E2,AH!B2),PRODUCT(F2,AH!B3),PRODUCT(G2,AH!B4),PRODUCT(H2,AH!B5))
=SUM(PRODUCT(E3,AH!B2),PRODUCT(F3,AH!B3),PRODUCT(G3,AH!B4),PRODUCT(H3,AH!B5))
=SUM(PRODUCT(E4,AH!B2),PRODUCT(F4,AH!B3),PRODUCT(G4,AH!B4),PRODUCT(H4,AH!B5))
=SUM(PRODUCT(E5,AH!B2),PRODUCT(F5,AH!B3),PRODUCT(G5,AH!B4),PRODUCT(H5,AH!B5))
.... etc ...

What I'm getting is:

=SUM(PRODUCT(E1,AH!B2),PRODUCT(F1,AH!B3),PRODUCT(G1,AH!B4),PRODUCT(H1,AH!B5))
=SUM(PRODUCT(E2,AH!B2),PRODUCT(F2,AH!B3),PRODUCT(G2,AH!B4),PRODUCT(H2,AH!B5))
=SUM(PRODUCT(E3,AH!B3),PRODUCT(F3,AH!B4),PRODUCT(G3,AH!B5),PRODUCT(H3,AH!B6))
=SUM(PRODUCT(E4,AH!B4),PRODUCT(F4,AH!B5),PRODUCT(G4,AH!B6),PRODUCT(H4,AH!B7))
=SUM(PRODUCT(E5,AH!B5),PRODUCT(F5,AH!B6),PRODUCT(G5,AH!B7),PRODUCT(H5,AH!B8))
.... etc ...

As you can see in the first example; I only want to update the first
argument in each PRODUCT(X,Y)... but what I'm getting is both being updated
which doesn't work for what I'm doing.

I've tried copying and pasting cell by cell, copying and pasting multiple
sells, using Edit->Fill ..., and Paste Special - and I can't seem to figure
this out other than to manually enter this for every single cell (which is
prohibitively too labour intensive because what I'm actually trying to do is
much more complicated than this example)

If this makes any sense, I'd appreciate your help

- Thanks! Kevin

excelent · 05-27-2006, 01:50 PM

if i got ur right try

=SUM(PRODUCT(E1,AH!B2),PRODUCT(F1,AH!$B$3),PRODUCT(G1,AH!$B$4),PRODUCT(H1,AH!$B$5))

"Kevin" skrev:

> I'm trying to copy and paste contents of a cell to another cell in order to
> complete an entire column (about 300 rows).
>
> The contents of the cell is a function which acts on data on two separate
> worksheets.
>
> I want the copy/paste to update some of the arguments of the function
> (arguments that change with each row) but not other parts (arguments from the
> second worksheet that don't change)
>
> The problem is that everytime I paste the function, it wants to
> automatically update ALL the arguments of the function.
>
> What I'm trying to copy/paste:
>
> =SUM(PRODUCT(E1,AH!B2),PRODUCT(F1,AH!B3),PRODUCT(G1,AH!B4),PRODUCT(H1,AH!B5))
>
> to then make rows like this:
>
> =SUM(PRODUCT(E1,AH!B2),PRODUCT(F1,AH!B3),PRODUCT(G1,AH!B4),PRODUCT(H1,AH!B5))
> =SUM(PRODUCT(E2,AH!B2),PRODUCT(F2,AH!B3),PRODUCT(G2,AH!B4),PRODUCT(H2,AH!B5))
> =SUM(PRODUCT(E3,AH!B2),PRODUCT(F3,AH!B3),PRODUCT(G3,AH!B4),PRODUCT(H3,AH!B5))
> =SUM(PRODUCT(E4,AH!B2),PRODUCT(F4,AH!B3),PRODUCT(G4,AH!B4),PRODUCT(H4,AH!B5))
> =SUM(PRODUCT(E5,AH!B2),PRODUCT(F5,AH!B3),PRODUCT(G5,AH!B4),PRODUCT(H5,AH!B5))
> ... etc ...
>
> What I'm getting is:
>
> =SUM(PRODUCT(E1,AH!B2),PRODUCT(F1,AH!B3),PRODUCT(G1,AH!B4),PRODUCT(H1,AH!B5))
> =SUM(PRODUCT(E2,AH!B2),PRODUCT(F2,AH!B3),PRODUCT(G2,AH!B4),PRODUCT(H2,AH!B5))
> =SUM(PRODUCT(E3,AH!B3),PRODUCT(F3,AH!B4),PRODUCT(G3,AH!B5),PRODUCT(H3,AH!B6))
> =SUM(PRODUCT(E4,AH!B4),PRODUCT(F4,AH!B5),PRODUCT(G4,AH!B6),PRODUCT(H4,AH!B7))
> =SUM(PRODUCT(E5,AH!B5),PRODUCT(F5,AH!B6),PRODUCT(G5,AH!B7),PRODUCT(H5,AH!B8))
> ... etc ...
>
> As you can see in the first example; I only want to update the first
> argument in each PRODUCT(X,Y)... but what I'm getting is both being updated
> which doesn't work for what I'm doing.
>
> I've tried copying and pasting cell by cell, copying and pasting multiple
> sells, using Edit->Fill ..., and Paste Special - and I can't seem to figure
> this out other than to manually enter this for every single cell (which is
> prohibitively too labour intensive because what I'm actually trying to do is
> much more complicated than this example)
>
> If this makes any sense, I'd appreciate your help
>
> - Thanks! Kevin

**bgeier** · 05-27-2006, 11:18 PM

If I am reading your post correctly, you want all "B2", "B3", "B4", "B5" references to remain "B2", "B3", "B4", and "B5"

the easiest way I can think of is to open your "source" sheet, select the columns where the formulae are located, then
search for "B2"
replace with "$B$2"

then repeat for "B3", "B4", "B5".

As an alternative, you cound do a search on "B" and replace with "$B$", but personally, I would "feel" safer doing it a step at a time, just to verify I am not changing anything I do not want changed.