A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

$ symbol in a formula



 
 
Thread Tools Display Modes
  #1  
Old September 18th, 2008, 03:39 PM posted to microsoft.public.excel.worksheet.functions
RAB2685
external usenet poster
 
Posts: 5
Default $ symbol in a formula

Is there a way to highlight a bunch of formula's and automatically have the $
sign added before the column identifier and the row identifier versus going
into ever formula in every cell and manually enteringr the $ sign before each
column or row indentifier? I understand the drag and auto fill which works
perfect since my worksheets are all set up in the same format, but now I'm
having to go into every cell to add the $ to lock where it is retrieving the
info. from - very time consuming - any suggestions?
  #2  
Old September 18th, 2008, 03:58 PM posted to microsoft.public.excel.worksheet.functions
Pete_UK
external usenet poster
 
Posts: 8,780
Default $ symbol in a formula

You can use Find & Replace a few times to change, for example:

(A

to ($A$

if your formula contains a reference to cells in column A after a
bracket.

Hope this helps.

Pete

On Sep 18, 3:39*pm, RAB2685 wrote:
Is there a way to highlight a bunch of formula's and automatically have the $
sign added before the column identifier and the row identifier versus going
into ever formula in every cell and manually enteringr the $ sign before each
column or row indentifier? *I understand the drag and auto fill which works
perfect since my worksheets are all set up in the same format, but now I'm
having to go into every cell to add the $ to lock where it is retrieving the
info. from - very time consuming - any suggestions?


  #3  
Old September 18th, 2008, 04:38 PM posted to microsoft.public.excel.worksheet.functions
Spiky
external usenet poster
 
Posts: 619
Default $ symbol in a formula

On Sep 18, 9:39 am, RAB2685 wrote:
Is there a way to highlight a bunch of formula's and automatically have the $
sign added before the column identifier and the row identifier versus going
into ever formula in every cell and manually enteringr the $ sign before each
column or row indentifier? I understand the drag and auto fill which works
perfect since my worksheets are all set up in the same format, but now I'm
having to go into every cell to add the $ to lock where it is retrieving the
info. from - very time consuming - any suggestions?


If you've already copy/dragged your formulas, why do they now need the
$s?
  #4  
Old September 18th, 2008, 09:24 PM posted to microsoft.public.excel.worksheet.functions
RAB2685
external usenet poster
 
Posts: 5
Default $ symbol in a formula

Well I will try to explain:

I have a bunch of IF functions and looking at MAX dates of 2 different cells
on a different worksheet and it looked similar to this:

=(IF(MAX('HUT Trace'!AW26,BL26)1/1/2005,(MAX('HUT Trace'!AW26,BL26)+1),""))

But when I would filter the "output page" the formula would change to this
as an example (the 2nd row # would change causing a REF error:

=(IF(MAX('HUT Trace'!AW26,BL4)1/1/2005,(MAX('HUT Trace'!AW26,BL4)+1),""))

So when I do this:

=(IF(MAX('HUT Trace'!$AW$26,$BL$26)1/1/2005,(MAX('HUT
Trace'!$AW$26,$BL$26)+1),""))

Then my problem is solved. Not sure why the 2nd row number in each MAX
sequence would change and not the first one, but to overcome it if I lock the
columns and rows then the problem is fixed, but then duplicating the formula
to change rows was the problem but the find and replace is working way better
then adding the "$" to each formula.

"Spiky" wrote:

On Sep 18, 9:39 am, RAB2685 wrote:
Is there a way to highlight a bunch of formula's and automatically have the $
sign added before the column identifier and the row identifier versus going
into ever formula in every cell and manually enteringr the $ sign before each
column or row indentifier? I understand the drag and auto fill which works
perfect since my worksheets are all set up in the same format, but now I'm
having to go into every cell to add the $ to lock where it is retrieving the
info. from - very time consuming - any suggestions?


If you've already copy/dragged your formulas, why do they now need the
$s?

  #5  
Old September 18th, 2008, 10:44 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 20,252
Default $ symbol in a formula

Sub Absolute()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlAbsolute)
End If
Next
End Sub

Sub AbsoluteRow()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlAbsRowRelColumn)
Next
End Sub

Sub AbsoluteCol()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlRelRowAbsColumn)
Next
End Sub

Sub Relative()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlRelative)
Next
End Sub


Gord Dibben MS Excel MVP

On Thu, 18 Sep 2008 07:39:02 -0700, RAB2685
wrote:

Is there a way to highlight a bunch of formula's and automatically have the $
sign added before the column identifier and the row identifier versus going
into ever formula in every cell and manually enteringr the $ sign before each
column or row indentifier? I understand the drag and auto fill which works
perfect since my worksheets are all set up in the same format, but now I'm
having to go into every cell to add the $ to lock where it is retrieving the
info. from - very time consuming - any suggestions?


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:54 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.