MrExcel Publishing
Your One Stop for Excel Tips & Solutions

linking a book to recognize inventory


Posted by n2aqua on November 20, 2000 6:27 PM

I need to show a "check off" system for inventory. When I list numbers as inventory, I need to link another book to mark a cell when it gets invoiced for ex: book1;cell A1 has 2001 in it when I invoice and in book2;cell A23, 2001 gets used and written to another book I want to have book1 cell B1 to mark to show it has been used. Can Excel recognize a number in another book and when it gets used mark it to the book it was first listed?


Posted by Ben O. on November 21, 2000 7:19 AM

I think I understand what you're trying to do. If you enter this formula in B1 in Book1, it will look in Book2 for the value in Book1 A1. It'll look in Book2 A1:A500, but you can change this if you want it to look in a different range:

=VLOOKUP(A1,[Book2]Sheet1!$A$1:$A$500,1,FALSE)

If it finds the value in A1, it'll return that value. If you want it to return "Used" or "X", use a formula like this:

=IF(VLOOKUP(A1,[Book2]Sheet1!$A$1:$A$500,1,FALSE)<>"","Used","Not Used")

I hope this helps,

-Ben