Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-26-2012, 07:15 AM
jprt68 jprt68 is offline Keep cell in formula constant Windows Vista Keep cell in formula constant Office 2010 32bit
Novice
Keep cell in formula constant
 
Join Date: Jun 2012
Posts: 1
jprt68 is on a distinguished road
Default Keep cell in formula constant

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



a b add
---------------
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?
Reply With Quote
  #2  
Old 06-26-2012, 08:31 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Keep cell in formula constant Windows 7 32bit Keep cell in formula constant Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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.
Reply With Quote
Reply

Tags
cell, formula, formulas

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Keep cell in formula constant Use formula to copy cell formats also mike_abc Excel 10 12-10-2011 01:55 AM
Keep cell in formula constant need to check whether cell contains formula etodem Excel 2 10-23-2010 07:33 PM
Keep cell in formula constant Formula in a Cell with condition? Learner7 Excel 1 07-19-2010 10:10 AM
Keep cell in formula constant How can I write the following (whole) formula in a single cell? Learner7 Excel 1 07-19-2010 10:06 AM
Keep cell in formula constant Protect formula in cell? markg2 Excel 4 05-24-2010 11:50 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:17 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft