Warehouse product locator suggestions..

**trainee01** · 12-07-2007, 09:46 AM

I want to create a search tool for the warehouse I work at that will find the location of products via a search in excel and will have an option for user to click on a link to an external html file with a map of the warehouse in .png format revealing the product's location on the map..

What is the best/better way of doing this?

**SamuelT** · 12-07-2007, 10:07 AM

Hi,

I'd use a validation list (more at: http://www.contextures.com/xlDataVal01.html) to find the various products and then VLOOKUP (check: http://www.ozgrid.com/Excel/excel-vlookup-formula.htm) or INDEX/MATCH (http://www.contextures.com/xlFunctions03.html) to display the other details.

HTH,

SamuelT

**trainee01** · 12-07-2007, 10:07 AM

I have discovered autoshapes and think I will use this instead to draw the map of the warehouse. But how do I get a certain shape to change color when someone has clicked the link to show where this location is?

**trainee01** · 12-07-2007, 10:26 AM

Originally Posted by SamuelT

I'd use a validation list

It will be over 700 items, I was thinking more like them typing the name and it will perform a search of matches in the spreadsheet...

Originally Posted by SamuelT

or INDEX/MATCH to display the other details.

hmm just by looking at the example not sure if this is a good idea, because it automatically fills in the data of the last empty cell right?

There are hundreds of items and say if they(employee) are holding in their hand a product that they do not know the exact name of but can identify what it is i.e. "a 2L bottle" they just type 2L bottle in search and will show all the 2L bottles search comes across for them to narrow down which one it is according to search results... then they can get the location and return it to it's place...

**SamuelT** · 12-07-2007, 11:55 AM

Hi,

Have a look at the attached file. The user first picks the category, followed by the exact product type. Excel then finds the location and gives a hyperlink to the map.

Obviously you'll have to create your own named ranges and product lists, but this approach might work. If you need any of the workings explained, let me know.

SamuelT

**trainee01** · 12-07-2007, 10:17 PM

Hi Samuel

take a look at this example. It just needs some macros to automatically fill in the correct shapes when link is clicked.

Sorry I don't know how to compress it under 100kb. I had to upload to mediafire because it's 600kb

http://www.mediafire.com/?baxs1mdmdtx

**mikerickson** · 12-07-2007, 11:37 PM

I also work out of a warehouse. I suspect that accounting department already has your inventory in a list of SKU's and descriptions. Those lists can't be changed, so they could be put on the left of a VLoopup table, with the changing shelf locations and counts to the right.

To change colors of shapes, record a macro.

**trainee01** · 12-08-2007, 05:33 AM

I don't understand how recording a macro works. You mean I have to record over 700 macros that's for each product that when a hyperlink is clicked will change color of the shapes??