Excel 2007 : How to deduce a cell in a range given by a lookup? (Snychronize sale and inventory)

[aspmotta]

Sorry about the confusing topic name, I'm trying to make a book Inventory that is synchronized with new sales I add.

I was able retrieve the number of available books using LOOKUP function entering the book's name, a range named 'Products', and a range named 'Inventory'.

It looks like this: =LOOKUP(A2,'BOOKSTORE.xlsx'!Products,'BOOKSTORE.xlsx'!Inventory); A2 is the book's name.

My problem is that I can't figure out how to make that number that is given to me by LOOKUP be deduced in the 'inventory' range by the quantity of books sold without manually doing it each time.

Does anyone have any idea how I could do that?

Thanks in advance!

Here is a very simple version of what I'm working on BOOKSTORE.xlsx

[benishiryo]

hi aspmotta, not sure if i got what you want. you want to make the range dynamic? changing everytime you add a new book?
in you name manager for Inventory, use this formula:
=OFFSET($B$1,1,0,COUNT($B:$B))

for Products, use this:
=OFFSET($A$1,1,0,COUNT($B:$B))