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 |

3/25/2010 5:17:03 PM

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 |

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 |

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 |

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 |

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 |

3/25/2010 6:27:01 PM

Hey, I need to insert a photo into a cell (a cell that has been merged btw) based on the information in a cell (i.e. A1). This cell (A1) uses list validation and allows one to choose from a list of names. What I want to do is put a photo into a cell (say B1) from a folder (say C:\Photos\) based on the name that is selected in A1. Make sense? azidrane, have a look here and see if this will do what you want http://www.mcgimpsey.com/excel/lookuppics.html -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Fee...

Hi - I'm using a custom function in a query and it takes forever - was hoping someone might have an idea of how to speed things up a bit. A little more info: the function is relatively simple (4 arguments that are run through an IF statement to pick a value from another table (only 12 records). The query is run against a table with 41k records. The numbers don't seem to big here, and the query takes over an hour. Any ideas?? Where & how are you using the IF's? Sounds like a coding problem to me Pieter <stephen.h.dow@gmail.com> wrote in message news:1189019241.93425...

Im working on two sheets On sheet 1, I wish cell e37 to enter the number of times a date is placed in the column h34:h44. (These dates will be randomly entered manually later) The dates will fall on or between the current day "today()" and a date enntered in sheet 2, cell N2 Every syntax I try falls over and I keep getting zero values in E37. Its got to be easy but it escapes me. Any help out there? -- dond ------------------------------------------------------------------------ dond's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25477 View this t...

Hi i was running if there was a percentile IF function or a way to run something similar to an average if function. Lets say I have a data set like below a 6 b 1 b 2 b 3 a 4 b 5 a 10 c 12 What I want to achieve is to find the percentile of all data points that are taged "a". The average if function for this data looks like =AVERAGEIF(D1:D8,"a",E1:E8) So ideally I want to be able to have a =PERCENTILEIF(D1:D8,"a",E1:E8), sorting column d is no appropriare as different filters are constantly applied to this data set. Potentially I would want to ...

Hey guys, I have 2 related cases that I dont understand how to get Excel formatting to work: 1. If I imported or copied rows of data (numerical) from another file (Word or Access) into Excel, the data is presented as raw numbers in each cell. Now I apply a formatting (i.e. I want comma separation for thousands, etc.) and it will not show up. However, if i then double-click inside a cell (as if to edit the contents directly), then hit enter the formatting I want shows up... but only that cell. I have hundreds of rows and I really need to force excel to display the formatting i want immediately...

I would like to click on a cell and obtain a character count. Is it possible? 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not IsEmpty(Target.Value) Then MsgBox Len(Target.Value) End If End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Paul K." <Pa...

Previously excel would show where I copied a cell from, ='EBO 2005'!J37, but now it only shows the value of the, $3,433.04. Previously this was just a cut and paste, but something has been changed in excel. Hi Jim It sounds as though you did a Paste Special>Values as opposed to Paste (perhaps inadvertently). Copy and Paste functionality has not altered. -- Regards Roger Govier "Jim" <Jim @discussions.microsoft.com> wrote in message news:B64910BC-3854-4E77-A410-3AC51B9BAE53@microsoft.com... > Previously excel would show where I copied a cell from, ='...

Does anyone know a way around this error other than to start over and reformat? I think you have two choices - either clear ranges of cells of their formats or upgrade to Excel 2007, which has a greater limit on the number of formats. -- Jim "Gene" <genemont@spircomfg.com> wrote in message news:jb2dnZOUOdFqjCrVnZ2dnUVZ_tLinZ2d@posted.webnetmemphis... | Does anyone know a way around this error other than to start over and | reformat? | | ...

In a table below, I am trying to "Count" the number of time a row begining with "H" has a number "1" iin it. I believe this has to be a "Nested" function but I am unsure. I will repeat this function for the leters A/U/S, and for the number 0/2 as well. H 1 1 0 2 0 H 2 2 1 0 0 A 1 1 1 1 1 U 0 0 U 2 1 1 S 0 H 1 2 1 2 1 A 2 2 2 1 1 U 1 1 1 1 2 Rick Hi Rick try the following for counting all H/1 in your range: =SUMPRODUCT((A1:A999="H")*(B1:H999=1)) assumption: Your range extends from A1...

Folks, I've programmed an AccessXP database that makes extensive use of VBA module code called via Macro in response to form object events. Since yesterday's Office Security updates (multiple), a particular module function called via macro has begun to throw Microsoft Visual Basic Run-time error 2427 - You entered an expression that has no value. On debug, it jumps to the second in a series of about 6 If/Else statement in which the [Object].Value is tested for content. The first appears to work correctly, but commenting out the following 5 items one at a time causes the func...

Hello, while addressing cells in a spredsheet from PivotTable Excel 2007 always puts GetPivotData functionautomatically instead, like old versions, simply addressing cells (like =B3 or simillar). It makes my formulas very long and not possible to understand. I wonder if I can switch off this automatic functuion and tell Excel to address cells just by they simple address? Thanks Rafal Office button/Excel Options/Formulas/2nd section: deselect "Use GetPivotData functions..." "Rafal Hemmerling" <Rafal Hemmerling@discussions.microsoft.com> wrote in message news:...

Here's what I've got to do: Column Row Priority Rating 1, 2, or 3 CIA Rating (spread over three columns) 0, 1, 2, or 3 - can be any combination of three digits (ex: 011, 111, etc) Priority Validation ODD or EVEN I need to count the number of records by country (Country names are located in column D) where records with the following cond...

I'm trying to do an if,then function that looks at data in a cell and compares to 13 different possible results but excel on allows 7 nested IF functions. Any thougths on how to complete this task? This is in regards to banking. The function should look at the term (13 different terms) and based on the term should return the appropriate rate. Thanks With that many, I would use a VLOOKUP. To learn how to use a VLOOKUP, see: http://www.contextures.com/xlFunctions02.html Or, if you're dead set on IFs, you could always concatenate them: =IF(A1=1,1.3%,"")&IF(A1=2,...

For unknown reason, I can't move cell selection using the left,right,up,down key scroll lock key ?? -- Don Guillett SalesAid Software dguillett1@austin.rr.com "crapit" <biggercrap@yahoo.com> wrote in message news:umFtFtNfGHA.3996@TK2MSFTNGP04.phx.gbl... > For unknown reason, I can't move cell selection using the > left,right,up,down key > ...

For some reason, and I'd be very interested to know WHAT the reason is, I cannot select a cell and enter the number 9. If I double-click on the cell I can enter the number 9, but not just by selecting the cell. If I just select a cell and press 9 the system beeps at me. What could this be? -gk- Look at the Autocorrect entries. Bernard "TBA" <toxicdistortion@hotmail.com> wrote in message news:vlrjncc9f5f2b6@corp.supernews.com... > For some reason, and I'd be very interested to know WHAT the reason is, I > cannot select a cell and enter the number 9. If I ...

I just got a book called, Excel for Chemists. But it didnt tell me something i want to know. I wonder if someone out there can help me? Here is the problem, say that i have put some data into a worksheet. A1 cell = XValues and A2 cell = YValues. If i have a Function that takes 2 arguments below how do i write code to pass, XValues, Yvalues to the function Deming? Example below: Function Deming(XValues, Yvalues) Dim MeanX(), MeanY() 'Get number of cells to use in calculation loop Ncells = XValues.Count ReDim MeanX(Ncells / 2), MeanY(Ncells / 2) N = 0 put code here......do something......

Hi All, I would like to ask if there is a specific ID for each server or at least for each instance? I would like to use it like @@nodeid (which i know from Sybase) Thanks One of these might help you... SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS'), ISNULL(SERVERPROPERTY('InstanceName'), 'Default'), SERVERPROPERTY('ServerName'), SERVERPROPERTY('MachineName'), DB_NAME() Other than that, look into the ServerProperty function for more options. http://msdn.microsoft.com/en-us/library/ms174396.aspx -Eric Is...

Apologies in advance, I am NOT a coder, I am trying to fix VBA someone wrote for me by 'slogging' through it on my own. Making some decent headway on easier stuff but I am at an impasse. He has a function that creates an Acronym out of a Value. However I want to modify it so it ONLY does it if the # of words in that value > 1. Can anyone give me a heads up on how to add that here please? It would be most appreciated: Private Function GenerateAcronym(ByVal val As String) As String 'Tested OK Dim str As String, prom As String, ch As String, res As String ...

Hello, I have these two monster formulas that I want to add together to get a grand total of time. One is: =YEAR(C9)-YEAR(C8)-IF(OR(MONTH(C9)<MONTH(C8),AND(MONTH(C9)=MONTH(C8), DAY(C9)<DAY(C8))),1,0)&" years, "&MONTH(C9)-MONTH(C8)+IF(AND(MONTH(C9) <=MONTH(C8),DAY(C9)<DAY(C8)),11,IF(AND(MONTH(C9)<MONTH(C8),DAY(C9) >=DAY(C8)),12,IF(AND(MONTH(C9)>MONTH(C8),DAY(C9)<DAY(C8)),-1)))&" months, "&C9-DATE(YEAR(C9),MONTH(C9)-IF(DAY(C9)<DAY(C8),1,0),DAY(C8))&" days" I want to add the above with: =YEAR(C12)-YEAR(C11)-IF(...

=SUM(--('Sheet1'!$B$2:$B$2000<=BB$3),--('Sheet1'!$B$2:$B$2000>=BB$2),--('Sheet1'!$D$2:$D$2000=$B7), --('Sheet2'!$B$2:$B$2000<=BB$3),--('Sheet2'!$B$2:$B$2000>=BB$2),--('Sheet2'!$D$2:$D$2000=$B7), --('Sheet3'!$B$2:$B$2000<=BB$3),--('Sheet3'!$B$2:$B$2000>=BB$2),--('Sheet3'!$D$2:$D$2000=$B7), --('Sheet4'!$B$2:$B$2000<=BB$3),--('Sheet4'!$B$2:$B$2000>=BB$2),--('Sheet4'!$D$2:$D$2000=$B7)) Sheets 1-4 all list claims but they need to be seperated. The values that are target...

HI Gurus I am new for C++ I want to create some drawing function and call from VB so i just try to create dll using VC++6 void _stdcall DrawBox(CDC *Dc, POINT Pt1, POINT Pt2 ) { // Dc.MoveTo (Pt1); // Dc.LineTo (Pt2); } but it give error "CDC is undeclared identifier" i have include <afxwin.h> also even it give err error LNK2005: _DllMain@12 already defined in Test1.obj what should i have to do I dont know what you are trying to do this. If you want to repaint, do an UpdateWindow/InvalidateRect. As far as putting this in a DLL, you will need to know what type of DLL ...

I would apreciate your help, thank you. I have Windows Vista Businees x86, Microsoft Office Basic 2007. The problem first appeared when i reinstalled everything a few days ago. The steps i took are the folowing: Format c: -> Reinstal Vista(internet cabe unpluged) -> install Office -> start Office - > open worksheet(any worksheet) -> Office crashes I also tried the folowing: Format c: -> Reinstal Vista(internet cabe unpluged) -> install SP1 then SP2 -> install Office -> install updates(windows Update) -> all updates installed-> start Office - > open wo...

I have the following IF statement written into this cell: =IF(N6>=1,O6,IF(P6>=1,Q6,IF(R6>=1,S6,IF(T6>=1,U6)))) If cells N6, P6, R6 or T6 all have zeros written into them, cell E6 shows the value "FALSE". How can I have this cell have the value 0 under these conditions? Like this: =IF(N6>=1,O6,IF(P6>=1,Q6,IF(R6>=1,S6,IF(T6>=1,U6,0)))) Regards, Fred "JG" <JG@discussions.microsoft.com> wrote in message news:5035242E-9052-4866-8397-66C59C71D089@microsoft.com... >I have the following IF statement written into this cell: ...

I know this should be so simple, but what would be the code to backstep 1 column from the active cell? Rob Rob, activecell.Offset(0,-1).Select John "rob nobel" <robnobel@dodoNOGOOBS.com.auNOGOOBS> wrote in message news:eLanqh%232DHA.1264@TK2MSFTNGP11.phx.gbl... > I know this should be so simple, but what would be the code to backstep 1 > column from the active cell? > Rob > > Hi Rob! Try: ActiveCell.Offset(0, -1).Select -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Synt...

I am using the "find" function to locate a particular letter in a cell. This works fine except when the letter does not exist in the cell. Displayed in the cell is "#VALUE!" and is not useable. Is there a way to =if(find("G",A1,1) = "nothing found",A1,find("G",A1,1)) =if(iserror(find("G",a1,1)),"nothing found",find("G",a1,1)) will return either the string "nothing found" or the position of the first G in A1. You could also use =isnumber() =if(isnumber(find(...)),"found it","not f...