Copying Formulas From One Column To Another??

[Guest]

I am setting up a worksheet to count the same data range but for various
people. Example
I am using the SUMPRODUCT function to add C1:C100 IF D1100=DENISE AND
C1:C100=X.
I would like to use the exact same data range but add for RYAN instead of
Denise. I have Ryan set up in his own column but when I copy and paste the
formula from Denise's column, it moves the ranges; C1:C100 becomes C2:C101
AND D1100 becomes E1:E100 or something similar to that... it varies
sometimes and I'm sure it's because of how I am copying, etc.

Any help would be greatly appreciated as it's getting to be more trouble
than it's worth to go in and change each formula for each cell.

Thanks!

 

[PCLIVE]

Use dollar signs to make your column references constant.
Example.
$C1:$C100 IF $D1:$D100=DENISE AND $C1:$C100=X

Regards,
Paul

 

[Guest]

Thanks- that definitely did the trick. My other question- if you can help-
would be is there an easier way to enter my formulas meaning... if i have
column a set up to be what i want it to add for (1st time in, past customer,
etc) and the formula for denise is set up in column b as per my previous
question. If i am trying to now enter the formulas under denise's colum
($c1:$c100 = denise, then add $d1:$d100 =x) then i would like the next cell
below that to add ($c1:$c100= denise, then add $e1:$100=x as it pulls from
another worksheet in the same workbook. My question is is there any way to
"fill" (probably another term for this) the cells below the first one to go
from d to e to f to g while keeping c constant- does that make sense?

Thanks!

 

[Guest]

Anddddd back to my first question... I inserted the "$" and it worked while I
was copying going across the worksheet. Now I would like to copy to cells
further down on the worksheet and it is changing my "$c1" to "$c14"... any
suggestions???

 

[PCLIVE]

Same thing. Add $ before the column reference.
Example.
$C$1:$C$100 IF $D$1:$D$100=DENISE AND $C$1:$C$100=X

 

[PCLIVE]

Sorry,

You lost me on this one. I'm not sure what it is you're trying to do.

 

[Gord Dibben]

Leslie

Make the references Absolute.

$A$1 is absolute column and row

$A1 is absolute column and relative row. Copy down and row number changes.

A1 is relative row and column

A$1 is relative row and absolute column.. Copy across and column letter
changes.

See help on relative and absolute cell references for more on this.


Gord Dibben MS Excel MVP

Anddddd back to my first question... I inserted the "$" and it worked while I
was copying going across the worksheet. Now I would like to copy to cells
further down on the worksheet and it is changing my "$c1" to "$c14"... any
suggestions???

Gord Dibben MS Excel MVP

 

[Guest]

Sorry- It's hard to explain...
I have Worksheet 1 set up to take data. I have colums going across that are
marked with an X if they qualify... example going across i have past
customer, first time in, referral, etc. Then each customer is logged and an X
is placed under the appropriate column. The next page in the worksheet is
set up to add up all of the data. I have going down column A the same things
that are going across in the first worksheet, and I am adding them up for
each salesperson. Once I set up the initial formula under "Denise" ... an
example would be =SUMPRODUCT(--('Nov 06'!$D$1:$D$100="DENISE"),--('Nov
06'!$G$1:$G$100="X")) set up with the "$" so that I can move them from Denise
to Ryan to Michele, etc. I would like to be able to copy that formula to the
cell below it which may be labeled "First Time In" so I would need the
formula to be exactly the same except instead of the 2nd part of the function
being "G" I would need it to be "H". Is there any tool that I can use so
that it will change that qualifier so I didn't have to go in to each cell to
change it.

 

[Guest]

What I think you're asking is how to keep formulas references either relative
or absolute as needed as you copy from one cell to another. Using a dollar
sign ($) in the formula will keep the reference absolute, otherwise
references are relative. Here's a simple example of one way to accomplish
your task.

A B C
1 Denise Ryan
2 A 40 33
3 B 34 13
4 X 12 13
5 C 6 5
6 D 83 81
7 X 24 14
8
9 X 36 27

If A1:C7 is your database and you want a sum of Denise's widgets that have a
value of X in column A, enter the following formula in B9:

=SUMPRODUCT(($A9=$A$2:$A$7)*(B2:B7))

Then copy B9 to C9. When you do, C9 will show the correct relative and
absolute references.

=SUMPRODUCT(($A8=$A$2:$A$7)*(C2:C7))

SUMPRODUCT is a very useful function because it enables you to do more
complex queries than the simple example above. Learning about relative
versus absolute references is a critically important concept whenever you
copy formulas, regardless of what function you're using. Hope this helps.

 

[PCLIVE]

I would just copy the top cell, including the formula, paste it in the new
column as needed...then manually change the formula to suit your needs.
Once done, copy the formula down as needed.

There may be other ways, but I'm not sure of one without using VBA.

HTH,
Paul

 

[Guest]

Thanks for all of your help- I think it's actually working! Last question for
the day (I promise)...

I am setting up the 3rd sheet in this workbook. If I want it to say... "If
the column labeled "sold"--column d-- on worksheet one ("Customers) is
checked for the range of cells d1:d100 i want the customers name which
appears in column a of that same worksheet in the same row as the "x" to mark
them as sold to then appear in column a of a different worksheet. Any
suggestions? THANKS AGAIN!!!