Evalution of a cell-entry

[Gilbert DE CEULAER]

Cell M20 contains "Whatever".
Cell S90 contains "+M20"
In order to verify things, in T90, I want to know the row, column, or
address of what is really in S90, I mean I want "M20", not the contents
("Whatever").
How can I do that ?
(Actually, in T90? I want to make a formula checking whether the
column-number mentioned in S90 ("M"=13) is less, equal or greater than the
column-number of S90 itself ("S"=19)).

 

[Bob Phillips]

=COLUMN(INDIRECT(S90))<COLUMN(S90)

 

[Pete_UK]

Do you really need the + in S90? If it just contained M20, then in T90
you could have something like this:

=IF(COLUMN(INDIRECT(S90))<COLUMN(S90),"less than","greater or equal")

Hope this helps.

Pete

 

[Joel]

You can use a very simple UDF

Function GetFormula(target As Range)

GetFormula = target.Formula
End Function


Call udf like this
=getformula(S90)

 

[Gary''s Student]

Try this small UDF:

Function formulaa(r As Range) As String
formulaa = ""
If r.HasFormula Then
formulaa = Replace(Replace(r.Formula, "=", ""), "+", "")
End If
End Function

Note that the "=" and the "+" are both discarded

 

[Bernard Liengme]

Here is a VBA function that does what you seem to want. In T90 enter
=WHATCOL(S90)
Note that I have allowed only for references up to column Z. Do you need to
be concerned with reference like = AB12 ?

If you are unfamiliar with VBA start by reading David McRitchie's site on
"getting started" with VBA

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Function whatcol(mycell)
myform = Mid(mycell.Formula, 2, 1)
mycol = Mid(mycell.Address(False, False), 1, 1)
Debug.Print myform; mycol

If myform < mycol Then
whatcol = "Less"
ElseIf myform = mycol Then
whatcol = "Equal"
Else
whatcol = "More"
End If
End Function

BTW: why begin the formula (+M20) with + rather that = ? Are you an old
Lotus user?
best wishes

 

[Gilbert DE CEULAER]

??? the result is "#REF!" ???

=COLUMN(INDIRECT(S90))<COLUMN(S90)

--
__________________________________
HTH

Bob

 

[Gilbert DE CEULAER]

Sorry, Pete, the result is "#REF!" ???
BTW, "M20", without "+" (or "=") puts "M20" in S90, instead of "Whatever" ;
"M20" is no formula.

Do you really need the + in S90? If it just contained M20, then in T90
you could have something like this:

=IF(COLUMN(INDIRECT(S90))<COLUMN(S90),"less than","greater or equal")

Hope this helps.

Pete

 

[Gilbert DE CEULAER]

Actually, what I want is a conditional formating, that turns the cell, say
yellow, if the column-number of the cell itself is less than the
column-number of the cell it refers to.
Can somebody help ?

 

[Bob Phillips]

You need to ditch the + in the cell reference.

 

[Pete_UK]

You wouldn't have had just +M20 in S90 for it to display "Whatever" -
Excel would have converted this to =+M20. I thought you just had the
text M20 in there.

Pete