software

If cell A1 contains a lookup, is it possible, either by conditional
formatting or from another cell, to see if the number in A1 comes from
the lookup, or if the formula has been overwritten with a number?--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile: www.excelforum.com/member.php...oamp;userid=25096
View this thread: www.excelforum.com/showthread...hreadid=517687It can only come from the lookup. If the formula gets overwritten by a
number, the lookup is gone. The cell cannot hold a formula and a value, one
or the other.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Brisbane Robquot; gt;
wrote in message
...
gt;
gt; If cell A1 contains a lookup, is it possible, either by conditional
gt; formatting or from another cell, to see if the number in A1 comes from
gt; the lookup, or if the formula has been overwritten with a number?
gt;
gt;
gt; --
gt; Brisbane Rob
gt; ------------------------------------------------------------------------
gt; Brisbane Rob's Profile:
www.excelforum.com/member.php...oamp;userid=25096
gt; View this thread: www.excelforum.com/showthread...hreadid=517687
gt;
Hi Brisbane Bob,
Seems like a trivial request given that you only have to look in the
cell to see if it is a value or a formula, however you might have a
valid reason that I have overlooked. The following UDF seems to work.
It just tests for the leading quot;=quot; that every formula must have. It
returns FALSE if no leading quot;=quot; and TRUE if the cell being tested (A1
in your case) does have a leading quot;=quot;....

Public Function IsFormula(rngCell As Range) As Boolean
If Left(rngCell.Formula, 1) lt;gt; quot;=quot; Then
Let IsFormula = False
Else: Let IsFormula = True
End If
End Function

Just copy and paste into a standard module in the VBA Editor or into
your PERSONAL.XLS macro book, then access as you would any other
worksheet function.

Ken Johnson
Ken

Thanks for the reply. I should have explained myself better. I have a
column of sixty lookups and I need to know which ones have been
overwritten. Your code works great for one cell (as my original request
so erroneously stated). IS there any way of checking all sixty and
showing the results preferably in the cell.

I tried a conditional formatting if(left(A1)lt;gt;quot;If(quot; but that didn't
work. The other way which works is by comparing the figure in the cell
with what the lookup would give, but the day will arrive when the
overwrite will be the same as the lookup.

I'm not sure this one is solveable.--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile: www.excelforum.com/member.php...oamp;userid=25096
View this thread: www.excelforum.com/showthread...hreadid=517687Try this:
Select Insert Name Define to display the Define Name dialog box
In the Define Name dialog box, aenter the following tin the Names in
Workbook field:
CellHasFormula
Enter teh follwoing formula in the Refers To field:
=GET.CELL(48,INDIRECT(quot;rcquot;,falsequot;))
Click Add, and then click OK to close the Define Name dialog box
Select all the cells to which you want to apply the conditional formatting
Select Format Conditional Formatting to display thge Conditional Formatting
dialog box
In the box select Formula Is and then enter this formula:
=CellHasFormula
Click the format button to display the Format Cells dialog box. Select the
type formatting you want for the cells that contain a formul
Click OK to close the dialog box

Sounds comolicated but is relatively easy - Good Luckquot;Brisbane Robquot; wrote:

gt;
gt; If cell A1 contains a lookup, is it possible, either by conditional
gt; formatting or from another cell, to see if the number in A1 comes from
gt; the lookup, or if the formula has been overwritten with a number?
gt;
gt;
gt; --
gt; Brisbane Rob
gt; ------------------------------------------------------------------------
gt; Brisbane Rob's Profile: www.excelforum.com/member.php...oamp;userid=25096
gt; View this thread: www.excelforum.com/showthread...hreadid=517687
gt;
gt;

Select all the cells and use CF with a formula of =IsFormula(A1)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Brisbane Robquot; gt;
wrote in message
news:Brisbane.Rob.23zp1z_1141214402.1893@excelforu m-nospam.com...
gt;
gt; Ken
gt;
gt; Thanks for the reply. I should have explained myself better. I have a
gt; column of sixty lookups and I need to know which ones have been
gt; overwritten. Your code works great for one cell (as my original request
gt; so erroneously stated). IS there any way of checking all sixty and
gt; showing the results preferably in the cell.
gt;
gt; I tried a conditional formatting if(left(A1)lt;gt;quot;If(quot; but that didn't
gt; work. The other way which works is by comparing the figure in the cell
gt; with what the lookup would give, but the day will arrive when the
gt; overwrite will be the same as the lookup.
gt;
gt; I'm not sure this one is solveable.
gt;
gt;
gt; --
gt; Brisbane Rob
gt; ------------------------------------------------------------------------
gt; Brisbane Rob's Profile:
www.excelforum.com/member.php...oamp;userid=25096
gt; View this thread: www.excelforum.com/showthread...hreadid=517687
gt;
Ken,

In VBA, a range has a HasFormula property which can be checked

Function IsFormula(rng As Range)
If rng.Count gt; 1 Then
IsFormula = CVErr(xlErrRef)
Else
IsFormula = rng.HasFormula
End If
End Function

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Ken Johnsonquot; gt; wrote in message ups.com...
gt; Hi Brisbane Bob,
gt; Seems like a trivial request given that you only have to look in the
gt; cell to see if it is a value or a formula, however you might have a
gt; valid reason that I have overlooked. The following UDF seems to work.
gt; It just tests for the leading quot;=quot; that every formula must have. It
gt; returns FALSE if no leading quot;=quot; and TRUE if the cell being tested (A1
gt; in your case) does have a leading quot;=quot;....
gt;
gt; Public Function IsFormula(rngCell As Range) As Boolean
gt; If Left(rngCell.Formula, 1) lt;gt; quot;=quot; Then
gt; Let IsFormula = False
gt; Else: Let IsFormula = True
gt; End If
gt; End Function
gt;
gt; Just copy and paste into a standard module in the VBA Editor or into
gt; your PERSONAL.XLS macro book, then access as you would any other
gt; worksheet function.
gt;
gt; Ken Johnson
gt;

Thanks, fellas. Your assistance is much appreciated.--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile: www.excelforum.com/member.php...oamp;userid=25096
View this thread: www.excelforum.com/showthread...hreadid=517687Hi Bob,
I thought there was such a formula, I didn't see in the list of
worksheet formulas so I thought I was imagining things.
Thanks Bob
Ken JohnsonHi Brisbane Rob,
I didn't mean to call you Brisbane Bob, I think I need new glasses:-)
Ken Johnson