If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Copy cell formula help
I've created a spreadsheet with a dozen colums and about 150 rows.
in the 1st row cells I have a conditional formula which states Sumif "cell Range", "Salesman Name", "Copycell Range". I'm trying to copy and paste the whole column in the hopes I retain the same cell ranges and formula, but evertime I paste from column A to Column B the whole range shifts the column range. My question is this; Is there a way to keep the conditions in the formula from changing? Please help. |
#2
|
|||
|
|||
The way to do this is add "$" to the formula. The $ acts as an
"anchor" that prevents the automatic shift that occurs when you copy from column A to column B. For instance: =SUM($A10:$A20) prevents the column reference from changing, but allows the row numbers to change. =SUM(A$10:A$20) prevents the row number from changing but allows the column reference to change. =SUM($A$10:$A$20) locks both the column reference and the row reference, allowing neither to change. This takes a bit of getting used to, so be careful at first to make sure it is behaving as you need it to. Also, a hint: when you're writing or editting the formula, you can press the F4 key a number of times to apply the anchors. |
#3
|
|||
|
|||
I you don't want to range to change then you need to enter $ in front of it.
Example: instead of A1:C8 you would have $A$1:$C$8. If you only want the column or the row to stay the same then just put the $ in front of that part. This can all easily be done by clicking F4 when you are on that formula. "tamato43" wrote: I've created a spreadsheet with a dozen colums and about 150 rows. in the 1st row cells I have a conditional formula which states Sumif "cell Range", "Salesman Name", "Copycell Range". I'm trying to copy and paste the whole column in the hopes I retain the same cell ranges and formula, but evertime I paste from column A to Column B the whole range shifts the column range. My question is this; Is there a way to keep the conditions in the formula from changing? Please help. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Problem copying formula to range of cells | Ellen | Setting up and Configuration | 4 | November 20th, 2004 12:52 AM |
How to copy the 'format' of 1 cell to another using formula | copy color | General Discussion | 2 | August 27th, 2004 10:14 PM |
Reset Formula to Use Cell Address Not Cell Name | Eli | General Discussion | 2 | August 17th, 2004 12:51 AM |
formula shows in cell when trying to copy | Angie | Worksheet Functions | 4 | April 29th, 2004 10:11 PM |
Copy Formula down for Gord Dibben | PCOR | Worksheet Functions | 1 | December 10th, 2003 08:34 PM |