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: http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=517687

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

"Brisbane Rob"
wrote in message
...

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:
http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=517687

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 "=" that every formula must have. It
returns FALSE if no leading "=" and TRUE if the cell being tested (A1
in your case) does have a leading "="....

Public Function IsFormula(rngCell As Range) As Boolean
If Left(rngCell.Formula, 1) < "=" 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)<"If(" 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: http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=517687

Try 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("rc",false"))
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 Luck


"Brisbane Rob" wrote:


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: http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=517687

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Brisbane Rob"
wrote in message
news:Brisbane.Rob.23zp1z_1141214402.1893@excelforu m-nospam.com...

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)<"If(" 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:
http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=517687

Ken,

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

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ken Johnson" wrote in message
ups.com...
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 "=" that every formula must have. It
returns FALSE if no leading "=" and TRUE if the cell being tested (A1
in your case) does have a leading "="....

Public Function IsFormula(rngCell As Range) As Boolean
If Left(rngCell.Formula, 1) < "=" 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

Thanks, fellas. Your assistance is much appreciated.


--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile: http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=517687

Hi 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 Johnson

Hi Brisbane Rob,
I didn't mean to call you Brisbane Bob, I think I need new glasses:-)
Ken Johnson

DOn't worry too much about the name, Ken. I can't get the =ifformula
(which sounds the simplest) to work.

I've tried referring it to the cell it's in without any joy, and I've
tried referring it to A1 and that didn't work ether. What am I doing
wrong?

Thanks


--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile: http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=517687

Hi Rob,
do you mean the = IsFormula as supplied by Bob Phillips? Or the IF
Worksheet Function.
I'm assuming you meant the IsFormula function which you should be using
with conditional formatting.

I pasted Bob's Function into a blank standard module then applied
conditional formatting to some cells using "=IsFormula(whatever the
address is of the cell with the Cond Format)" without the speech marks
in the "Formula Is" box of the Cond Formatting and it worked OK, cells
with a formula were formatted accordingly

Sorry about the delay, I must have just been leaving for work when you
replied.

Ken Johnson

Thanks, Ken, I'll have another go at it tomorrow.

Much appreciated.


--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile: http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=517687

Hi Rob,
You're welcome, good luck.
Ken Johnson