Excel is being stupid - Help please!

[jeffsmarketplace]

Hi,

A couple of things first off why is it when I enter a formula like the
one below (which I will be asking for help on) excel will give me an
answer, but when I start messing with it excel will just show it like
it isn't a formula (without the = sign) but I can paste the same
formula in another cell and it will return a result. Its almost like I
corrupt the cell and I have to exit without saving for it to work
again - its killing me.

Now the problem at hand.
=IF(T25>A25,"0",T25)*VLOOKUP(F25,Dieload!G$2:AW$294,43,FALSE)



Column A R S T All the stuff in between A-R is not
referenced.

2 100 20 2

I hope you have enough information because I don't know how to explain
what I want, I just know I need this to work. I am not excel savvy and
probably have had to create a ton of references I don't need to get
where I am at right now in my project.

Jeff

 

[Don Guillett Excel MVP]

Hi,

A couple of things first off why is it when I enter a formula like the
one below (which I will be asking for help on) excel will give me an
answer, but when I start messing with it excel will just show it like
it isn't a formula (without the = sign) but I can paste the same
formula in another cell and it will return a result. Its almost like I
corrupt the cell and I have to exit without saving for it to work
again - its killing me.

Now the problem at hand.
=IF(T25>A25,"0",T25)*VLOOKUP(F25,Dieload!G$2:AW$294,43,FALSE)

Column A  R      S      T       All the stuff in between A-R is not
referenced.

            2   100     20      2

I hope you have enough information because I don't know how to explain
what I want, I just know I need this to work. I am not excel savvy and
probably have had to create a ton of references I don't need to get
where I am at right now in my project.

Jeff

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

 

[Dave Peterson]

First, since you're multiplying, you should change the text "0" to a real number 0.

=IF(T25>A25,"0",T25)*VLOOKUP(F25,Dieload!G$2:AW$294,43,FALSE)
becomes
=IF(T25>A25,0,T25)*VLOOKUP(F25,Dieload!G$2:AW$294,43,FALSE)

Excel is very forgiving and will actually coerce the text "0" to a number when
it needs to--but it may confuse you later if you review that formula.

Second, you didn't explain what the problem was with that formula.

Third...

Saved from a previous post.

You're not doing anything wrong.

Excel likes to help.

Try this on a test worksheet.
Select A1 and hit ctrl-; (to put the date in the cell)
now select B1 and type: =a1

Notice that excel changed the format of B1 to match the format in A1.

Now format D1 as Text.
put ASDF in D1
put =D1 in E1
You see ASDF.

With E1 selected, hit the F2 key and then enter (to pretend that you're changing
the formula).

Excel has "helped" you by changing that cell's format to text.

I don't know of any way of changing this behavior.

I just select the cell, and reformat it to General (or whatever I wanted). I
hit F2 and then enter (to reenter that formula).

Sometimes this feature is nice, sometimes it ain't.

 

[CellShocked]

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

I suspect he is having a cursor key problems while editing a cell
string. One must be careful to mind one's use of cursor keys in an
attempt to move the cursor within a formula string.