how do you do an if statement to check if a cell contains a value like a
number or a formula?

Hi,

Excel only provides that capability via VBA. You could write a VBA Function.

Here is a very basic example:

Function AFormula(cell As Range)
AFormula = cell.HasFormula
End Function


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Joe" wrote:

how do you do an if statement to check if a cell contains a value like a
number or a formula?

Joe,

To find IF formulas or numbers (constant) or txt, etc...are STATED inside
the cells.

from xls03 help file, you may try this..

*Select cells that contain formulas*
1.Do one of the following:
Select the range (range: Two or more cells on a sheet. The cells in a range
can be adjacent or nonadjacent.) that includes the type of cells you want to
select.
Click any cell to select all cells of this type on the active worksheet.

2.On the Edit menu, click Go To.
3.Click Special.
4.Click Formulas, and then select the check box next to the type of data you
want to select.

you can place pattern colors cells after xls selection.

*same applies for numbers (constant)

hop this will help,

--
regards


"Joe" wrote:

how do you do an if statement to check if a cell contains a value like a
number or a formula?

I have a column with formulas and another column with just values and I want
to do an IF statement by checking if the cell contents are a formula or not.
I know many times I have problems with a cell if it has a formula which would
not be there if the it was just plain numbers so there should be a way to
tell them apart with a caculation or function.

"Joe" wrote:

how do you do an if statement to check if a cell contains a value like a
number or a formula?

Joe,

Since your file is inside the excel environment, excel built-in tools are
provided for more convenience and distinction of data entered in its
environment.

My previous suggestion is a copy-paste from excel platform.

--
regards,


"Joe" wrote:

I have a column with formulas and another column with just values and I want
to do an IF statement by checking if the cell contents are a formula or not.
I know many times I have problems with a cell if it has a formula which would
not be there if the it was just plain numbers so there should be a way to
tell them apart with a caculation or function.

"Joe" wrote:

how do you do an if statement to check if a cell contains a value like a
number or a formula?

What's wrong with Driller's proposal?Just select the column, press F5 and
'Go To' Special, select 'Formulas' and check, if there are any cells
containing formulas.

As an even easier brute force solution you could copy the whole column and
Paste Special Values over the selection.

Unfortunately there is no built-in function that you could use for catching
formulas.

Joerg


"Joe" wrote in message
...
I have a column with formulas and another column with just values and I
want
to do an IF statement by checking if the cell contents are a formula or
not.
I know many times I have problems with a cell if it has a formula which
would
not be there if the it was just plain numbers so there should be a way to
tell them apart with a caculation or function.

"Joe" wrote:

how do you do an if statement to check if a cell contains a value like a
number or a formula?

I think I should have been more clear. Sorry about that. I'm not just trying
to identify the cells. I am trying to use this in an if statement to
determine how to treat a portion of data. I ended up using the Visual Basic
=IsFormula. Thanks for your help.

"Joerg Mochikun" wrote:

What's wrong with Driller's proposal?Just select the column, press F5 and
'Go To' Special, select 'Formulas' and check, if there are any cells
containing formulas.

As an even easier brute force solution you could copy the whole column and
Paste Special Values over the selection.

Unfortunately there is no built-in function that you could use for catching
formulas.

Joerg


"Joe" wrote in message
...
I have a column with formulas and another column with just values and I
want
to do an IF statement by checking if the cell contents are a formula or
not.
I know many times I have problems with a cell if it has a formula which
would
not be there if the it was just plain numbers so there should be a way to
tell them apart with a caculation or function.

"Joe" wrote:

how do you do an if statement to check if a cell contains a value like a
number or a formula?

just curious, how are these categorized; (value or formula)

for a cell that contains either <excluding quotes " "s

"=25"

or

"25"

any suggestion.

(does a formula need an operator=1 ; i.e +/-/*)

--
regards

If the cell contains a formula it is just a normal formuala like =A1+A2 or
=IF(A1="","",A1+A2). Maybe there is an easier way to do this. Some background:

I have two sheets. Lets call them sheet A and Sheet B.

Sheet A is a balance sheet and profit and loss financial statement. I enter
my data in this sheet.

Sheet B is a running accumulation of each months financials. On sheet B I
have the financial data for Jan, then in the next columns I have the same
thing for Feb, and so on.

The cells in the columns in sheet B contain formulas that fill themselves in
by what is entered in sheet A. I know this is kind of backwards but it's the
way this workbook is stuctured.

When I enterd Jan data in Sheet A it automatically fills in Column A in
sheet B. Since the columns in sheet B or looking at sheet A, the only way I
can retain the Jan data on sheet B is to copy it and then paste values. This
makes it stay there. Then as I enter Feb data in Sheet A, sheet B column B
looks at it column heading which would be Feb and sees the date of Feb on
Sheet A and fills itself in with the data from sheet A.

This brings me to what I was asking about. I wanted a warning on Sheet A
with conditional formatting that gave me a STOP message if the column in
Sheet B was still in the fromula form and had not been copied and value
pasted. This way I would not be losing all my Jan data in Sheet B as I
changed the date in Sheet A.

The actual formula I ended up in Sheet A is:

=IF(MONTH(A7)=MONTH(TODAY())-2,IF(isformula(INDEX(Months!T5:AE5,MATCH(A7,Months !T1:AE1,0))),"STOP!","GO"),"OK")

A7 is the cell where I enter the date of the month I am closing. In this
case it is 2/28/2009.

T5:AE5 is the range of cells to check to see if the data in the form of a
formula or just a number.

T1:AE1 is the range of cells that contain the text that tells each column
what month they are.

The formula looks at a column in Sheet B that corresponds to the previous
month and if it is still in formula form it says STOP! and if it is just
number values it says GO or OK. The -2 is because I am always 2 months ahead
of the closed month. I close Feb the first week in March and it in this case
it would be Jan that would need to be not lost.

Thanks

"driller" wrote:

just curious, how are these categorized; (value or formula)

for a cell that contains either <excluding quotes " "s

"=25"

or

"25"

any suggestion.

(does a formula need an operator=1 ; i.e +/-/*)

--
regards