
06 Jan 2014  #1 

Automatically add $ symbols to formula?
I've typed a long formula into a cell in Excel 2010 and realised that all the cell references should have $ symbols in front so I can copy and paste it to different cells without references changing. Is there a quick way to achieve this after having typed in the formula or do I need to do it manually?

My System Specs 
. 
06 Jan 2014  #2 

Try this.
In another column (like column B for example), you can enter the following formula where "$" would be combined with the contents cell "A" with a leading "dollar sign". You can then paste this formula down in column B to create this special field based on column A ="$"&A1 
My System Specs 
06 Jan 2014  #3 

That would work fine if the only reference that needed a $ to lock the reference were the first one. If it is a long formula, there really is no easy way to do it automatically, unless the cell references are the same (in which case you could do a find and replace, looking in formulas).
However, if the cell references are all different you're going to have to do it manually. The good news is that you should only have to do it once per range, because you can, as you noted, just copy the formulas across and down. Here's an example of a formula I have in one of my spreadsheets... In a case like this, I actually did forget the $ and had to manually add it where required.. Code:
=COUNTIFS('Analysis Data'!$R$5:$R$1840,MONTH($C8),'Analysis Data'!$S$5:$S$1840,$B8,Games!$F$5:$F$1840,"O",Games!$I$5:$I$1840,Q$3) +COUNTIFS('Analysis Data'!$R$5:$R$1840,MONTH($C8),'Analysis Data'!$S$5:$S$1840,$B8,Games!$F$5:$F$1840,"O",Games!$M$5:$M$1840,Q$3) +COUNTIFS('Analysis Data'!$R$5:$R$1840,MONTH($C8),'Analysis Data'!$S$5:$S$1840,$B8,Games!$F$5:$F$1840,"O",Games!$Q$5:$Q$1840,Q$3) +COUNTIFS('Analysis Data'!$R$5:$R$1840,MONTH($C8),'Analysis Data'!$S$5:$S$1840,$B8,Games!$F$5:$F$1840,"O",Games!$U$5:$U$1840,Q$3) +COUNTIFS('Analysis Data'!$R$5:$R$1840,MONTH($C8),'Analysis Data'!$S$5:$S$1840,$B8,Games!$F$5:$F$1840,"O",Games!$Y$5:$Y$1840,Q$3) +COUNTIFS('Analysis Data'!$R$5:$R$1840,MONTH($C8),'Analysis Data'!$S$5:$S$1840,$B8,Games!$F$5:$F$1840,"O",Games!$AC$5:$AC$1840,Q$3) 
My System Specs 
. 

Thread Tools  
Similar help and support threads  
Thread  Forum  
Excel Formula Hello Sir, Can you let me know the formula in Excel: I have a table as below: This table, At the column "A,Status" when i Put the word" Completed", it's will automatic put current Date to Column "B, Row 2". But i don't know after that when tomorrow arrived,at the column "B,Row 2",... 
Microsoft Office  
Formula 1 '97 Hello, i'm a new user. :) I correctly manage to install this old game (F1 '97) in my computer which is running Windows 7 Home Premium x64. I can even play with it but after a race the screen goes black and it crash. I found this message on Event Viewer... I'm from Italy so sorry if the... 
Gaming  
Excel 2003 sum formula problem I have been entering currency data in Column B in Euros to an EXCEL 2003 s/s and the formula used has added these totals entered in that Column correctly until I reached a total sum of 74364.70 Euros ,this being the sum of entries in Rows B6 to B82 . This was reached with the formula showing as... 
Microsoft Office  
Corsair Formula iv Hello, I have the Asus Crosshair Formula iv Mobo, and I think its the root of my PC issues. I get random freezes and at times cannot start, just blank screens. The reset does not work , i have to hit the reset button on the board itself. Does anyone have any similar issues with this particular... 
Hardware & Devices  
Excel Formula Help please... This is probably very simple and I have got it working but it isn't working how I want it to work. I have created an Excel document to show my monthly payments of a bank loan. I have created 3 columns in the first column is the payment number, in the second column is the amount that the loan... 
Microsoft Office  
Crosshair III Formula RAID0 help is there ANY compatible driver available for the 750SB ... of course I'm looking for drivers I can use to install 7 on my RAID0 striping array of two 150 gig VelociRaptors. I'm just trying to install the RC, not RTM for now. Please see my setup. Thanks, any help or a simple "no you can't... 
Drivers 
Our Sites 
Site Links 
About Us 
Find Us 
Windows 7 Forums is an independent web site and has not been authorized,
sponsored, or otherwise approved by Microsoft Corporation.
"Windows 7" and related materials are trademarks of Microsoft Corp.
© Designer Media Ltd All times are GMT 5. The time now is 12:08. 
