Function to determine if a cell contains a formula

In excel 2007, is there a formula to determine whether or not a cell contains 
a formula?  Basically I want to do and If/Then function that will return a 
certain answer if a cell contains a formula and will return a different 
answer if the cell only contains text (that was manually entered in).

Thank you.
DoubleZ
0
Utf
3/25/2010 5:17:03 PM
excel.misc 78881 articles. 3 followers. Follow

5 Replies
1274 Views

Similar Articles

[PageSpeed] 48

Hi,

How about a UDF. Alt + F11 to open VB editor. Right click 'this workbook' 
and insert module and paste the code in

Call with

=isformula(A1)

Function IsFormula(cel As Range) As String
    If cel.HasFormula Then
        IsFormula = "It's a formula"
    Else
        IsFormula = "It's not a formula"
    End If
End Function
-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"DoubleZ" wrote:

> In excel 2007, is there a formula to determine whether or not a cell contains 
> a formula?  Basically I want to do and If/Then function that will return a 
> certain answer if a cell contains a formula and will return a different 
> answer if the cell only contains text (that was manually entered in).
> 
> Thank you.
> DoubleZ
0
Utf
3/25/2010 5:35:01 PM
Unfortunately, no. You could use a short UDF to determine this though. To 
install the UDF, open the VBE (Alt+F11) and goto Insert, module. Paste the 
following in:

'==========
Function IsFormula(r As Range) As Boolean
IsFormula = Left(r.Formula, 1) = "="
End Function
'==========

Close the VBE, and back in your workbook, you can simply use the formula
=IsFormula(A1)
to check if a cell contains a formula or not.

-- 
Best Regards,

Luke M
"DoubleZ" <DoubleZ@discussions.microsoft.com> wrote in message 
news:25C1DBFA-B8D0-47DC-BEE0-54541A6B4B26@microsoft.com...
> In excel 2007, is there a formula to determine whether or not a cell 
> contains
> a formula?  Basically I want to do and If/Then function that will return a
> certain answer if a cell contains a formula and will return a different
> answer if the cell only contains text (that was manually entered in).
>
> Thank you.
> DoubleZ 


0
Luke
3/25/2010 5:43:10 PM
For many years, the documentation for the CELL function stated that
you could use it to determine whether a cell has a formula, but it
never worked. So they changed the documentation.

You have to use a VBA function:

Public Function HasFormula(R As Range) As Boolean
	HasFormula = R(1,1).HasFormula
End If

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
	Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com




On Thu, 25 Mar 2010 10:17:03 -0700, DoubleZ
<DoubleZ@discussions.microsoft.com> wrote:

>In excel 2007, is there a formula to determine whether or not a cell contains 
>a formula?  Basically I want to do and If/Then function that will return a 
>certain answer if a cell contains a formula and will return a different 
>answer if the cell only contains text (that was manually entered in).
>
>Thank you.
>DoubleZ
0
Chip
3/25/2010 5:59:15 PM
Borrowing from Mike's post...

Function IsFormula(r As Range) As Boolean
IsFormula = r.HasFormula
End Function

-- 
Best Regards,

Luke M
"Luke M" <lukemoraga@nospam.com> wrote in message 
news:OG4MkKEzKHA.928@TK2MSFTNGP05.phx.gbl...
> Unfortunately, no. You could use a short UDF to determine this though. To 
> install the UDF, open the VBE (Alt+F11) and goto Insert, module. Paste the 
> following in:
>
> '==========
> Function IsFormula(r As Range) As Boolean
> IsFormula = Left(r.Formula, 1) = "="
> End Function
> '==========
>
> Close the VBE, and back in your workbook, you can simply use the formula
> =IsFormula(A1)
> to check if a cell contains a formula or not.
>
> -- 
> Best Regards,
>
> Luke M
> "DoubleZ" <DoubleZ@discussions.microsoft.com> wrote in message 
> news:25C1DBFA-B8D0-47DC-BEE0-54541A6B4B26@microsoft.com...
>> In excel 2007, is there a formula to determine whether or not a cell 
>> contains
>> a formula?  Basically I want to do and If/Then function that will return 
>> a
>> certain answer if a cell contains a formula and will return a different
>> answer if the cell only contains text (that was manually entered in).
>>
>> Thank you.
>> DoubleZ
>
> 


0
Luke
3/25/2010 6:08:26 PM
Thank you Mike and Luke!  I am surprised to hear that Excel does not have 
that functionality built in, but at least it isn't a difficult UDF.

Thanks again.
DoubleZ

"Luke M" wrote:

> Unfortunately, no. You could use a short UDF to determine this though. To 
> install the UDF, open the VBE (Alt+F11) and goto Insert, module. Paste the 
> following in:
> 
> '==========
> Function IsFormula(r As Range) As Boolean
> IsFormula = Left(r.Formula, 1) = "="
> End Function
> '==========
> 
> Close the VBE, and back in your workbook, you can simply use the formula
> =IsFormula(A1)
> to check if a cell contains a formula or not.
> 
> -- 
> Best Regards,
> 
> Luke M
> "DoubleZ" <DoubleZ@discussions.microsoft.com> wrote in message 
> news:25C1DBFA-B8D0-47DC-BEE0-54541A6B4B26@microsoft.com...
> > In excel 2007, is there a formula to determine whether or not a cell 
> > contains
> > a formula?  Basically I want to do and If/Then function that will return a
> > certain answer if a cell contains a formula and will return a different
> > answer if the cell only contains text (that was manually entered in).
> >
> > Thank you.
> > DoubleZ 
> 
> 
> .
> 
0
Utf
3/25/2010 6:27:01 PM
Reply:

Similar Artilces:

Show actual values added instead of cell references? (=A1+A4 shows as =10+2 in cell)
Is this even possible? I have the following: A1 =A2 (Shows result of 10) A2 10 A3 A4 =A5 (Shows Result of 2) A5 2 A7 =10+2 (Shows result of 27) Is there a way to show the formula in A7 as =10+2 instead of having it =A2+A5 ??? Thanks a bunch! On Dec 29, 1:24=A0pm, Naji <najisaa...@gmail.com> wrote: > Is this even possible? > > I have the following: > > A1 =A0=3DA2 (Shows result of 10) > A2 =A010 > A3 > A4 =3DA5 (Shows Result of 2) > A5 2 > > A7 =3D10+2 (Shows result of 27) > > Is there a way to show the formula in A7...

Can a Formula in Cell X modify Cell Y?
Example: A Formula in Cell A1 should do the following: If cell A1 equals 1, then cell B1 should have the value 5, otherwise B1 should be 6. A condition in cell A1 is changing the value of cell B1... is that possible? Please note that there is NO Formular in Cell B1, which is the cell to be modified... Thanks No, only through VBA -- Regards, Peo Sjoblom "alMandragor" <alMandragor@discussions.microsoft.com> wrote in message news:94154F2A-DBD2-4A47-AF47-F21935C77B91@microsoft.com... > Example: > > A Formula in Cell A1 should do the following: > > If c...

Returning a range from an inner function
I have a range of data, which has had the DOLLAR() function performed on it. I would like to get the total of this range using the SUM() function. doing SUM(F1:F4) will not work, so I need to convert the data into values that will work. The VALUE() function is perfect for converting the data into values that will work for SUM(), but the formula SUM(VALUE(F1:F4)) does not work because VALUE() will not accept a range as a parameter (and also does not return a range). I need to perform the VALUE() function on each cell before submitting it to SUM(). There must be some way to do this, cons...

Can formulas in cells be made to remain if the data is deleted?
Can you explain in a bit more detail what your question/problem is? -- Kind regards, Niek Otten "wendyp" <wendyp@discussions.microsoft.com> wrote in message news:B7A92B0A-E752-4D58-90FF-3DE5F292D35C@microsoft.com... > Hi Wendy, See Insert a Row using a Macro to maintain formulas http://www.mvps.org/dmcritchie/excel/insrtrow.htm To simply remove constants from a selection within a macro Selection. SpecialCells(xlConstants).ClearContents To remove constants manually from a selection Edit, GoTo (Ctrl+G), Constans [you can pick what kind ...

Converting Text to an external cell ref.
Hi, I'm, trying to put together an external cell reference in my worksheet. I can join together text that looks like the File Path / Work Sheet / Cell reference, but I can't figure out how to turn this string of text into a file path etc. and pick up the data I want. I get this strange feeling I'm being a complete idiot asking this can't see the wood for the trees, but I need help. --- Message posted from http://www.ExcelForum.com/ Hi normally you can use INDIRECT for this. E.g. =INDIRECT("'[Book1.xls]Sheet1'!A1") which gets the value from cell A1, sheet...

formula query
how do i display only formula in an excel spreadsheet? Hi do you want to display the formula text? -- Regards Frank Kabel Frankfurt, Germany "johan" <johan@discussions.microsoft.com> schrieb im Newsbeitrag news:A6E861AB-5BFF-4911-83A2-7B7D8849637C@microsoft.com... > how do i display only formula in an excel spreadsheet? Ctrl + ` (grave accent) will toggle between formula and non formula view you can also go to tools>options>view andf check formulas Regards, Peo Sjoblom "johan" wrote: > how do i display only formula in an excel spreadsheet? ...

ROUND Function with other function
Hi, I have this formula: =IF(R8>0,CONCATENATE("+",R8),R8) and the results shows me for example: +11.6 but what I really want t show is +12. I've been trying to put the "Round" function at a few places in th formula but it doesn't work. The closest I got was with this formula: =ROUND(IF(R8>0,CONCATENATE("+",R8),R8),0) where it showed me 12, without the "+" sign before.... Can anyone help me ? Thank -- Outapi ----------------------------------------------------------------------- Outapin's Profile: http://www.excelforum.com/member...

Conditional Formatting
Is it possible to format a portion of a text string within a cell (as opposed to the entire cell). For example, I would like to format the word 'gift' in red font anywhere it a appears in range C2:C417 but only that word, not the entire cell. Not with conditional formatting. But you could change the actual format for that word (or group of characters)... Saved from a previous post (or two!): If you want to change the color of just the characters, you need VBA in all versions. You want a macro???? Option Explicit Option Compare Text Sub testme() Application.ScreenUpdating ...

Re: Lifetime customer value formula?
I'm probably asking a very basic question, so please forgive me. suspect there's a standard formula for this, but I cannot find it. I'm looking for a formula for a spreadsheet I'm building that will giv me the lifetime customer value for a subscription website. I don't need a net present value for the sum. The input variables are: 1. Initial sign-on fee ($IN) 2. Access fee per billing cycle ($AF) 3. The retention rate - % who renew membership each billing cycle (R%) The formula shouldn't go through infinite cycles. A close approximatio would be adequate. For ins...

JustifyString function problem
Hi, I'm using the "JustifyString" function (from Stephan Lebans) to right justify three columns in a ListBox. Two of them are working fine ... one's a simple number (long integer) and the other's currency. The third causes an Access 2003 crash! That third column is a long integer which is a link to the autonumber field in another table. Both tables are in the query as I need columns from both tables in the ListBox. If I change the JustifyString to reference the autonumber field in the other table, I still get an Access crash. I didn't review the Access er...

Returning a CHAR[] from a function
I have the following function that returns the path of where the program was run from. However I cant seem to return the butter as a CHAR. The code works if I use it in a procedure but not where I need to return the variable CHAR buffer. char CAutoRunDlg::GetProgramPath(void) { char buffer[_MAX_PATH]; //declares maimum path //opens window maximized //_getcwd(,)gets the current working directory // #include <direct.h> //needed for current working directory /* Get the current working directory: */ if( _getcwd( buffer, _MAX_PATH ) == NULL ) perror( "_getcwd error" )...

Using a formulae within scenarios
Hi, I've just used scenarios for the first time but am a little frustrated in that it only remembers specific values rather than the actual cell reference that I type into the "Enter values for each of the changing cells" boxes. Is there a way of doing this as I wanted to include a whole range of values in the scenario that would be replaced each time. For example consider a basic cashflow forecast where line 1 is balance brought forward lines 2-5 are various incomes lines 6-8 are various payments line 9 is balance carry forward (sum of all of the above) each column represe...

How to have auto dialog box showing when selecting cell
I would like to have a dialog box appear each time a cell is selected in a column. I need the dialog box show the next cheque number and allow the user to change that if necessary. The cells cannot have a formula in it as that would cause problems when sorting. Is there a way to do this? Rob You could use data validation to do this. Firstly in an empty area of the worksheet define a cell that is equa to max(column range where the cehque numbers are shown) + 1. (ie max(a:a) + 1 Then set the data validation for the whole cheque input range to list list = the address of the new cell. Th...

Cell manipulation
I have a spreadsheet with 20 rows of data. The data is one cell in length and goes from cell A1 to A20. What I'm wondering is if there's a formula or function that I can use to take the data that is in cell A2 and move it to B1 and likwise down the list -- A3 to C1, A4 to D1. This 20 item list is a very small one. I'm going to be working with 200 items and larger and need to do the same thing. Any help is appreciated. Thanks, Kevin G copy. paste-special in a blank cell with transpose selected. >-----Original Message----- >I have a spreadsheet with 20 rows ...

automating functions
I am a complete novice at CRM 1.2, but want to be able to create, for example, a workflow process that will monitor the CSR's and the queues and automatically assign a new case to the least busy CSR. Any input would be appreciated. Thanks in advance Jim Please tell me what I would have to do and with what tools in order to create such a workflow process. "jmb" wrote: > I am a complete novice at CRM 1.2, but want to be able to create, for > example, a workflow process that will monitor the CSR's and the queues and > automatically assign a new case to the lea...

Turning Off Error Reporting using API Functions and VB
We want to automatically turn off the error reporting options using Windows API functions for the following functions area Click on Start, Settings, Control Panel, System, the Advanced tab and Error Reporting (at the bottom of the screen), which launches the Error Reporting screen. Another way to get to that screen: Open My Computer, right-click on Properties, click on Advanced tab and Error Reporting. Any assistance would be welcome. It's not a program's call. It's the user's decision. --=20 -------------------------------------------------------------------------= --...

Complex functions
Hi All, Suppose you have a very complex and long function that is also subject to a condition, then you will/can have something like: =if(condition(complex function), complex function, other complex function) It can have more lines in the formula bar, so readability below 0 Kelvin. Is there a way to 'name' a function? =If(condition(myfunc = complex function), myfunc, other complex function) With kind regards, JP Yes you can put a formula into an Excel Name (the same as you can give a range an Excel Name. Very powerful it can be too. Do a Google fo...

How do I add a cell reference in a header?
I'm trying to add a cell reference in the header of an Excel document. The workbook allows a user to select choices from a drop-down menu and pulls the specific data into summaries, but I would also like the selection to change the header each time. Hi only possible with VBA using an event procedure. e.g. put the following code in your workbook module: Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim wkSht As Worksheet For Each wkSht In Me.Worksheets With wkSht.PageSetup .CenterHeader = wkSht.range("A1").value End With Next wkSht End Sub -...

Formula #32
I need a formula for this: If C1 is blank and B1 is less than 4/03/2008 count as 1. Answer: 2 A B C 1 Almond Joy 04/01/2008 2 Baby Ruth 04/02/2008 3 MARS 03/28/2008 04/01/2008 4 Mounds 04/10/2008 04/15/2008 5 Reeses 05/01/2008 6 Starburst 03/25/2008 03/31/2008 Thank you! Try this: =3DSUMPRODUCT((C1:C10=3D"")*(B1:B10<--"4/03/2008")) Adjust the ranges to suit. Hope this helps. Pete On Sep 2, 10:08=A0pm, Accesscrzy <Accessc...@discussions.microsoft.com> wrote: > I need a fo...

Match Function
Hi, I have put together a spreadsheet to find codes, states, descriptions. It finds the data by dates. Columns are: Dates, Codes, States, Descriptions If I put in a date it matches the code, state and description. My problem is that if I have 2 dates that are the same it will only match the first date it can find. What kind of formula do I need so that it will output the second or third date data. Thanks for your help. If you need a copy of the spreadsheet please let me know. How many rows of data do you have? -- Biff Microsoft Excel MVP "Melanie" <Melanie@discussio...

If Function Function
okay. So I am trying to workout a formula to compute a commision earning. Basically, if X amount of new business is made, then X*Y (corresponding Commision rate percentage), but if new business =<X, then X*Y (Commision rate percentage that is true). Problem is, it's super confusing...anyone have any suggestions? See if this helps: http://mcgimpsey.com/excel/variablerate.html -- Biff Microsoft Excel MVP "aetejada" <aetejada@discussions.microsoft.com> wrote in message news:AA4E55F7-9435-4BB6-837A-416A59BB72A0@microsoft.com... > okay. So I am...

Outlook 2002 Change Password function
I want users to be able to change password from the Outlook Login Page, but it does noet seem to work. Outlook 2002 Sp1 and Windows Server 2003 (AD) and Exchange 2003 SP1 Important: Outlook client is not member of the domain... There are not many Microsoft Articles about this. Anyone? -- Regards, Menko den Ouden Netherlands Menko den Ouden schreef in deze nieuwsgroep op 23-6-2005 15:28: > I want users to be able to change password from the Outlook Login Page, but > it does noet seem to work. > > Outlook 2002 Sp1 and Windows Server 2003 (AD) and Exchange 2003 SP1 > ...

DateDiff function throwing error in Where clause
I have a set of queries that generate dates and want to use DateDiff to select those records with certain dates are greater than others. The following clause keeps giving me an incompatible data type error: WHERE DateDiff('d',[From date],[DatumStvoreni])>0 I have the exact expression in the SELECT clause and it show positive and negative integers just fine, but when I add this test, I get the error. There are no bad values in the dataset, I've looked, and when I export the DateDiff column to a temp table and run a query on that, it works fine. Only when I use t...

What if formulas and calculations
I need to create a calculation based upon a number of fields e.g. A1 = Y or N B1 = Y or N C1 = 32 D1 = a number E1 is for the result Required: If A1 = Y and B1 = Y then E1 = C1 * D1 else E1 = D1 All I can get is FALSE if either A1 or B1 is not Y but is both are Y then I get the correct result =IF(AND(A1="Y",B1="Y"),C1*D1,D1) -- Kind Regards, Niek Otten Microsoft MVP - Excel "BJS" <BJS@discussions.microsoft.com> wrote in message news:60CBD837-9F0A-4AB9-88CC-7493705AAA33@microsoft.com... >I need to create a calculation based upon a number of fie...

How to Select Multiple Cells Conforming to A Certain Criteria Automatically?
Dear all, I have a big table containing a lot of data in my worksheet. Within a particular column, I want to select cells whose row numbers form an arithmetic progression with a common difference 8(e.g. G6, G14, G22, ...). At the moment, I can only perform this task by selecting the cells individually while pressing "Ctrl", however, due to the large number of data in the table, this work is very tedious. Does anyone know if there is an automatic way to select these cells? Thank you. Best wishes, Alex, This code can do the job for you. Sub SelectCells() Dim rng1 As Range ...