GET.CELL

[Robbinsl]

I want to filter by font colour and MSKB Q213923 suggests defining a name with the formula GET.CELL(24,OFFSET(INDIRECT("A2"),ROW()-2,0)).

I want to understand how the formula is constructed, what it all means, so I can use it for different things. What's it all about??

[Mark W.]

On 2002-03-13 08:25, Robbinsl wrote:
I want to filter by font colour and MSKB Q213923 suggests defining a name with the formula GET.CELL(24,OFFSET(INDIRECT("A2"),ROW()-2,0)).

I want to understand how the formula is constructed, what it all means, so I can use it for different things. What's it all about??

GET.CELL is a macro function from the "old" Excel 4.0 macro language. It's 1st argument is Type_num. 24 corresponds to "Font color of the first character in the cell, as a number in the range 1 to 56. If font color is automatic, returns 0". It's 2nd (optional) argument is a reference which in this case is produced by the OFFSET function. OFFSET(INDIRECT("A2"),ROW()-2,0) produces a reference to the cell in column A that's on the same row as the active cell.

[ This Message was edited by: Mark W. on 2002-03-13 15:02 ]

[Arviragus]

The GET.CELL function is part of the Excel4 Macro language (which was VBA's predecessor).
It cannot be used directly in a worksheet cell.
The syntax is :- GET.CELL(type_num, reference)
Type_num 24 returns the font color of the first character in the reference, as a number in the range 1 to 56. If the font color is set to automatic, it returns 0.

I will assume you are familiar with the OFFSET, INDIRECT & ROW functions, so :-
INDIRECT("A2") fixes the reference as cell A2.
ROW()-2 provides the number of rows to be offset from A2. So in row 2 the offset is 0, in row 3 it is 1, etc.

Post again if not clear.

[Robbinsl]

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?

2.Why does GET.CELL work in a defined name and not as a worksheet formula?

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)

4.I have downloaded macrofun.hlp - it seems to contain several useful looking functions. Why are these not available in Excel 2000?

5.If these functions work in defined names, why are they not supported in Excel help - this seems disempowering to the user?

I look forward to hearing your reply

Regards

Lucy



_________________


[ This Message was edited by: Robbinsl on 2002-03-14 01:08 ]

[Arviragus]

See comments below >>>

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 ]