Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying Formulas From One Column To Another??
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 D1:D100=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 D1:D100 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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying Formulas From One Column To Another??
Use dollar signs to make your column references constant.
Example. $C1:$C100 IF $D1:$D100=DENISE AND $C1:$C100=X Regards, Paul "Leslie M" wrote in message ... 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 D1:D100=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 D1:D100 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying Formulas From One Column To Another??
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! "PCLIVE" wrote: Use dollar signs to make your column references constant. Example. $C1:$C100 IF $D1:$D100=DENISE AND $C1:$C100=X Regards, Paul "Leslie M" wrote in message ... 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 D1:D100=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 D1:D100 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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying Formulas From One Column To Another??
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" wrote: Use dollar signs to make your column references constant. Example. $C1:$C100 IF $D1:$D100=DENISE AND $C1:$C100=X Regards, Paul "Leslie M" wrote in message ... 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 D1:D100=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 D1:D100 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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying Formulas From One Column To Another??
Same thing. Add $ before the column reference.
Example. $C$1:$C$100 IF $D$1:$D$100=DENISE AND $C$1:$C$100=X "Leslie M" wrote in message ... 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" wrote: Use dollar signs to make your column references constant. Example. $C1:$C100 IF $D1:$D100=DENISE AND $C1:$C100=X Regards, Paul "Leslie M" wrote in message ... 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 D1:D100=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 D1:D100 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! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying Formulas From One Column To Another??
Sorry,
You lost me on this one. I'm not sure what it is you're trying to do. "Leslie M" wrote in message ... 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! "PCLIVE" wrote: Use dollar signs to make your column references constant. Example. $C1:$C100 IF $D1:$D100=DENISE AND $C1:$C100=X Regards, Paul "Leslie M" wrote in message ... 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 D1:D100=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 D1:D100 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! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying Formulas From One Column To Another??
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 On Thu, 2 Nov 2006 11:15:02 -0800, Leslie M wrote: 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" wrote: Use dollar signs to make your column references constant. Example. $C1:$C100 IF $D1:$D100=DENISE AND $C1:$C100=X Regards, Paul "Leslie M" wrote in message ... 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 D1:D100=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 D1:D100 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! Gord Dibben MS Excel MVP |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying Formulas From One Column To Another??
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. "PCLIVE" wrote: Sorry, You lost me on this one. I'm not sure what it is you're trying to do. "Leslie M" wrote in message ... 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! "PCLIVE" wrote: Use dollar signs to make your column references constant. Example. $C1:$C100 IF $D1:$D100=DENISE AND $C1:$C100=X Regards, Paul "Leslie M" wrote in message ... 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 D1:D100=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 D1:D100 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! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying Formulas From One Column To Another??
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. "Leslie M" wrote: 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 D1:D100=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 D1:D100 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! |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying Formulas From One Column To Another??
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 "Leslie M" wrote in message ... 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. "PCLIVE" wrote: Sorry, You lost me on this one. I'm not sure what it is you're trying to do. "Leslie M" wrote in message ... 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! "PCLIVE" wrote: Use dollar signs to make your column references constant. Example. $C1:$C100 IF $D1:$D100=DENISE AND $C1:$C100=X Regards, Paul "Leslie M" wrote in message ... 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 D1:D100=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 D1:D100 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! |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying Formulas From One Column To Another??
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!!! :-) "Gord Dibben" wrote: 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 On Thu, 2 Nov 2006 11:15:02 -0800, Leslie M wrote: 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" wrote: Use dollar signs to make your column references constant. Example. $C1:$C100 IF $D1:$D100=DENISE AND $C1:$C100=X Regards, Paul "Leslie M" wrote in message ... 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 D1:D100=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 D1:D100 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! Gord Dibben MS Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Format as a MACRO | Excel Worksheet Functions | |||
Help with copying dynamic column selected based on remote cell value and dynamic formula fill | Charts and Charting in Excel | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions |