Evalution of a cell-entry
 

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)).

Evalution of a cell-entry
 

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

--
__________________________________
HTH

Bob

"Gilbert DE CEULAER" wrote in message
...
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)).

Evalution of a cell-entry
 

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

On Dec 4, 1:55*pm, "Gilbert DE CEULAER"
wrote:
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)).

Evalution of a cell-entry
 

You can use a very simple UDF

Function GetFormula(target As Range)

GetFormula = target.Formula
End Function


Call udf like this
=getformula(S90)



"Gilbert DE CEULAER" wrote:

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)).

Evalution of a cell-entry
 

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
--
Gary''s Student - gsnu200817


"Gilbert DE CEULAER" wrote:

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)).

Evalution of a cell-entry
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Gilbert DE CEULAER" wrote in message
...
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)).

Evalution of a cell-entry
 

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

"Bob Phillips" wrote in message
...
=COLUMN(INDIRECT(S90))<COLUMN(S90)

--
__________________________________
HTH

Bob

"Gilbert DE CEULAER" wrote in message
...
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)).

Evalution of a cell-entry
 

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

"Pete_UK" wrote in message
...
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

On Dec 4, 1:55 pm, "Gilbert DE CEULAER"
wrote:
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)).

Evalution of a cell-entry
 

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 ?

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)).

Evalution of a cell-entry
 

You need to ditch the + in the cell reference.

--
__________________________________
HTH

Bob

"Gilbert DE CEULAER" wrote in message
...
??? the result is "#REF!" ???

"Bob Phillips" wrote in message
...
=COLUMN(INDIRECT(S90))<COLUMN(S90)

--
__________________________________
HTH

Bob

"Gilbert DE CEULAER" wrote in message
...
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)).

Evalution of a cell-entry
 

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

On Dec 4, 8:18*pm, "Gilbert DE CEULAER"
wrote:
Sorry, Pete, the result is "#REF!" ???
BTW, "M20", without "+" (or "=") puts "M20" in S90, instead of "Whatever" ;
"M20" is no formula.

"Pete_UK" wrote in message

...
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

On Dec 4, 1:55 pm, "Gilbert DE CEULAER"



wrote:
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)).- Hide quoted text -

- Show quoted text -