SOLVED

Lock a value In a formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2944049%22%20slang%3D%22en-US%22%3ELock%20a%20value%20In%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2944049%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20do%20I%20freeze%20a%20value%20in%20a%20formula%3F%20For%20example%3A%20Cell%20A1%20contains%20the%20formula%20%3DB1%2BC1.%26nbsp%3BCell%26nbsp%3B%20A2%20contains%20the%20formula%20%3DB2%2BC1.%26nbsp%3BCell%20A3%20contains%20the%20formula%20%3DB3%2BC1.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20I%20would%20like%20to%20pull%20this%20formula%20down%20to%20cell%20A100%20which%20should%20then%20read%20%3DB100%2BC1.%20So%2C%20how%20do%20I%20lock%20in%20the%20value%20%22B1%22%20as%20I%20drag%20the%20formula%20down%3F%20I%20believe%20it%20is%20a%20symbol%20of%20some%20sort%20but%20trial%20and%20error%20has%20not%20revealed%20an%20answer.%20Thank%20you%20so%20much%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2944049%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2944087%22%20slang%3D%22en-US%22%3ERe%3A%20Lock%20a%20value%20In%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2944087%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1212968%22%20target%3D%22_blank%22%3E%40stevied234%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20actually%20the%20C1%20that%20you%20are%20%22locking%20in%2C%22%20to%20use%20your%20terminology.%20Excel%20refers%20to%20%22absolute%22%20and%20%22relative%22%20references.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20wrote%20the%20formula%20in%20cell%20A1%20as%20%3CSTRONG%3E%3DB1%2B%24C%241%20%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Ethen%20you%20could%20copy%20it%20down%20to%20cell%20A100%20where%20it%20would%20read%20%3CSTRONG%3E%3DB100%2B%24C%241%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20a%20reference%20you%20might%20find%20useful%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fglossary%2Fabsolute-reference%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fglossary%2Fabsolute-reference%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

How do I freeze a value in a formula? For example: Cell A1 contains the formula =B1+C1. Cell  A2 contains the formula =B2+C1. Cell A3 contains the formula =B3+C1. 

 

Now I would like to pull this formula down to cell A100 which should then read =B100+C1. So, how do I lock in the value "B1" as I drag the formula down? I believe it is a symbol of some sort but trial and error has not revealed an answer. Thank you so much

2 Replies
best response confirmed by mathetes (Respected Contributor)
Solution

@stevied234 

 

It's actually the C1 that you are "locking in," to use your terminology. Excel refers to "absolute" and "relative" references.

 

If you wrote the formula in cell A1 as =B1+$C$1

then you could copy it down to cell A100 where it would read =B100+$C$1

 

Here's a reference you might find useful: https://exceljet.net/glossary/absolute-reference

 

@mathetes 

 

Thank you! That is exactly what I wanted to know. I thought it was $ and had actually put that in front of C1 ($C1) but did not think to put it in front of the numeral 1 as well. Not knowing the terminology of "absolute" and using words like "locking" led me on quite a few wild goose chases. I've got that now and have applied your fix and it worked perfectly.

 

Again, thank you and you guys are great.