Formula for labels!

[Michael Clavien]

Hi All
I need to label a small warehouse .

I think Excel could generate labels describing location of various shelves
in such a row and such a bay, but I am not sure how

I need to label such as a prefix plus an aisle a bay a row and then a box
on a shelf . The numbers are not critical

What I was thinking of as an example is DX-Aisle-Bay01-Row1-BoxA

There are about 9 aisle,6 bay per aisle, 6 row per bay and 3 box per row so
it would be quite a job to do manually.

Anyone can help
Thanks
Michael

 

[Guest]

Hi Michael,

Suggested is as follows:
1) In cell A1 type "Labels"
2) In cell B1 type "Number of Aisles"
3) In cell C1 type "Bays per Aisle"
4) In cell D1 type "Rows per Bay"
5) In cell E1 type "Boxes per Row"
6) In cell A2 paste the following formula. Correct for wordwrap:-
=IF(INT((ROW() - 2)/($C$2*$D$2*$E$2)) > $B$2-1, "",
CHAR(64+1+INT((ROW()-2)/($C$2*$D$2*$E$2))) & " Aisle - " & " Bay - " & 1+
MOD(INT((ROW() - 2)/($D$2*$E$2)), $C$2) & " Row - " & 1+ MOD(INT((ROW() -
2)/$E$2),$D$2) &" Box - " & CHAR(64+ 1+ MOD((ROW() - 2),$E$2)))

Note that the formula was 'quick and dirty'. Hope it's OK. I didn't know how
you wanted to label the aisles. This will label them A, B, C etc. and will do
the same for the boxes. The bays and rows will be numbered. You will likely
want to adjust the formula to suit. Seems to work for me.

Regards,
Greg

 

[Guest]

I forgot to mention to drag the formula down for the maximum possible number
of labels required.

Regards,
Greg

 

[Guest]

On follow-up read of my post I see I forgot to mention you need to enter the
desired number of Aisles, Bays, Rows and Boxes in cells B2 through E2. My
first attempt to post failed and on repost I thought I had mentioned it.
Sorry.

Regards,
Greg