ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   How to stop formulas from incrementing when you copy and paste? (https://www.excelbanter.com/excel-worksheet-functions/10466-how-stop-formulas-incrementing-when-you-copy-paste.html)

Kirkwill

How to stop formulas from incrementing when you copy and paste?
 
I have a document that I want the column of cells that I selected to always
equal what I put in cell B2. It works in the first cell of the row, but then
it increments the formula below to read cell B3 and the next one B13 and so
on. What I am trying to do is have anywhere from 10 to 100 rows that
calculate number of hours worked by the dollar amount of a contract and that
amount changes for each client, so in my formula I tried having it look to
the cell B2 where I have that dollar amount, but the formula when copied
increments to b3, then b4 and so on.

Any info would be great thanks
Kirk

ExcelBanter AI

Answer: How to stop formulas from incrementing when you copy and paste?
 
Hi Kirk,

To stop formulas from incrementing when you copy and paste, you can use absolute cell references. An absolute cell reference is a reference to a specific cell that does not change when you copy and paste the formula to other cells.

Here's how you can use absolute cell references in your formula:
  1. Select the cell with the formula you want to modify.
  2. Click on the cell reference in the formula that you want to make absolute. In your case, it's cell B2.
  3. Press the F4 key on your keyboard. This will add dollar signs ($) before the column letter and row number of the cell reference, making it absolute. The cell reference should now look like $B$2.
  4. Press Enter to save the modified formula.

Now, when you copy and paste the formula to other cells, the cell reference to B2 will remain absolute and not change.

Peo Sjoblom

In the formula use

$B$2

and it won't increment

Regards

Peo Sjoblom

"Kirkwill" wrote:

I have a document that I want the column of cells that I selected to always
equal what I put in cell B2. It works in the first cell of the row, but then
it increments the formula below to read cell B3 and the next one B13 and so
on. What I am trying to do is have anywhere from 10 to 100 rows that
calculate number of hours worked by the dollar amount of a contract and that
amount changes for each client, so in my formula I tried having it look to
the cell B2 where I have that dollar amount, but the formula when copied
increments to b3, then b4 and so on.

Any info would be great thanks
Kirk



All times are GMT +1. The time now is 01:06 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com