Formula: =VLOOKUP(F6,'PO-RAW'!C21:R2001,2,FALSE)
Cell Format: General
ToolsCalculation: Automatic (even F9 does not work)

Problem:
At first entry the formula works by returning the value I expected. Upon
entering the Formula Bar only one of the arguments will highlight regardless
of which one my cursor is in. I exit the Formula Bar, then there is no
longer a returned value in the cell, the cell only displays the formula
verbatim. I can UNDO my action back to the working state, but then again, as
soon as I touch the formula (even without editing) it fails again. If I do
choose to make an edit, like set one cell reference to an absolute cell, then
even the one argument that once was highlighted fails to highlight and the
formula effectively is dead.

For all the other VLOOKUPs in the workbook, they work perfectly fine. So it
is not the Calculation setting in the Tools menu. I made sure that cell
containing the formula is set to General. There are no spaces in the formula
(its syntax is correct and has worked previously in my session. I saved
closed rebooted the computer (in case it was memory -- workbook only 5MB)
restarted Excel reopened the file, still it remains a failed calculation.

PLEASE HELP!!!

I'm not sure I've ever seen this with an =vlookup() formula.

But I have seen it with simple =A1 formulas.

In fact, you can see it if you do this against a test worksheet.

Select A1
Hit ctrl-; (to put the date in A1)

select c4 (any other cell)
put =A1 in that cell

Excel likes to help and will change the format of C4 to a date.
Format c4 as General.
select c4, hit F2, then enter--it's back to a date.

You can do the same kind of thing if you format A1 as Text. C4 will be changed
to text the first time (and every time) you edit that formula in C4.

I don't know of a way of turning off this "helpful" feature.

==

After you edit the cell with your formula and see the formula--not the results
of the formula, if you do Format|Cells|number tab, is that cell formatted as
Text? (I'm guessing yes.)



ESAEO wrote:

Formula: =VLOOKUP(F6,'PO-RAW'!C21:R2001,2,FALSE)
Cell Format: General
ToolsCalculation: Automatic (even F9 does not work)

Problem:
At first entry the formula works by returning the value I expected. Upon
entering the Formula Bar only one of the arguments will highlight regardless
of which one my cursor is in. I exit the Formula Bar, then there is no
longer a returned value in the cell, the cell only displays the formula
verbatim. I can UNDO my action back to the working state, but then again, as
soon as I touch the formula (even without editing) it fails again. If I do
choose to make an edit, like set one cell reference to an absolute cell, then
even the one argument that once was highlighted fails to highlight and the
formula effectively is dead.

For all the other VLOOKUPs in the workbook, they work perfectly fine. So it
is not the Calculation setting in the Tools menu. I made sure that cell
containing the formula is set to General. There are no spaces in the formula
(its syntax is correct and has worked previously in my session. I saved
closed rebooted the computer (in case it was memory -- workbook only 5MB)
restarted Excel reopened the file, still it remains a failed calculation.

PLEASE HELP!!!

--

Dave Peterson