Clueless, inventory quick search

[Clueless401]

i started using excel at my current new employment. I got to say that i am quickly become a little obsessed in learning more!

Right now i am creating an inventory list with shelves named by alphabet and number of shelves. ex: A1,A2,A3 and so on.

Every letter has its own tab, containing the part ID number and QTY.

Is there a way that i can search all tabs by part ID for quick access?

Would appriciate the help, i have been trying to learn as much as possible through youtube and i found this site through a pod cast.

 

[kweaver]

Welcome to MrExcel.

If you illustrate with XL2BB, actual data could be used in an example rather than trying to recreate your Part IDs.

I put the name of the sheets in column D and created a name range called MySheets.
Then, in A2, I put the Part ID I'm trying to find.
In B2 I put this array formula (CTRL+Shift+Enter) which unfortunately uses a volatile function called INDIRECT.
But, it seems to work. It'll be slow if there are lots of sheets and rows.

This formula returns the 3rd column (Quantity) where the 2nd column (Part ID) matches A2.

=VLOOKUP($A2, INDIRECT("'"&INDEX(MySheets, MATCH(1, --(COUNTIF(INDIRECT("'"& MySheets&"'!$B$1:$B$10"), $A2)>0), 0)) &"'!$B$1:$C$10"), 2, FALSE)

 

[Toadstool]

Hi Clueless401,

I'd actually change the design and have one data sheet with all locations.

A problem with multiple sheets is that only a few functions work across 3D: Microsoft 3D functions

You don't say what you want to do but here's an example Data and Main. The Main shows how to jump to the first Location for a selected letter, to count how many there are of that letter and to list all Locations starting with that letter.

Clueless401.xlsx
	A	B
1	-Location-	Value
2	A2	2
3	A4	2
4	A6	1
5
6	C1	2
7	C6	22
8	C8	12
9	X3	9
10	X4	18
Data

Clueless401.xlsx
	A	B	C
1	Jump to:	C	Location C
2
3	Count:	3
4
5	List:	Location	Value
6		C1	2
7		C6	22
8		C8	12
9
10
Main

Cell Formulas
Range	Formula
C1	C1	=IF(ISNA(MATCH(B1&"*",Data!A:A,0)),"No Location "&B1,HYPERLINK("#"&ADDRESS(MATCH(B1&"*",Data!A:A,0),1,,,"Data"),"Location "&B1))
B3	B3	=COUNTIF(Data!A:A,B1&"*")
B6:C10	B6	=IF(ROW()-ROW(B$5)>$B$3,"",INDEX(Data!A$2:A$10000,AGGREGATE(15,6,ROW(Data!$A$2:$A$10000)-ROW(Data!$A$1)/(LEFT(Data!$A$2:$A$10000,1)=$B$1),ROW()-ROW(B$5))))

Cells with Data Validation
Cell	Allow	Criteria
B1	List	A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z

 

[kweaver]

Toadstool's suggest is VERY smart.

 

[Toadstool]

Toadstool's suggest is VERY smart.

Thank you!
Now I'm blushing...

 

[Clueless401]

Hi Clueless401,

I'd actually change the design and have one data sheet with all locations.

A problem with multiple sheets is that only a few functions work across 3D: Microsoft 3D functions

You don't say what you want to do but here's an example Data and Main. The Main shows how to jump to the first Location for a selected letter, to count how many there are of that letter and to list all Locations starting with that letter.

Clueless401.xlsx
	A	B
1	-Location-	Value
2	A2	2
3	A4	2
4	A6	1
5
6	C1	2
7	C6	22
8	C8	12
9	X3	9
10	X4	18
Data

Clueless401.xlsx
	A	B	C
1	Jump to:	C	Location C
2
3	Count:	3
4
5	List:	Location	Value
6		C1	2
7		C6	22
8		C8	12
9
10
Main

Cell Formulas
Range	Formula
C1	C1	=IF(ISNA(MATCH(B1&"*",Data!A:A,0)),"No Location "&B1,HYPERLINK("#"&ADDRESS(MATCH(B1&"*",Data!A:A,0),1,,,"Data"),"Location "&B1))
B3	B3	=COUNTIF(Data!A:A,B1&"*")
B6:C10	B6	=IF(ROW()-ROW(B$5)>$B$3,"",INDEX(Data!A$2:A$10000,AGGREGATE(15,6,ROW(Data!$A$2:$A$10000)-ROW(Data!$A$1)/(LEFT(Data!$A$2:$A$10000,1)=$B$1),ROW()-ROW(B$5))))

Cells with Data Validation
Cell	Allow	Criteria
B1	List	A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z

Thank you, im realizing that im in trouble. IDK anything! i will try and implement this.

 

[Toadstool]

Thank you, im realizing that im in trouble. IDK anything! i will try and implement this.

You're welcome!

I'm pleased to see your key fields begin with an alpha so you'll avoid the old challenge of trying to match alpha and numeric... and I hope you can see how easy it would be to modify the List formula to give all Locations for a Product, even across Location letters, if they're all on one sheet.

Before you get much further I really would make a list of the functions you want to perform and what data you'll need to drive them as an hour on design will save 10+ hours on making changes later.

e.g. if you need a FIFO system as your Products have a shelf life then you'll need date into stock (maybe from an Orders sheet?). If your Locations have size/weight limits (such as racks) then maybe you also need a Product sheet to retrieve dimensions/weight and some kind of Location specifications for size/weight limits.