Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)). |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)). |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)). |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)). |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)). |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)). |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)). |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)). |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)). |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)). |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change Text Color in one cell based upon entry in referenced cell | Excel Discussion (Misc queries) | |||
Control Data Entry - push entry to next cell | Excel Discussion (Misc queries) | |||
restricting entry into a cell based on entry to a previous cell | New Users to Excel | |||
Cell Entry That Locks Selected Cells From Any Data Entry. | Excel Worksheet Functions | |||
enter a time into a cell, have the cell show two times the entry | Excel Worksheet Functions |