Hi,

I have 13 digits in my barcode numbers, and I can only have their cell
format in either TEXT, or NUMBER format.

__________________________________________________ _____________

The thing is, I can't type it in cells that is in a Number format, if
I do, my description column in '13DBC'!$B10 using

=IF(A10<0,(VLOOKUP($A10,'Stock Items'!$A$3:$B$1002,2,FALSE)),"")

gives me a #N/A and does not give me my description.

But it works if I have my cell Format in TEXT format.

__________________________________________________ _____________

The problem now is, is that I want to pass the information from 13DBC
to SHORT-1, with the formula

{=IF(ROWS($1:1)<=COUNT('13DBC'!$V$10:$V$999),INDEX ('13DBC'!A$10:A$999,
SMALL(IF('13DBC'!$V$10:$V$999<"",ROW(INDIRECT("1: "&COUNT('13DBC'!$A$10:$A$999)))),ROW(1:1))),"" )}

but cant, because my cells are in text format in 13DBC column A.

__________________________________________________ _____________

In my 'Stock Item' Sheet the cell format (column A) is Number format,
and I am using this formula

=IF($A455<"",IF(ISERROR(VLOOKUP($A455,Mo!$A$1:$X$ 1000,4,FALSE)),"",VLOOKUP($A455,Mo!$A$1:$X1452,4,F ALSE)))

to extract unit prices, it works for the 3 to 8 digit barcodes
(numbers), but not for the 13 digit barcodes (numbers)

__________________________________________________ _____________

Any idea how I can fix this? Is there a cell format that I can use,
that will not trow the 13 digit numbers in a Scientific format
(2.90688E+12) and that will work with all formulas?. Something I can
use accross the board. e.g my 3 to 8 digits as well.

Regards
Antoinette


--
sonar
------------------------------------------------------------------------
sonar's Profile: http://www.excelforum.com/member.php...fo&userid=8424
View this thread: http://www.excelforum.com/showthread...hreadid=399917

Hi!

What do your barcodes look like?

1234567890123 is a 13 digit number and Excel has no problem working with 13
digit numbers.

There should be no reason to format a number such as that above as TEXT.

Can you post an example of this stuff?

Is there a cell format that I can use,
that will not trow the 13 digit numbers in a Scientific format
(2.90688E+12)

This usually only happens if the cell is not wide enough to hold the full
length of the number. May be as simple as widening the column!

Biff

"sonar" wrote in
message ...

Hi,

I have 13 digits in my barcode numbers, and I can only have their cell
format in either TEXT, or NUMBER format.

__________________________________________________ _____________

The thing is, I can't type it in cells that is in a Number format, if
I do, my description column in '13DBC'!$B10 using

=IF(A10<0,(VLOOKUP($A10,'Stock Items'!$A$3:$B$1002,2,FALSE)),"")

gives me a #N/A and does not give me my description.

But it works if I have my cell Format in TEXT format.

__________________________________________________ _____________

The problem now is, is that I want to pass the information from 13DBC
to SHORT-1, with the formula

{=IF(ROWS($1:1)<=COUNT('13DBC'!$V$10:$V$999),INDEX ('13DBC'!A$10:A$999,
SMALL(IF('13DBC'!$V$10:$V$999<"",ROW(INDIRECT("1: "&COUNT('13DBC'!$A$10:$A$999)))),ROW(1:1))),"" )}

but cant, because my cells are in text format in 13DBC column A.

__________________________________________________ _____________

In my 'Stock Item' Sheet the cell format (column A) is Number format,
and I am using this formula

=IF($A455<"",IF(ISERROR(VLOOKUP($A455,Mo!$A$1:$X$ 1000,4,FALSE)),"",VLOOKUP($A455,Mo!$A$1:$X1452,4,F ALSE)))

to extract unit prices, it works for the 3 to 8 digit barcodes
(numbers), but not for the 13 digit barcodes (numbers)

__________________________________________________ _____________

Any idea how I can fix this? Is there a cell format that I can use,
that =IF(A10<0,(VLOOKUP($A10,'Stock Items'!$A$3:$B$1002,2,FALSE)),"")
and that will work with all formulas?. Something I can
use accross the board. e.g my 3 to 8 digits as well.

Regards
Antoinette


--
sonar
------------------------------------------------------------------------
sonar's Profile:
http://www.excelforum.com/member.php...fo&userid=8424
View this thread: http://www.excelforum.com/showthread...hreadid=399917

Hi Biff

Id love to post you the file, but the file is too big. Play around
with General, Numbers, and TEXT as cell formats, and put in 13 digit
numbers, and see the reaction.

Regards


--
sonar
------------------------------------------------------------------------
sonar's Profile: http://www.excelforum.com/member.php...fo&userid=8424
View this thread: http://www.excelforum.com/showthread...hreadid=399917

I believe that for the VLookup function to work properly both fields that the
lookup is working off of must be the same data type (both text, both #, etc).
Otherwise the VLookup function returns #N/A. Perhaps this is your issue.
Ensure that the data types are the same. The formats could be the same but
the data types can still be different. You can use the TYPE function to
determine the data type of your data.

Hope this helps.

Thanks,
Bill Horton

"sonar" wrote:


Hi,

I have 13 digits in my barcode numbers, and I can only have their cell
format in either TEXT, or NUMBER format.

__________________________________________________ _____________

The thing is, I can't type it in cells that is in a Number format, if
I do, my description column in '13DBC'!$B10 using

=IF(A10<0,(VLOOKUP($A10,'Stock Items'!$A$3:$B$1002,2,FALSE)),"")

gives me a #N/A and does not give me my description.

But it works if I have my cell Format in TEXT format.

__________________________________________________ _____________

The problem now is, is that I want to pass the information from 13DBC
to SHORT-1, with the formula

{=IF(ROWS($1:1)<=COUNT('13DBC'!$V$10:$V$999),INDEX ('13DBC'!A$10:A$999,
SMALL(IF('13DBC'!$V$10:$V$999<"",ROW(INDIRECT("1: "&COUNT('13DBC'!$A$10:$A$999)))),ROW(1:1))),"" )}

but cant, because my cells are in text format in 13DBC column A.

__________________________________________________ _____________

In my 'Stock Item' Sheet the cell format (column A) is Number format,
and I am using this formula

=IF($A455<"",IF(ISERROR(VLOOKUP($A455,Mo!$A$1:$X$ 1000,4,FALSE)),"",VLOOKUP($A455,Mo!$A$1:$X1452,4,F ALSE)))

to extract unit prices, it works for the 3 to 8 digit barcodes
(numbers), but not for the 13 digit barcodes (numbers)

__________________________________________________ _____________

Any idea how I can fix this? Is there a cell format that I can use,
that will not trow the 13 digit numbers in a Scientific format
(2.90688E+12) and that will work with all formulas?. Something I can
use accross the board. e.g my 3 to 8 digits as well.

Regards
Antoinette


--
sonar
------------------------------------------------------------------------
sonar's Profile: http://www.excelforum.com/member.php...fo&userid=8424
View this thread: http://www.excelforum.com/showthread...hreadid=399917

Hi Biff

I have downsized the file to include just the 13 digit barcodes.

Please have a look at it in comparison to what I have explained. Maybe
you will see something I dont know.

Regards

NB. Not to worry about viruses, my hubby is in IT, and our server is
here at home. We have an active virus program that runs and updates
24/7.

http://www.epping.co.za/Book4.zip


--
sonar
------------------------------------------------------------------------
sonar's Profile: http://www.excelforum.com/member.php...fo&userid=8424
View this thread: http://www.excelforum.com/showthread...hreadid=399917