Good Day,
I have been given another task to try and do in Excel that is a little more complicated then before. Currently we write by had a map showing where the Categories and Items are located on about 50 shelves. We try and have our shelves sorted in Categories and Items in those categories. The number of items in each category change, so I need to find some way to make this as dynamic as possible.
I have a Sheet that contains the numbering for the Category and Item#, example 1-1 through 1-140, 2-1 through 2-146, 3-1 through 3-146, etc.
We want to make a type of map that will allow us to easily know what Shelf and Bin the items are need to pull are located. The Shelves do not have the same number of Bins on each shelf. An example of what we would like is something like this:
Bins per Shelf
Shelf 1 1-1 1-36 36
Shelf 2 1-37 1-68 32
Shelf 3 1-68 1-104 36
Shelf 4 1-105 1-140 2-1 2-33 68
Shelf 5 2-34 2-88 54
Shelf 6 2-89 2-145 56
Shelf 7 2-146 2-152 3-1 3-39 46
Shelf 8 3-40 X X 3-96 56
Shelf 9 3-97 3-155 58
Shelf 10 3-156 3-166 4-1 4-50 60
Shelf 11 4-51 4-113 62
Note: Right column is the number of BINs we have on each shelf. The X's are places we cannot have bins due to electrical, plumbing or other restriction.
Is there a way to keep the same cells structure and just change the numbers within the cells to match the Category and Items list when it changes? COUNTA would give me the total for each category but plugging those into this map would be a little more manual work.
In the above example if the number of items changes in Category 1 (140) to say 150 items then the corresponding Category 2 items on Shelf 4 would need to be reduced by 10 so it would only be 2-1 through 2-23 on Shelf 4. Shelf five would then need to start with 2-24. Then the category 2 items changes to 147 and that changes numbers in Shelf 7, etc. Example of new map with number of items changed should be something like this:
Bins per Shelf
Shelf 1 1-1 1-36 36
Shelf 2 1-37 1-68 32
Shelf 3 1-68 1-104 36
Shelf 4 1-105 1-150 2-1 2-23 68
Shelf 5 2-24 2-78 54
Shelf 6 2-79 2-135 56
Shelf 7 2-136 2-147 3-1 3-35 46
Shelf 8 3-36 X X 3-94 56
Shelf 9 3-95 3-153 58
Shelf 10 3-154 3-156 4-1 4-57 60
Shelf 11 4-58 4-120 62
Like I said I would like to keep the same cells and just change the numbers in the cells to match what we should have on our shelves and bins.
Is this even possible? If so, what kind of formula are we looking at or even creating a macro to get these updated in a lot less time then doing them by hand.
Thank you for any suggestions.
Mark
I have been given another task to try and do in Excel that is a little more complicated then before. Currently we write by had a map showing where the Categories and Items are located on about 50 shelves. We try and have our shelves sorted in Categories and Items in those categories. The number of items in each category change, so I need to find some way to make this as dynamic as possible.
I have a Sheet that contains the numbering for the Category and Item#, example 1-1 through 1-140, 2-1 through 2-146, 3-1 through 3-146, etc.
We want to make a type of map that will allow us to easily know what Shelf and Bin the items are need to pull are located. The Shelves do not have the same number of Bins on each shelf. An example of what we would like is something like this:
Bins per Shelf
Shelf 1 1-1 1-36 36
Shelf 2 1-37 1-68 32
Shelf 3 1-68 1-104 36
Shelf 4 1-105 1-140 2-1 2-33 68
Shelf 5 2-34 2-88 54
Shelf 6 2-89 2-145 56
Shelf 7 2-146 2-152 3-1 3-39 46
Shelf 8 3-40 X X 3-96 56
Shelf 9 3-97 3-155 58
Shelf 10 3-156 3-166 4-1 4-50 60
Shelf 11 4-51 4-113 62
Note: Right column is the number of BINs we have on each shelf. The X's are places we cannot have bins due to electrical, plumbing or other restriction.
Is there a way to keep the same cells structure and just change the numbers within the cells to match the Category and Items list when it changes? COUNTA would give me the total for each category but plugging those into this map would be a little more manual work.
In the above example if the number of items changes in Category 1 (140) to say 150 items then the corresponding Category 2 items on Shelf 4 would need to be reduced by 10 so it would only be 2-1 through 2-23 on Shelf 4. Shelf five would then need to start with 2-24. Then the category 2 items changes to 147 and that changes numbers in Shelf 7, etc. Example of new map with number of items changed should be something like this:
Bins per Shelf
Shelf 1 1-1 1-36 36
Shelf 2 1-37 1-68 32
Shelf 3 1-68 1-104 36
Shelf 4 1-105 1-150 2-1 2-23 68
Shelf 5 2-24 2-78 54
Shelf 6 2-79 2-135 56
Shelf 7 2-136 2-147 3-1 3-35 46
Shelf 8 3-36 X X 3-94 56
Shelf 9 3-95 3-153 58
Shelf 10 3-154 3-156 4-1 4-57 60
Shelf 11 4-58 4-120 62
Like I said I would like to keep the same cells and just change the numbers in the cells to match what we should have on our shelves and bins.
Is this even possible? If so, what kind of formula are we looking at or even creating a macro to get these updated in a lot less time then doing them by hand.
Thank you for any suggestions.
Mark
