Simple warehouse location management template

[Don Adeks]

Dear Gurus,

I need your help.

I am trying to design this simple warehouse location management template for my business, such that I can quickly check my system and know where "what" is, and how many of it is in "what" location within my warehouse.

So I attached a sample template with worksheet rack names (Rack A, Rack B, Rack C...), and each of the Racks still have sub locations 1,2,3.... This means a rack has a unique code of A1, B2, C2, etc.

The products have unique codes which makes them different from each other, so whatever formulas we are bringing in will be based on these codes.

I want a situation where when I enter a unique code in the "Lookup Page" worksheet cell B3 (Example used is CK3 colored YELLOW in the workbook), cell C3 will display the description, and column A (from A5 downwards) will display all the rack numbers where the item is not zero in quantity, while column B (from B5 downwards) will display the corresponding quantities.

The working principle is, whenever I take anything out of any of the locations, I will subtract the quantities from that specific rack so as to ensure I always have accurate data.

[avk]

My advise is data maintain data in single sheet.
I restructure data. If you comfortable. See attach file.
Lookup Page sheet "A5"

Please Login or Register  to view this content.

Ensure pressing shift+ctrl+enter
In "B5"

Please Login or Register  to view this content.

[Don Adeks]

Dear avk,

Thank you for your swift response and thoughtfulness. I like the idea of using a single data sheet, just that I have over a thousand SKUs but I believe a find/search will make it easy.

I however will like you to help me look at the possibility of getting a result as in the "Picking Page" worksheet, or you can advice something that will work best. I just want it easy for me to know where to go and pick the items when they are many.

Many thanks in advance.

[KOKOSEK]

Use this UDF (because in XL2007 you do not have TEXTJOIN):

put it into standard module in VBA:

Please Login or Register  to view this content.

then in C4 on Picking page:

Please Login or Register  to view this content.

as it is arrya formula you have to accept it with Ctrl+Shift+Enter ({} around formula should appear).


Check attached file.

[Don Adeks]

Dear KOKOSEK,

I can't seem to get this working on my real template.
I inserted the VBA code using "Alt + F11", went back and inserted the formula, referencing the names of the worksheets in my real data, but I got error message "NAME?".

Kindly assist please.

Use this UDF (because in XL2007 you do not have TEXTJOIN):

put it into standard module in VBA:

Please Login or Register  to view this content.

then in C4 on Picking page:

Please Login or Register  to view this content.

as it is arrya formula you have to accept it with Ctrl+Shift+Enter ({} around formula should appear).


Check attached file.

[KOKOSEK]

As I see you've pasted code into Sheet4 code.
Insert it into standard module. Did everything works correctly in file which I've attached in my prev. post?

[Don Adeks]

Can't get this working. Please I still need assistance with this.

[torachan]

post you actual workbook file with any confidential data de=sensitised.
we can then see your total structure instead of trying to trouble shoot bits of code possibly pasted in the wrong location.
torachan.