Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Warehouse map in Excel Query
Dear All 1) I've used an excel spreadsheet to map out one of our warehouses. If I say that my example warehouse has 3851 shelves (555 are empty) one cell per client/shelf and I have 15 clients in total - I'd like t be able to (when data is delivered) add the name of a client or indee new clients name and the background color of a cell automaticall colours itself with its respective colour. Also when data is remove from a shelf and is now available to be filled by a new or existin client, when I remove the text the cell resorts back to having n background colour. 2) Ideally I'd love to attach the example spreadsheet - any way o doing this or would that be outside the forum? If this procedure is complicated I'll drop it & just fill in manuall as I'm doing to date. I think I've searched through all your forums! Many thanks harperspac -- Harperspac ----------------------------------------------------------------------- Harperspace's Profile: http://www.excelforum.com/member.php...fo&userid=3260 View this thread: http://www.excelforum.com/showthread.php?threadid=52410 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Warehouse map in Excel Query
If you need 15 different colors (one for each client), you will need vba to
do it. If you only need 2 colors (empty, not-empty), then use conditional formatting - it requires no vba) If you need many colors you could do something like this: 'assumes data rows start at 3 and client column is A LastRow = Cells(Rows.Count, 1).End(xlUp).Row for i = 3 to LastRow DO CASE CASE Cells(i,1).value = "Client 1" cells(i,1).select With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 5 End With CASE Cells(i,1).value = "Client 2" Same code, different values CASE Cells(i,1).value = "Client 3" etc End Case next i Hope this helps Glen "Harperspace" wrote in message ... Dear All 1) I've used an excel spreadsheet to map out one of our warehouses. If I say that my example warehouse has 3851 shelves (555 are empty) - one cell per client/shelf and I have 15 clients in total - I'd like to be able to (when data is delivered) add the name of a client or indeed new clients name and the background color of a cell automatically colours itself with its respective colour. Also when data is removed from a shelf and is now available to be filled by a new or existing client, when I remove the text the cell resorts back to having no background colour. 2) Ideally I'd love to attach the example spreadsheet - any way of doing this or would that be outside the forum? If this procedure is complicated I'll drop it & just fill in manually as I'm doing to date. I think I've searched through all your forums! Many thanks harperspace -- Harperspace ------------------------------------------------------------------------ Harperspace's Profile: http://www.excelforum.com/member.php...o&userid=32608 View this thread: http://www.excelforum.com/showthread...hreadid=524101 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Warehouse map in Excel Query
Hard to give any detailed answer without knowing more. The main question is
how you decide which shelf(cell) to use when you have a new delivery. Also, how do you plan on assigning colors for new clients - would this be a random choice? My approach would be: 1) Have a list of your clients on a separate worksheet tab along with any other info needed, and also use a cell in this list set to the proper color that you can use both as a color key to your warehouse map and as a cell that can be copied/paste format in order to apply that client's color to the cells in the map. 2) Build a userform for entering deliveries/pick ups. You could use a combobox linked to your client list for selection of the client (along with an "add new" option). 3) Once the userform is filled in, use a command button to run code that applies whatever logic you use to decide which shelf/cell gets filled (or emptied). If filled, copy the color-formatted cell from your client list and paste it in the map to create the proper color. If removed, reset the cell to the default format, e.g. MapCell.Interior.ColorIndex = xlColorIndexNone. This just sketches out an approach to doing what you describe; the individual elements (list format, userform, combobox, etc) you can find other examples in this newsgroup that would help you put them together. The only part that would be particular to your need is the logic behind the code that determines how to find the proper cell in your map; only you know how you are doing this and without understanding your procedure I can't tell you how to write code that would do it for you. -- - K Dales "Harperspace" wrote: Dear All 1) I've used an excel spreadsheet to map out one of our warehouses. If I say that my example warehouse has 3851 shelves (555 are empty) - one cell per client/shelf and I have 15 clients in total - I'd like to be able to (when data is delivered) add the name of a client or indeed new clients name and the background color of a cell automatically colours itself with its respective colour. Also when data is removed from a shelf and is now available to be filled by a new or existing client, when I remove the text the cell resorts back to having no background colour. 2) Ideally I'd love to attach the example spreadsheet - any way of doing this or would that be outside the forum? If this procedure is complicated I'll drop it & just fill in manually as I'm doing to date. I think I've searched through all your forums! Many thanks harperspace -- Harperspace ------------------------------------------------------------------------ Harperspace's Profile: http://www.excelforum.com/member.php...o&userid=32608 View this thread: http://www.excelforum.com/showthread...hreadid=524101 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Warehouse utilization Template | Excel Discussion (Misc queries) | |||
Warehouse load sheet - Help!! | Excel Discussion (Misc queries) | |||
I am looking for a warehouse inventory control excel format | Excel Discussion (Misc queries) | |||
Create a layout for a warehouse | Excel Discussion (Misc queries) | |||
Creating a Multiple Warehouse Inventory Spreadsheets | Charts and Charting in Excel |