Problem: VLookup formula stops working midway through, then starts again after 40 lines. Every column has a formula top to bottom, every cell type is the same, and there are no blank spaces before or after any cell text.
Scope: My worksheet contains data for my warehouse layout. The first column is the Aisle and the 2nd column is the rack location within the aisle. In order to keep tabs on where product belongs in the warehouse, all product is assigned to various aisles and rack locations. I have a user input table that I want to use to populate an "Assignment" column - in other words, the user will enter an aisle number and start and stop numbers for rack locations, which will then populate this column.
IE, if a user assigns my customer, American Sugar Refining to to Aisle GG3, they then select the START and STOP (first and last) locations in the aisle. However (plot twist!), to make it easier for my users, once an aisle is selected (from a pre-set named drop down list) the START and STOP entry cells are dynamic drop-down lists based on which aisle is selected.
That said, let's say, per my example below, that a user assigns my customer American Suger Refining to aisle GG3, slots 1A through 57D (letters designate the rack level), I need that assigned customer name to appear in the column associated with it's rack location on my other sheet.
I have no earthly idea why what I have so far is only working for part of my workbook but it's incredibly annoying and I would absolutely love some help.
This is my entry form:
This is effectively my background data:
Scope: My worksheet contains data for my warehouse layout. The first column is the Aisle and the 2nd column is the rack location within the aisle. In order to keep tabs on where product belongs in the warehouse, all product is assigned to various aisles and rack locations. I have a user input table that I want to use to populate an "Assignment" column - in other words, the user will enter an aisle number and start and stop numbers for rack locations, which will then populate this column.
IE, if a user assigns my customer, American Sugar Refining to to Aisle GG3, they then select the START and STOP (first and last) locations in the aisle. However (plot twist!), to make it easier for my users, once an aisle is selected (from a pre-set named drop down list) the START and STOP entry cells are dynamic drop-down lists based on which aisle is selected.
That said, let's say, per my example below, that a user assigns my customer American Suger Refining to aisle GG3, slots 1A through 57D (letters designate the rack level), I need that assigned customer name to appear in the column associated with it's rack location on my other sheet.
I have no earthly idea why what I have so far is only working for part of my workbook but it's incredibly annoying and I would absolutely love some help.
This is my entry form:
BUILDING 1 - STORAGE.xlsm | ||||||||
---|---|---|---|---|---|---|---|---|
E | F | G | H | I | J | |||
3 | ACCOUNT | AISLE | AISLE.TMP | START | STOP | LEVELS | ||
4 | AMERICAN SUGAR REFINING INC | Aisle_GG3 | GG3 | GG3_1A | GG3_57D | ALL | ||
5 | AMERICAN SUGAR REFINING INC | Aisle_GG4 | GG4 | GG4_1A | GG4_40D | EXCLUDE D | ||
ASSIGNMENTS |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G4:G5 | G4 | =RIGHT([@AISLE],LEN([@AISLE])-SEARCH("_",[@AISLE])) |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
F4:F5 | List | =Table_AisleList |
H4:H5 | List | =INDIRECT(F4) |
I4:I5 | List | =INDIRECT(F4) |
J4:J5 | List | =AISLES!$C$2:$C$4 |
This is effectively my background data:
BUILDING 1 - STORAGE.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
5620 | GG3 | GG3_8C | RACK | 8 | C | 1 | AMERICAN SUGAR REFINING INC | ||
5621 | GG3 | GG3_8D | RACK | 8 | D | 1 | AMERICAN SUGAR REFINING INC | ||
5622 | GG3 | GG3_9A | RACK | 9 | A | 1 | AMERICAN SUGAR REFINING INC | ||
5623 | GG3 | GG3_9B | RACK | 9 | B | 1 | AMERICAN SUGAR REFINING INC | ||
5624 | GG3 | GG3_9C | RACK | 9 | C | 1 | AMERICAN SUGAR REFINING INC | ||
5625 | GG3 | GG3_9D | RACK | 9 | D | 1 | AMERICAN SUGAR REFINING INC | ||
5626 | GG3 | GG3_10A | RACK | 10 | A | 1 | #N/A | ||
5627 | GG3 | GG3_10B | RACK | 10 | B | 1 | #N/A | ||
5628 | GG3 | GG3_10C | RACK | 10 | C | 1 | #N/A | ||
5629 | GG3 | GG3_10D | RACK | 10 | D | 1 | #N/A | ||
5630 | GG3 | GG3_11A | RACK | 11 | A | 1 | #N/A | ||
5631 | GG3 | GG3_11B | RACK | 11 | B | 1 | #N/A | ||
5632 | GG3 | GG3_11C | RACK | 11 | C | 1 | #N/A | ||
5633 | GG3 | GG3_11D | RACK | 11 | D | 1 | #N/A | ||
5634 | GG3 | GG3_12A | RACK | 12 | A | 1 | #N/A | ||
5635 | GG3 | GG3_12B | RACK | 12 | B | 1 | #N/A | ||
5636 | GG3 | GG3_12C | RACK | 12 | C | 1 | #N/A | ||
5637 | GG3 | GG3_12D | RACK | 12 | D | 1 | #N/A | ||
5638 | GG3 | GG3_13A | RACK | 13 | A | 1 | #N/A | ||
5639 | GG3 | GG3_13B | RACK | 13 | B | 1 | #N/A | ||
5640 | GG3 | GG3_13C | RACK | 13 | C | 1 | #N/A | ||
5641 | GG3 | GG3_13D | RACK | 13 | D | 1 | #N/A | ||
5642 | GG3 | GG3_14A | RACK | 14 | A | 1 | #N/A | ||
5643 | GG3 | GG3_14B | RACK | 14 | B | 1 | #N/A | ||
5644 | GG3 | GG3_14C | RACK | 14 | C | 1 | #N/A | ||
5645 | GG3 | GG3_14D | RACK | 14 | D | 1 | #N/A | ||
5646 | GG3 | GG3_15A | RACK | 15 | A | 1 | #N/A | ||
5647 | GG3 | GG3_15B | RACK | 15 | B | 1 | #N/A | ||
5648 | GG3 | GG3_15C | RACK | 15 | C | 1 | #N/A | ||
5649 | GG3 | GG3_15D | RACK | 15 | D | 1 | #N/A | ||
5650 | GG3 | GG3_16A | RACK | 16 | A | 1 | #N/A | ||
5651 | GG3 | GG3_16B | RACK | 16 | B | 1 | #N/A | ||
5652 | GG3 | GG3_16C | RACK | 16 | C | 1 | #N/A | ||
5653 | GG3 | GG3_16D | RACK | 16 | D | 1 | #N/A | ||
5654 | GG3 | GG3_17A | RACK | 17 | A | 1 | #N/A | ||
5655 | GG3 | GG3_17B | RACK | 17 | B | 1 | #N/A | ||
5656 | GG3 | GG3_17C | RACK | 17 | C | 1 | #N/A | ||
5657 | GG3 | GG3_17D | RACK | 17 | D | 1 | #N/A | ||
5658 | GG3 | GG3_18A | RACK | 18 | A | 1 | #N/A | ||
5659 | GG3 | GG3_18B | RACK | 18 | B | 1 | #N/A | ||
5660 | GG3 | GG3_18C | RACK | 18 | C | 1 | #N/A | ||
5661 | GG3 | GG3_18D | RACK | 18 | D | 1 | #N/A | ||
5662 | GG3 | GG3_19A | RACK | 19 | A | 1 | #N/A | ||
5663 | GG3 | GG3_19B | RACK | 19 | B | 1 | #N/A | ||
5664 | GG3 | GG3_19C | RACK | 19 | C | 1 | #N/A | ||
5665 | GG3 | GG3_19D | RACK | 19 | D | 1 | #N/A | ||
5666 | GG3 | GG3_20A | RACK | 20 | A | 1 | AMERICAN SUGAR REFINING INC | ||
5667 | GG3 | GG3_20B | RACK | 20 | B | 1 | AMERICAN SUGAR REFINING INC | ||
5668 | GG3 | GG3_20C | RACK | 20 | C | 1 | AMERICAN SUGAR REFINING INC | ||
5669 | GG3 | GG3_20D | RACK | 20 | D | 1 | AMERICAN SUGAR REFINING INC | ||
5670 | GG3 | GG3_21A | RACK | 21 | A | 1 | AMERICAN SUGAR REFINING INC | ||
QUERY |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C5620:C5670 | C5620 | =IF([SLOT.DEPTH]=1, "RACK", "FLOOR") |
G5620:G5670 | G5620 | =IF([@AISLE]=VLOOKUP([@AISLE],Table3[[AISLE.TMP]:[LEVELS]],1), LOOKUP(2,1/([@LOCATION] >= Table3[START])*([@LOCATION]<= Table3[STOP]),Table3[ACCOUNT]), "NO") |