format value automatically changed

[nguyenthehoat]

Dear my indeed friends,
Thank you very much for prompt response on my problem.
The problem can be described as bellows:
In the source file data:
Code/Item/Name
18403127/CAVS-0.5-B /Wire 09123424/CAVSAS-0.3*1-W/Wire
B0123542/CAVSST-0.85-R/ Wire
I use some sheets to calculate import, export Q'ty, amount.... In these sheet I type only "code" it will also appear "Item" and "Name" by vlookup formula. After that I use "Sumif, If, Dcount,....." for my own purpose. But some time the value in sheet automatically changed to different types (in error checking notice "The number in this cell is formatted as text or preceded by an apostrophe") It means the "Code" between sheets can not be matched each other and my formulas can not work correctly.
I don't understand why values were automatically changed to different format ?
Many computers in my company use Windows, Office XP also met this problem?
How can I convert to the value as I typed ?
Please help me.
Tks&brgds,
The Hoat / Yazaki, Haiphong,Vietnam


_________________
Edited by The Hoat / Yazaki Haiphong, Vietnam.

[ This Message was edited by: nguyenthehoat on 2002-05-06 19:53 ]

[Chris Davison]

On 2002-05-06 19:49, nguyenthehoat wrote:
Dear my indeed friends,
Thank you very much for prompt response on my problem.
The problem can be described as bellows:
In the source file data:
Code/Item/Name
18403127/CAVS-0.5-B /Wire 09123424/CAVSAS-0.3*1-W/Wire
B0123542/CAVSST-0.85-R/ Wire
I use some sheets to calculate import, export Q'ty, amount.... In these sheet I type only "code" it will also appear "Item" and "Name" by vlookup formula. After that I use "Sumif, If, Dcount,....." for my own purpose. But some time the value in sheet automatically changed to different types (in error checking notice "The number in this cell is formatted as text or preceded by an apostrophe") It means the "Code" between sheets can not be matched each other and my formulas can not work correctly.
I don't understand why values were automatically changed to different format ?
Many computers in my company use Windows, Office XP also met this problem?
How can I convert to the value as I typed ?
Please help me.
Tks&brgds,
The Hoat / Yazaki, Haiphong,Vietnam


_________________
Edited by The Hoat / Yazaki Haiphong, Vietnam.

Do you mean how can you always have 18403127 as a number in your VLOOKUP ?

try :

=VLOOKUP(A1*1, etc etc etc etc

where A1 is 18403127







[ This Message was edited by: nguyenthehoat on 2002-05-06 19:53 ]

[nguyenthehoat]

Dear Chris Davison,
Tks for yr help.
I mean vlookup value format and value format in table ray offen automatically change so it can not match each other and formulas can not work.
I want to convert to the format as i typed.
Tks&brgds,
The. Hoat

[ This Message was edited by: nguyenthehoat on 2002-05-08 02:09 ]

[nguyenthehoat]

Dear Chris Davison,
Tks for yr help.
I mean vlookup value format and value format in table ray offen automatically change so it can not match each other and formulas can not work.
I want to convert to the format as i typed.
Tks&brgds,
The. Hoat

[Chris Davison]

I think we nearly understand each other



can I see your spreadhseet, if it's not too private

maybe highlight your problem cells in red also?

novulari@hotmail.com

thanks

[Chris Davison]

spreadsheet received

hmmmmm (!)

Formula is as follows :
=VLOOKUP(C388,'1'!$B$2:$C$1556,2,0)
and error value is #N/A

lookup value in c388 :
18403746

=ISNUMBER returns FALSE
=ISTEXT returns TRUE


value in column B on Sheet "1" in the lookup range :
18403746

=ISNUMBER returns FALSE
(however, I can easily show 5 decimal places by clicking the "increase decimal places" button)
=ISTEXT returns TRUE



when I also test them against each other using :
=C388='1'!B68 it returns FALSE

any ideas anyone ?

(spreadhseet is quite big with lots of VLOOKUPS...dunno if a memory problem would cause this though)

ta
Chris

[Chris Davison]

The Hoat,

in the meantime, try :

=VLOOKUP(C388*1,'1'!$B$2:$C$1556,2,0)

in JP IMP Cell D388

and :=VLOOKUP(C388*1,'1'!$B$2:$E$914,4,0)

in JP IMP Cell E388

and copy up and down.....

[Yogi Anand]

(spreadhseet is quite big with lots of VLOOKUPS...dunno if a memory problem would cause this though)

ta
Chris

Hi The. Hoat and Chris:
Chris, how large is the file? I wouldn't mind looking at it ... hopefully when we all put our heads together, hopefully we can either resolve the problem or come up with a practical solution.

Regards!

[nguyenthehoat]

Hi Chris Davison,

Thanks for your help.
My file is about 15 MB
My vlookup values are kar897, 0034, 1241, so 0034, 1241 need changing to text.
After changing table_ray format value to text ( = E2&"") and use the formula

=VLOOKUP(IF(ISNUMBER(MATCH(A1,E2:E2000,0)),A1,A1&""),E2:J2000,2,0)

If Vlookup value is text or number, formula will still return true.
I do not have to input data again. How excellent is !!

Kind brgds,
The Hoat