Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 23
Default Zero on the left !!!

I have cells with numbers like this, 012345678 or 001234567, I formatted all
cells with this function =text(cell,"000000000") so that the zeros are not
cancelled,which is good,my problem was this:
a1=012345678 ,b1=012345678, c1=Shop Stock.
=if(a1=b1,"yes","no") the result was yes.
=if(isna(vlookup(a1,b:c,2,0)),"????",vlookup(a1,b: c,2,0)) the result was
???? and it should be Shop Stock !!!!!!
Could someone help,
Thx
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Zero on the left !!!

Try something like this,
with the lookup values in col A formatted in the same way:
=VLOOKUP(TEXT(A1,"000000000"),B:C,2,0)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik
---
"MAANI" wrote:
I have cells with numbers like this, 012345678 or 001234567, I formatted all
cells with this function =text(cell,"000000000") so that the zeros are not
cancelled,which is good,my problem was this:
a1=012345678 ,b1=012345678, c1=Shop Stock.
=if(a1=b1,"yes","no") the result was yes.
=if(isna(vlookup(a1,b:c,2,0)),"????",vlookup(a1,b: c,2,0)) the result was
???? and it should be Shop Stock !!!!!!

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 23
Default Zero on the left !!!

Max,the column that has the numbers is pasted special as values,so your
suggestion didn't work.

"Max" wrote:

Try something like this,
with the lookup values in col A formatted in the same way:
=VLOOKUP(TEXT(A1,"000000000"),B:C,2,0)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik
---
"MAANI" wrote:
I have cells with numbers like this, 012345678 or 001234567, I formatted all
cells with this function =text(cell,"000000000") so that the zeros are not
cancelled,which is good,my problem was this:
a1=012345678 ,b1=012345678, c1=Shop Stock.
=if(a1=b1,"yes","no") the result was yes.
=if(isna(vlookup(a1,b:c,2,0)),"????",vlookup(a1,b: c,2,0)) the result was
???? and it should be Shop Stock !!!!!!

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Zero on the left !!!

Upload & post a direct link here to your sample* which doesn't work
http://freefilehosting.net/
*desensitized
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---
"MAANI" wrote in message
...
Max,the column that has the numbers is pasted special as values,so your
suggestion didn't work.



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 23
Default Zero on the left !!!

Here is the link http://freefilehosting.net/download/415b3.
Notice column G in Shop Stock sheet is not working

"Max" wrote:

Upload & post a direct link here to your sample* which doesn't work
http://freefilehosting.net/
*desensitized
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---
"MAANI" wrote in message
...
Max,the column that has the numbers is pasted special as values,so your
suggestion didn't work.






  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Zero on the left !!!

Notice column G in Shop Stock sheet is not working ..

In Shop Stock,
what you had in G3, array-entered*, copied down:
=IF(ISNA(VLOOKUP($B3,'Local Purchase
$'!$A:$F,3,FALSE)),"",VLOOKUP($B3,'Local Purchase $'!$A:$F,3,FALSE))
*unnecessarily array-entered

Try this correction
In G3, normal ENTER:
=IF(ISNA(MATCH($B3,'Local Purchase $'!$B:$B,0)),"",VLOOKUP($B3,'Local
Purchase $'!$B:$F,2,0))
Copy G3 down

You were matching the lookup value in B3 to the wrong lookup col in the
vlook's table array in: Local Purchase $, ie to col A, instead of to col B.
So of course, you'd get no results.

The correction essentially covers this key error, and suggests the use of
MATCH in the front ISNA error trap & normal ENTER for faster recalc, and it
uses the shorter zero instead of FALSE to specify exact matching

Believe that should do it ok for you
Btw, do take a moment to press the Yes button below, won't you?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 23
Default Zero on the left !!!

Thanks MAX that helped alot. I did press the YES button.

"Max" wrote:

Notice column G in Shop Stock sheet is not working ..


In Shop Stock,
what you had in G3, array-entered*, copied down:
=IF(ISNA(VLOOKUP($B3,'Local Purchase
$'!$A:$F,3,FALSE)),"",VLOOKUP($B3,'Local Purchase $'!$A:$F,3,FALSE))
*unnecessarily array-entered

Try this correction
In G3, normal ENTER:
=IF(ISNA(MATCH($B3,'Local Purchase $'!$B:$B,0)),"",VLOOKUP($B3,'Local
Purchase $'!$B:$F,2,0))
Copy G3 down

You were matching the lookup value in B3 to the wrong lookup col in the
vlook's table array in: Local Purchase $, ie to col A, instead of to col B.
So of course, you'd get no results.

The correction essentially covers this key error, and suggests the use of
MATCH in the front ISNA error trap & normal ENTER for faster recalc, and it
uses the shorter zero instead of FALSE to specify exact matching

Believe that should do it ok for you
Btw, do take a moment to press the Yes button below, won't you?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Zero on the left !!!

Welcome, thanks for feeding back & for the response rating.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500, Files:362, Subscribers:62
xdemechanik
---
"MAANI" wrote in message
...
Thanks MAX that helped a lot. I did press the YES button.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to make left side stay still and right side can move left to r AAS Excel Discussion (Misc queries) 1 May 27th 08 09:50 PM
My Excel view is Right to Left instead of Left to Right !!! Akash Puri Excel Discussion (Misc queries) 2 May 5th 08 07:15 PM
worksheet is right-to-left but i want left-to-right...? rose_mina New Users to Excel 1 April 25th 07 12:40 PM
Column labels run right to left, not left to right tmassey Excel Discussion (Misc queries) 1 November 10th 06 12:03 PM
How to change the right-to-left worksheet to left-to-right workshe RAMA Excel Discussion (Misc queries) 1 July 4th 05 01:57 PM


All times are GMT +1. The time now is 01:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"