formating problem, PLEASE help.

G

guy12345

Hello. For last 2 days I've been encountering a problem I have never seen
before. When I'm writing a function, for example a Vlookup. I get a N/A error
from one of the function arguements as it relates to the column I'm
refrencing. The column is formatted as a number however when I re-enter the
number the error goes away. This would be fine except I work with a lot of
records and it is very time consuming to keep re-entering all the numbers in
the column when their already there. Does anyone know what might be causing
this or how to fix it. I would really appricate any help you could give.
Thanks in advance.
-Ian
 
F

Franz Verga

guy12345 said:
Hello. For last 2 days I've been encountering a problem I have never
seen before. When I'm writing a function, for example a Vlookup. I
get a N/A error from one of the function arguements as it relates to
the column I'm refrencing. The column is formatted as a number
however when I re-enter the number the error goes away. This would be
fine except I work with a lot of records and it is very time
consuming to keep re-entering all the numbers in the column when
their already there. Does anyone know what might be causing this or
how to fix it. I would really appricate any help you could give.
Thanks in advance. -Ian
Click to expand...

Hi Ian,

I think you could have your calculation set to manual.

Set it to automatic: menu Tools, Option, tab Calculation set the radio
button Automatic;


--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
D

Dave F

Is your workbook set to calculate automatically?

Go to Tools-->options, and check to make sure automatic calculation is
selected.
 
C

CLR

Is the column "really" formatted as numbers?...... From the sound of things,
it's probably still formatted as TEXT, even tho it appears to be formatted as
numbers......sometimes it's really hard to get the formatting
converted......and especially if the column is imported from outside, it
could be reformatting itself as TEXT each time it's refreshed........maybe
you could just reformat your vlookup cell as TEXT.

Vaya con Dios,
Chuck, CABGx3
 
D

Dave Peterson

Changing the format of a cell (or column of cells) doesn't change the value
within each cell.

That's why things worked after you reentered the value in the cell.

One way to convert lots of these text numbers to number numbers is to:
select an empty cell
edit|copy
select your range of offending cells
edit|Paste special|check add
 
G

Gord Dibben

Sounds like your number are not numbers, but text.

Re-format all to General then............

Copy an empty cell and select the column in question and Edit>Paste
Special>Add>OK>Esc


Gord Dibben MS Excel MVP
 
Top