Cell is showing the FORMULA and NOT the RESULT

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
Hi:

I am working on a spreadsheet and have entered a formula in cell A1

=IF('Deposit Form'!A11>"",'Deposit Form'!A11,"")

When I check my formula it shows the result I want however on cell A1 is showing the formula and not the result. Any idea how to change this? Maybe there is a better formula. I basically am saying that if A1 is blank then show blank, otherwise show the contents of A1

THANKS for your help,
Mark
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
There are two possiblities.

Option 1-This is the most likely.
The cell is formated as a text cell, so it displays the formula as text. This is different then using the ' in front of the text or just a general text format. Usually happens for legacy system exports of data.
To fix.
Select the cell.
Select Data....Text To Columns.
A wizard will appear.
Press Next twice.
Select General as the format.
Select Finish.

Option 2- There is a general flag to display formulas instead of the results in the options menu. This is a global value, so it will effect all formula's, not just one.
To fix.
Select Tools...Options.
On the view tab, make sure the Windows Options checkbox for Formula is unchecked.
Press OK.

HTH
Cal
 

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
THANKS Cbrine... I did have the columns formatted as Text. I have now changed them to General and evrything is wonderful once again. You learn something new every day.

Have a GREAT day,
Mark
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
One other gotya, that got me when this happened. Make sure you convert the formula before you copy it. Otherwise, the cell address will not increment like they should. So, if you are copying the formula down, enter the formula, convert it to general, then copy it down.

Cal
 

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507

ADVERTISEMENT

HI Cal:

THANKS for that tid bit off info. I am sure it will come in handy in the future. In regards to using an IF statement do you know if there is a WILDCARD for excel. What I am meaning is I want to create a statement that says if the cell contain the word Credit then show BLANK. Does this make sense? I know some other programs I have used I could put something like *Credit*

Any suggestions or solutions?

THANKS Again for your speedy assitance,
Mark :p
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
You use the Find or Search functions to do that,

Try:

=IF(ISERROR(FIND("credit",A1)),A1,"")
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196

ADVERTISEMENT

There is no like operator as an Excel function, but this will simulate the same thing.

=IF(ISERROR(FIND("test",B1)),"no","Yes")

Just change Find("test",B1) to the stirng you are searching for, and the cell address where it will exist.

HTH
Cal

Edit: To slow, someone beat me to the punch.:)
 

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
Hi NBVC:

I am not certain on how to use your formula as the Search and Find functions are new to me. I did however go and try setting up the search and wound up wit this formula:

=SEARCH("credit",'Deposit Form'!A11)

In turn when it finds the word credit it shows the # (spot) in the cell that the word starts at which is good however if the word Credit does not appear there it is showing #VALUE! Can the formula I am using be altered so if Credit doesn't appear it shows nothing (Blank cell). Hope this makes sense.

Bye 4 Now,
Mark
 

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
Hi Cal:

THANKS that seems to be working. My apologies for posting again to the board but I was not yet notified that you had replied or I wouldn't have had to bug NBVC

THANKS to both of you, and to the forum.
Mark
 

Watch MrExcel Video

Forum statistics

Threads
1,118,011
Messages
5,569,610
Members
412,282
Latest member
bobHenk
Top