Microsoft Office Forums Keep cell in formula constant
 User Name Remember Me? Password
 Register FAQ Search Today's Posts Mark Forums Read

 Thread Tools Display Modes
#1
06-26-2012, 07:15 AM
 jprt68 Windows Vista Office 2010 32bit Novice Join Date: Jun 2012 Posts: 1
Keep cell in formula constant

Say I have 2 columns a and b, as well as an Add column for example:

---------------
4 2
3 4
1 2

For the Add formula I would type =A2+B2 into the first cell, then just drag this down so that all other rows have appropriate formulas =A3+B3, =A4+B4 ... etc.

What if I had a forth column, e.g. "Multiply by Price" where Price is stored in another cell, e.g. F3.

a b add Multiply by Price
------------------------------------
4 2 6
3 4 7
1 2 3

Price
--------
7.5

So in the first "Multiply by Price" cell I would type =F3*A3

I want to be able to drag this down so that the formulas change to =F3*A4, =F3*A5 .... etc where the F3 remains constant.

What do I do to the formula to do this?
#2
06-26-2012, 08:31 AM
 Colin Legg Windows 7 32bit Office 2010 32bit Expert Join Date: Jan 2011 Location: UK Posts: 369

Hi,

You can lock the column and row references by putting a \$ sign in front of them.
This is called absolute referencing.

(a) This will lock the F and the 3 so they will not adjust if you fill either across or down.
=\$F\$3*A3

(b) This will lock the F so it will not adjust if you fill across. The 3 will still adjust when you fill down.
=\$F3*A3

(c) This will lock the 3 so it will not adjust if you fill down. The F will still adjust when you fill across.
=F\$3*A3

In this case you can use either (a) or (c) since it is the 3 you want to lock as you fill down.

 Tags cell, formula, formulas

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post mike_abc Excel 10 12-10-2011 01:55 AM etodem Excel 2 10-23-2010 07:33 PM Learner7 Excel 1 07-19-2010 10:10 AM Learner7 Excel 1 07-19-2010 10:06 AM markg2 Excel 4 05-24-2010 11:50 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:07 AM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top