Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.worksheetfunctions
J.Smith
 
Posts: n/a
Default Function: cell contents formula or value?

Hi,
It seems like a trivial thing, but I seem to be unable to locate a worksheet
function that allows me to determine, if a given cell contents is a formula
or a value.

Thank you!


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Miguel Zapico
 
Posts: n/a
Default Function: cell contents formula or value?

You can use a custom function like:
Function IsFormula(rng1 As Range) As Boolean
IsFormula = rng1.HasFormula
End Function

Also, you can see it directly on the screen using CTRL+~

Hope this helps,
Miguel.

"J.Smith" wrote:

Hi,
It seems like a trivial thing, but I seem to be unable to locate a worksheet
function that allows me to determine, if a given cell contents is a formula
or a value.

Thank you!



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.worksheetfunctions
Max
 
Posts: n/a
Default Function: cell contents formula or value?

J.Smith wrote:
It seems like a trivial thing, but I seem to be unable to locate a worksheet
function that allows me to determine, if a given cell contents is a formula
or a value.


Try the F5* Special route (options are available there)
*or click Edit Go to.. , or Press Ctrl+G

Press F5 Special Check "Formulas" OK
will select all formula cells on the sheet at one go
Then we could apply Format Cells Font tab (to taste)

Similarly, we could also select the constants:
Press F5 Special Check "Constants" OK
then apply format to taste, etc ...

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
broro183
 
Posts: n/a
Default Function: cell contents formula or value?


Hi,

Another option that you may be interested in is colouring cell contents
based on what sort of info they contain eg formula, constant, inputs
etc. If so, check out Aaron's colour code macro:
http://www.xl-logic.com/xl_files/vba/color_code.zip
which is listed on his page:
http://www.xl-logic.com/pages/vba.html

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=543604

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
J.Smith
 
Posts: n/a
Default Function: cell contents formula or value?


Another option that you may be interested in is colouring cell contents
based on what sort of info they contain eg formula, constant, inputs
etc. If so, check out Aaron's colour code macro:
http://www.xl-logic.com/xl_files/vba/color_code.zip
which is listed on his page:
http://www.xl-logic.com/pages/vba.html

hth
Rob Brockett
NZ


Thanks, Rob! This is exactly the purpose I need it for. I want to use a
formula which detects if a formula is used in a current cell, in order to
apply conditional formatting, so those cells with formula would show up
differently (and the user would have a choice to either overwrite them with
values, or leave them with calculated defaults).

Thank you for your reply!




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
J.Smith
 
Posts: n/a
Default Function: cell contents formula or value?


You can use a custom function like:
Function IsFormula(rng1 As Range) As Boolean
IsFormula = rng1.HasFormula
End Function

Also, you can see it directly on the screen using CTRL+~

Hope this helps,
Miguel.


Thank you, Miguel! I was hoping, there would be a spreadsheet function, but
looks like there is none. If that's the case, then, of course, a VB function
is the last resort.

Thanks, again, for your reply.


  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.worksheetfunctions
J.Smith
 
Posts: n/a
Default Function: cell contents formula or value?

Thanks, Max, but I was looking for a worksheet function which does that.
Thanks for replying, anyway!

Try the F5* Special route (options are available there)
*or click Edit Go to.. , or Press Ctrl+G

Press F5 Special Check "Formulas" OK
will select all formula cells on the sheet at one go
Then we could apply Format Cells Font tab (to taste)

Similarly, we could also select the constants:
Press F5 Special Check "Constants" OK
then apply format to taste, etc ...

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---



  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.worksheetfunctions
Max
 
Posts: n/a
Default Function: cell contents formula or value?

J.Smith wrote:
Thanks, Max, but I was looking for a worksheet function which does that.
Thanks for replying, anyway!


No prob, was just venturing a guess as to your underlying intent.
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
broro183
 
Posts: n/a
Default Function: cell contents formula or value?


Hi
Thanks for the feedback, pleased I could help.
I hope it works as you want/expect it to - from memory, it permanently
overwrites the other formatting that you have on your sheets.

Rob Brockett
NZ
Always learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=543604

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.worksheetfunctions
Harlan Grove
 
Posts: n/a
Default Function: cell contents formula or value?

J.Smith wrote...
It seems like a trivial thing, but I seem to be unable to locate a worksheet
function that allows me to determine, if a given cell contents is a formula
or a value.


No built-in function to do this. You could use VBA to write a
user-defined function. UDFs need to go into general VBA modules, not
class modules.

Function isformula(r As Range) As Boolean
isformula = r.Cells(1).HasFormula
End Function



  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
J.Smith
 
Posts: n/a
Default Function: cell contents formula or value?


Another option that you may be interested in is colouring cell contents
based on what sort of info they contain eg formula, constant, inputs
etc. If so, check out Aaron's colour code macro:
http://www.xl-logic.com/xl_files/vba/color_code.zip
which is listed on his page:
http://www.xl-logic.com/pages/vba.html


This is exactly the kind of thing I was hoping to accomplish (cell
coloring).
Thanks a lot!

P.S. I posted a reply a long time ago, but it never showed on this NG.
Strange!
This is a second try.


  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
J.Smith
 
Posts: n/a
Default Function: cell contents formula or value?

Thank you, Miguel!
I was hoping for a worksheet function, but looks like there really isn't
any.
In this case, a VB function should do. Thanks, again!

(I posted my thanks awhile ago, but for some reason I can't see it in this
NG. Weird!)

You can use a custom function like:
Function IsFormula(rng1 As Range) As Boolean
IsFormula = rng1.HasFormula
End Function

Also, you can see it directly on the screen using CTRL+~

Hope this helps,
Miguel.

"J.Smith" wrote:

Hi,
It seems like a trivial thing, but I seem to be unable to locate a

worksheet
function that allows me to determine, if a given cell contents is a

formula
or a value.

Thank you!





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
IF Function to test formula in a cell Fred Holmes Excel Worksheet Functions 5 November 18th 05 01:04 AM
Possible Lookup Table Karen Excel Worksheet Functions 5 June 8th 05 09:43 PM
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 07:48 PM
How do I find the contents of a cell using the "ADDRESS" function. sweeney Excel Worksheet Functions 2 April 5th 05 03:23 AM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 10:34 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"