formula not working

[mds7189]

I simply want the data in one cell in one sheet to be exactly the same as the data in another cell in another sheet.

For one cell excel should pull N/A instead =Sheet1!B5 shows up in the cell.

For another cell excel puts zero in when it should be putting nothing.

What am I doing wrong? How can I fix this?

[SAP PoD]

My guess is that you are viewing the formulas rather than results.

Try:-

Tools -> Options -> View tab.

If the Formula box is ticked, take it out and see if that resolves

[mds7189]

I unchecked Formula bar in the view tab but I am still getting the same result.

I have 4 columns in my spreadsheet. The other two colums are correctly pulling the data.

[Chris Berry]

For your second problem, put an if statement in there. Something like =if(c16<>0,c16,""). Of course you'll have to tailor the statement to what you want.

I would have guessed you had show formula's checked on your first problem if Sheet 1 B5 is giving a value. What does this cell show, or what is in this cell?

[mds7189]

Thanks chris. The if statement solved my first problem.

Sheet 1 B5 shows N/A.

Sheet 2 B5 shows =Sheet1!B5

[Chris Berry]

I don't want to offend you or anything, but are you sure there is not an apostrophe at the beginning of the input in sheet 2 B5?

Mama always said try the simple things first

[mds7189]

No there is definitely not an apostophe.

I set Sheet2!B2 and Sheet2!B33 equal to Sheet1!B5 and it works.

Only cells Sheet2!B5 - Sheet2!B32 are displaying the formula.

[Chris Berry]

That's odd. I'm sorry, but I'm out of ideas. I'll keep checking back to see if somebody else can figure out what's wrong.

Here's a nifty little macro created by John Walkenbach that makes comparing formulas in cells fairly easy.

Please Login or Register  to view this content.

This will create a tab that will list the cell address each formula is in, the formula itself and the value the formula returns. It could be handy in troubleshooting your problem.