Windows 7 Forums
Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.


Windows 7: Automatically add $ symbols to formula?

06 Jan 2014   #1

Win 7 Home Premium (OEM) - Install date: 02-2010
 
 
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 SpecsSystem Spec
.

06 Jan 2014   #2

Windows 7 ultimate x64
 
 

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 SpecsSystem Spec
06 Jan 2014   #3

Linux Mint 17 Cinnamon | Win 7 Ult x64
 
 

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)
EDIT: Actually, the ="$"&A1 wouldn't work.. You'd end up with $=<cell_value>.. It'd have to be ="=$"&A1
My System SpecsSystem Spec
.


Reply

 Automatically add $ symbols to formula?




Thread Tools



Similar help and support threads for2: Automatically add $ symbols to formula?
Thread Forum
Solved Excel custom formula Help Microsoft Office
Formula 1 '97 Gaming
Corsair Formula iv Hardware & Devices
Excel Formula Help please... Microsoft Office
Crosshair III Formula RAID0 help 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 03:11 PM.
Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App
  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33