Where is the source of the data?

[Brisbane Rob]

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?

[Bob Phillips]

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" <Brisbane.Rob.23zeen_1141200601.439@excelforum-nospam.com>
wrote in message
news:Brisbane.Rob.23zeen_1141200601.439@excelforum-nospam.com...
>
> 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
>

[Ken Johnson]

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

[Brisbane Rob]

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.

[Bob Phillips]

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" <Brisbane.Rob.23zp1z_1141214402.1893@excelforum-nospam.com>
wrote in message
news:Brisbane.Rob.23zp1z_1141214402.1893@excelforum-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
>

[Bob Phillips]

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" <KenCJohnson@gmail.com> wrote in message
news:1141202465.923782.68160@i40g2000cwc.googlegroups.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
>

[Ken Johnson]

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

[Florida User]

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

[Brisbane Rob]

Thanks, fellas. Your assistance is much appreciated.

[Ken Johnson]

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

[Brisbane Rob]

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

[Ken Johnson]

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

[Brisbane Rob]

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

Much appreciated.