Problem with named formulas

[jagmohan]

Hi,


I am using named formulas in my excel. Ofcourse, I am new to it.


Here is the problem I am facing:

1. I have a formula that does not use any cell reference

e.g. DateOfFirstDay = DATE(YEAR(TODAY()), MONTH(TODAY()),1)

This works perfect as there is no cell reference.


2. I have another formula which is almost same as above except - instead of "TODAY()" it uses serial_number from a date defined in another cell and hence refers to it.

e.g. DataOfFirstDay_new =DATE(YEAR(<CellRef&#62, MONTH(<CellRef&#62,1)


This new formula works well as long as <CellRef> is same. But the <CellRef> changes automatically. I mean - Since the results were not correct, I opened the Named Formula dialog and set the ref correctly. Again the results were not correct and so I opened the Named formula dialog and again the ref was not where I had set it.


Is there any reason why it should happen?


regards,


Jagmohan


P.S. Another small question - what is the best way to edit previously entered formula in Named Formula dialog or Conditional Formatting dialog? Using arrow keys is not possible here as once you press any arrow key it enters the reference to the cell.


regards,


Jagmohan

 

[Luke M]

When you are defining the named range, make sure you are using absolute references (with $ symbol) rather than a relative reference, or the formula will change depending on what the active cell is.


To edit the formula box w/ arrow keys, hit the F2 key to toggle the reference frame (worksheet/formula box). This used to drive me nuts as well. =)


Finally, you could simplify your formula to a single reference like so:

=EOMONTH(TODAY(),-1)+1

or

=EOMONTH(Named_Range,-1)+1


the EOMONTH function gives the last day of month x months relative to date. So, we find the last date of the previous month, and then add 1 to get the first day of referenced month. Hope this answers everything. =)

 

[jagmohan]

Hi,


Thanks Luke!


Missed to mention in my post. Dollarizing the cell reference used in the formula solves the problem. But then it certainly loses the flexibility.


The solution you suggested is also really nice one.


regards,


Jagmohan

 

[Luke M]

If you want to keep the relative reference, the trick is remember that it's a "constant relative". By that I mean, if you initially set it up to reference a cell 1 row down, the named range will ALWAYS refer to 1 cell down, no matter what cell you put it in. So, layout of formulas is key.

 

[keymaster]

Regarding your

P.S. Another small question - what is the best way to edit previously entered formula in Named Formula dialog or Conditional Formatting dialog? Using arrow keys is not possible here as once you press any arrow key it enters the reference to the cell.

Once you are in the edit mode, press F2. This will let you use arrow keys to move around the formula.