stopping column increment in formula copy

B

btdown

Sorry for the img post, but its easier to articulate the problem with
picture...

I have a column of data (distances) B5 thru b14 that I'm trying t
multiply by two different company prices, which are in C1 and C2. Whe
I put the first equation in B5 (=c1*B5) and then try to copy/paste th
equation down the column to B14, the C1 cell value in the equatio
increments as it goes down the column. Same thing happens when I do i
for company B.

So I guess I'm doing something wrong. How do I easily paste th
functions down the column while still having the equation use the sam
multiplier (C1)? I'd also like to be able to change the company
and/or B price and have the values change automatically to reflect, bu
I tried various methods and functions and could not make it work.

Any help would be appreciated!



[image: http://216.120.230.82/~krathis/xlspic.gif
 
D

duane

put a $ sign in front of the column number or row number - see help fo
"absolute" reference
 
B

btdown

Yeah. Tried it and it doesnt work.

When you paste the formula from B6 down to B14 it always shows the sam
value that B5, even though when you click on it, it shows that the C
ref value is locked.

Any ideas
 
B

btdown

Thats it! Calc was on manual. Im not sure how it got that way...Actuall
I never knew you could turn it off. ;)


Its working now. Thank you for your help. Its much appreciated.

B
 
R

RagDyer

Enter this formula in B5:
=$C$1*A5

Enter this formula in C5:
=$C$2*A5

Now go to:
<Tools> <Option> <Calculation> tab,
And make sure that "Automatic: *is* checked.

Now, select *both* B5 and C5,
and drag down to copy as needed, using the "fill handle" in the lower right
corner of the selection.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Yeah. Tried it and it doesnt work.

When you paste the formula from B6 down to B14 it always shows the same
value that B5, even though when you click on it, it shows that the C1
ref value is locked.

Any ideas?
 
G

Gord Dibben

BT

If you're wondering how it got switched to "manual"......

Excel takes the Calculation mode each session from the settings on the first
workbook opened in that session.

i.e. If you saved Book1 with calc mode in manual and opened it first, calc
mode would be in Manual.

If you saved Book2 with calc mode in auto and opened it after Book1, Book2
would be in manual mode(Excel ignores the auto calc mode in this case).

If you close Book1 before opening Book2, Book2 will be in auto calc mode.

Confusing enough? <g>

Gord Dibben Excel MVP
 
Top