Left Lookups across multiple work sheets

[johnb31337]

Hi,

I used to consider myself a master of excel until I started
this project and now I am stumped and could really use some help. I
have a friend who needs me to create a spreadsheet that he can run his
whole business on but he doesnt have the funds to have a database
custom built. I am doing an inventory and customer tracking system not
so out of the ordinary I am sure. Anyway, I am designing it into 5
spreadsheets, warehouse, dispatch, payroll, recon, and reports and
invoicing. within the warehouse there is a master inventory sheet which
has all of the equipment that is in his warehouse. There is a worksheet
for each technician that gets equipment scanned out to him, that is
printable for each day of the week.
The Inventory is categorized by serialized or non-serialized.
so here is the deal.

these are the tables

Model Serial Number On account On Truck DOA

Below these columns is where the data will be scanned in. then the tech
page looks like this

Model Serial number.

I need a formula to put in the "On Truck" Field that will search
through all of the worksheets and find the serial number loation, and
then put the techs name in the "On Truck" Field.

I am trying to do a left lookup using the index and match functions,
Formula example:

=INDEX(DUMBUMBY!S13:S92,1,MATCH(H8, DUMBUMBY!S13:S92),-1,-1,1,1)

This formula will not work between multiple sheets though, and I am
getting very frustrating. Basically I just want the cell to find the
serial number listed in the inventory table in the worksheet and column
that it is scanned on return the techs name which is listed to the
left.

Is there a way to do this?

I have tried to be very informative in this post please let me know if
you need more information I am pulling my hair out.

 

[Murray]

Johnb

Despite your detailed description I can't answer your question. But
just as a thought, have you considered using Excel in it's "relational
database" capacity.

A good primer is on Tushar Mehta's pages at
http://www.tushar-mehta.com/excel/newsgroups/rdbms_in_excel/index.html

Combining required parts of one sheet/workbook with another then
becomes a relatively simple process.

Regards

Murray