Where is the source of the data?

  • Thread starter Thread starter Brisbane Rob
  • Start date Start date
B

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?
 
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" <[email protected]>
wrote in message
news:[email protected]...
 
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
column of sixty lookups and I need to know which ones have bee
overwritten. Your code works great for one cell (as my original reques
so erroneously stated). IS there any way of checking all sixty an
showing the results preferably in the cell.

I tried a conditional formatting if(left(A1)<>"If(" but that didn'
work. The other way which works is by comparing the figure in the cel
with what the lookup would give, but the day will arrive when th
overwrite will be the same as the lookup.

I'm not sure this one is solveable
 
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
 
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)
 
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
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Lookup Function help 2
Re: Value Not Available Error in Vlookup 1
A function that returns the name of the current cell 17
Cell entry used in formula 1
V-Lookup Data Formats Imported from Crystal 1
Lookup 1
Vlookup function 8
transferring data from a shared workbook 2

Back
Top