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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,130
Messages
5,835,573
Members
430,367
Latest member
User800

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top