A way to compare/checkoff

Posted by Eric Ellis on January 30, 2002 7:43 PM

Hello, If you have any idea how to do something like this please help!!!

Here is what I am trying to do. I have to make something for our inventory. Column b has 240 asset numbers. The rest of the columns are filles with makes models etc. Now on worksheet 2 is basically blank. I have a bar code scanner that will but the asset numbers into column b. It would make a long list (hopefully 240 assets if nothing was stolen :) )of numbers. Is there a way to make the first sheet's asset numbers look at the scanned in asset numbers from the 2nd sheet. If so how would I do it? Also then is there a way to make it check off or do something to column A on the first page (right next to the asset numbers) to show the item was scanned?

Sorry if this is confusing. i am really confused myself. I do not have much excel experience so please give the actual formula.

Thank you so very much for any help you can supply!!!!
Eric

Posted by eileen on January 30, 2002 8:22 PM

Use a vlookup within an if statement. It should look something like this. This formula is entered on the first empty column on sheet1.
=IF(ISNA(VLOOKUP($B2,Sheet2!$B$1:$B$4,1,FALSE)),"not scanned",VLOOKUP(B2,Sheet2!$B$1:$B$4,1,FALSE))

Posted by Eric Ellis on January 30, 2002 10:03 PM

I don't know if I am did it right. Let me explain my problem another way.

Sheet 1 Sheet 2
A B A
Y 12345 25471
N 23456 54545
N 34567 12345

Ok, sheet 1 column B has the actual inventory. Sheet 2 column A has the items I scanned in with the bar code reader. The 2nd sheet is not in any particular sorted order because items are being scanned as they are found. Now column A on Sheet 1 needs to look at Column A on Sheet 2 and look to see if it has the same number that is located right next to it in Column B on Sheet 1. From the example I wrote above please notice that on sheet 1 there is a Y located next to 12345 because that number is somewhere on Sheet 2 Column A. Also please note that column A on both sheets can be up to 240 rows long.
Maybe that formula you sent is right but I am not sure how to interpret it to customize it to fit my needs. I REALLY appreciate your help and anyone else that can help!
Eric

Posted by Eric Ellis on January 30, 2002 10:07 PM

Sorry for the repost. when it posted it messed up the alignment for my example.

I don't know if I used your formula right. Let me explain my problem another way.

Sheet 1
A - B
Y - 12345
N - 23456
N - 34567

Sheet 2
A
25471
54545
12345

Posted by Aladin Akyurek on January 30, 2002 10:25 PM

Apparently, you want a Y or N for each inventory item column B (in Sheet1) depending on whether it is scanned as given in A of Sheet2:

In A1 of Sheet1 enter: =IF(COUNTIF(Sheet2!A:A,B1),"Y","N")

Copy down this formula as far as needed.

=======

Posted by Eric Ellis on January 30, 2002 10:45 PM

That seems to work great!! Thank you.. Just one last question, I hope. Is there a way to fill the square (sheet 1 column A) red if the cell has a "N" in it?

Thanks again for your help!
Eric

Posted by Aladin Akyurek on January 30, 2002 11:21 PM

> Is there a way to fill the square (sheet 1 column A) red if the cell has a "N" in it?

Yes. Select all of the cells in A.
Activate Format|Conditional Formatting.
Choose "Cell value Is" & "Equal To" for Condition 1
Enter in the empty box just the letter N
Activate Format.
Choose red on the Patterns tab.
Click OK, OK.

========== Apparently, you want a Y or N for each inventory item column B (in Sheet1) depending on whether it is scanned as given in A of Sheet2: In A1 of Sheet1 enter: =IF(COUNTIF(Sheet2!A:A,B1),"Y","N") Copy down this formula as far as needed. ======= : Sorry for the repost. when it posted it messed up the alignment for my example. : I don't know if I used your formula right. Let me explain my problem another way. : Sheet 1

Posted by Eric Ellis on January 30, 2002 11:28 PM

Thank you, thank you, thank you!!! ctivate Format|Conditional Formatting.