Calling Excel Gurus

[Luke]

Hi gang,

I'd like to determine whether a cell has a number in it, or a function returning a number in it.

Confused...? Consider the following:

           A               B              C   
1         3               4       =Sum(A1+B1)

Of course, C1 just shows a 7. So, to ask another way, is there any logical test I can do to distinguish A1 from c1?

I've got nowhere with help, IF or even ISNUMBER. Can you?

Thanks in advance!


Edited to add CODE format - silly me

 

[gameover]

You'll need to create a user defined function.

- Open the VBA environment (alt f11).
- Select 'Insert' > 'Module'
- Copy and paste this:

* Begin *

Function IsFunction(CellRange As Range)

If Not (IsNull(CellRange)) Then

If (Left(CellRange.Formula, 1) = "=") Then
IsFunction = True
Else
IsFunction = False
End If

Else
IsFunction = False
End If

End Function


* End *

Without the * Begin * and * End *


Save, and now you can use this function in a cell, e.g:

IF (IsFunction(A1), 1, 0) etc. (I'll assume you know how IF works)

 

[Luke]

Thanks Gameover...
Your answer makes sense... but unfortunately my question didn't.

What I really want is to check from another cell, and only include the results if there is not a formula in the cell.

Specifically I want the value if it's a number and 0 if it's a formula.
When I try Isnumber(C1) I get a true result... so that's not going to help unfortunatley.

Or have I missed something?

Thanks



Edit... I just saw your new response.
I'll have to test it tomorrow now, but that looks very plausible.


HUGE THANKS!

 

[gameover]

Sorry, just after I posted, I realised what you were _actually_ after. I've edited the above post, it should help you - I tested it quickly, seemed to work

 

[Pete]

Luke,

Another way to skin the same cat, but without using a user defined function or VBA.

Go to the cell where you want the result. For example D1.

Prepare to insert a defined name (via menu insert, name, define. Or just hit CTRL-F3.)

Under names in workbook enter something like Is_Formula

and in "refers to" enter
=GET.CELL(48,Sheet1!C1)

substitute whatever your real sheet name is,
or select via mouse and the real sheet name will appear.
If via mouse, recommend you delete $ signs so that the reference is relative and not absolute. (This can be done by use of the F4 key; press as many times as required.) Then hit add or return, to create.

Now in the cell where you want the result, enter
=IF(Is_Formula,0,C1)

(To save typing Is_Formula you can use F3 key as a shortcut to the paste name box.)

If you have multiple cells where you want to use the formula, make sure that where you enter the formula is in the same relative place as when you created the defined name. Such as, if you originally used D1, being the cell one to the right of cell C1, and you also want the formula to check the contents of cells C2, C3, etc. Then enter the =IF(Is_Formula,0,C2), etc in D2 etc.

Hope that is clear. It is simpler than all the description might suggest.

regards

 

[Luke]

Thanks guys!

Thanks to both of you, Gameover and Pete.

Gameover: thanks - your solution was fantastic, and I understood it too
23 minutes from problem to solution. I love this board!

Pete: You've proved once again that Windows always has multiple ways of doing the same thing. You're right - it's simple to do, but unfortunatley I have NO IDEA how/why your method works. I can't find any reference to the GET.CELL function in the help file, and the info on CELL doesn't seem right either.
but happy.

btw, I found something else:
If you go Edit, Go To, Special and just select Constant then you highlight all the values in the worksheet that are just values.
You can then choose numbers/text etc or any combination of the above.

Thanks again to both of you!
Luke