Left Lookups across multiple data 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.

 

[Domenic]

Assuming that the corresponding value in Column R is to be returned,
download and install the free add-in Morefunc.xll...

http://xcell05.free.fr/

....then try...

=INDEX(THREED('Sheet1:Sheet5'!R13:R92),MATCH(H8,THREED('Sheet1:Sheet5'!S1
3:S92),0))

Otherwise, let A2:A6 contain the sheet names, and then try the
following...

B2:

=INDEX(A2:A6,MATCH(TRUE,COUNTIF(INDIRECT("'"&A2:A6&"'!S13:S92"),H8)>0,0))

....confirmed with CONTROL+SHIFT+ENTER

C2:

=INDEX(INDIRECT("'"&B2&"'!R13:R92"),MATCH(H8,INDIRECT("'"&B2&"'!S13:S92")
,0))

....confirmed with just ENTER

Hope this helps!

 

[johnb31337]

Thank you , I will try this now, will post back the results.

 

[johnb31337]

This one didnt work, It gave me an #NA error which is closer then I got
yet, but the indirect function receives all #REF errors, when you show
calculation steps. I didnt use the Morefunc because I need this to be
user friendly to all, and I dont think that all of the users should
have to go through getting the add on installed. So I tried the
inderect formula.

This is a very complicated lookup, but it seems to me that it would be
a very common one.

 

[Domenic]

Did you confirm the formula for B2 with CONTROL+SHIFT+ENTER? Also, with
regards to the add-in, the latest version allows the THREED function to
be embedded within the file. So there's no need for others to install
it.