what can i do about this? (excel question)

Gordon Gekko · Aug 5, 2003

ok, say in A1 i have this formula: =B1+C1

now, say i copy this formula from A2 to A1000

in A2, i will get =B2+C2

HERE IS WHAT BUGS ME!

what if in the formula i want B1 to change, but i want C1 to stay C1?

for example, say i want A2 to be =B2+C1

what i have been doing is copying the formula to the cells i want it, then i go back by hand and change the values that i didn't want changed. BUT FOR A LOT OF CELLS, THIS SUCKS! what can i do, if anything?!

thx!

Sanjuro · Aug 5, 2003

GG,

=B1+C1
=B2+C1
=B3+C1

Manually fill this in for the first 3 As.
Then highlight the 3 As.
Then drag the bottom right corner all the way down to 1000.

Excel should pick up that only the Bs shoud be changing and Cs
remain the same.

Good Luck!

kgharris · Aug 5, 2003

Quote from Gordon Gekko:

ok, say in A1 i have this formula: =B1+C1

now, say i copy this formula from A2 to A1000

in A2, i will get =B2+C2

HERE IS WHAT BUGS ME!

what if in the formula i want B1 to change, but i want C1 to stay C1?

for example, say i want A2 to be =B2+C1

what i have been doing is copying the formula to the cells i want it, then i go back by hand and change the values that i didn't want changed. BUT FOR A LOT OF CELLS, THIS SUCKS! what can i do, if anything?!

thx!

If you want the C1 to remain constant, just put $ sign in front of it. In your example above:

B2+$C$1

C1 will remain constant when you cut and paste.

AmbushHillbilly · Aug 5, 2003

Quote from kgharris:

If you want the C1 to remain constant, just put $ sign in front of it. In your example above:

B2+$C$1

C1 will remain constant when you cut and paste.

Also, a '$' in front of the column ($C1) will lock the column only as you copy. A '$' before the row does the same for it (C$1).

swoop[TR] · Aug 5, 2003

Relative vs Absolute cell referencing. Basic Excel stuff dude.

Just teasing

Gordon Gekko · Aug 5, 2003

oh yes!!

you guys just saved me a lot of time. my plan was to do like 100 cells per day til i got it done. lol but i knew there had to be a better way.

thanks!

lescor · Aug 5, 2003

You can also assign a name to cell C1, then just reference the name in your formulas.

electron · Aug 5, 2003

Quote from Gordon Gekko:

oh yes!!

you guys just saved me a lot of time. my plan was to do like 100 cells per day til i got it done.

That would be a good exercise in patience so needed when trading...