On 2002-03-14 01:07, Robbinsl wrote:
Thank you to both Mark W. and Arviragus for you feedback. I would however appreciate some clarification of certain points.
My understanding (in plain english) of the GET.CELL(24,OFFSET(INDIRECT("A2"),ROW()-2,0)) function is:
"get the font colour format of the first character in the cell which is offset from cell A2. The degree of row offset equals the row number of the current row minus 2. The column offset is zero"
1.How can I make this a general formula which is not dependant on cell A2?
>>> Actually it is a general formula. One cell must be used as a starting point (or fix) in order to return the color(s) of the required cell(s). For instance, you could put this in the Name's RefersTo box :-
=GET.CELL(24,OFFSET(INDIRECT("A1"),ROW()-1,COLUMN()-2))
Then, entering =Red_Cell in any cell would return the color ref number of the cell immediately to the left.
It's really a question of using a formula in the RefersTo box that takes account of where you want the worksheet formula put in relation to the cell being checked.
2.Why does GET.CELL work in a defined name and not as a worksheet formula?
>>> It is part of a programming language, not a worksheet formula.
From a positive viewpoint, it is good that it can be used at all, even though indirectly.
3.On what grounds can a formula be used in a defined name? (I have never used a formula as a defined name and I cannot visualize how it works)
>>> In fact, formulas are always used in all defined names. The RefersTo box must always start with "=".
John Walkenbach says :-
"When you create a name, you're actually creating a named formula - a formula that doesn't exist in a cell. Rather, these named formulas exist in Excel's memory."
For named formulas (as opposed to named ranges - although they are really no different), try for example putting in the RefersTo box =A1+A2+A3
4.I have downloaded macrofun.hlp - it seems to contain several useful looking functions. Why are these not available in Excel 2000?
>>> They are the old Excel4Macro language. They have been replaced (mostly) by VBA. Any that are not covered by VBA can still be called in VBA procedures.
(I suppose it's the same as asking why can't all the things that can be done by VBA also be done by normal worksheet functions.)
5.If these functions work in defined names, why are they not supported in Excel help - this seems disempowering to the user?
>>> I think this is a question for Microsoft.
Bear in mind though that many of the functions in Macrofun are covered by Excel's normal capabilities and, if not, are covered by VBA.
Perhaps MS doesn't want to confuse the issue by including bits of an old macro language in Excel help - it is practical to use very few of the functions in named formulas (nor necessary to do so).
I look forward to hearing your reply
Regards
Lucy
_________________
This message was edited by Robbinsl on 2002-03-14 01:08