Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
$ 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
$ 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
$ 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
$ 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
$ 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What does the & symbol in a formula do? | Excel Discussion (Misc queries) | |||
What does a ^ symbol mean used in a formula | Excel Discussion (Misc queries) | |||
What does the $ symbol mean in a formula? | New Users to Excel | |||
Using a Symbol in a formula. | Excel Discussion (Misc queries) | |||
I need a symbol but "symbol" in the Insert menu is grayed-out. | Excel Discussion (Misc queries) |