Hi all,
I have a spreadsheet that I am trying to write a macro for. The first worksheet contains a master list of each product ID, its bin location, and the date it was moved to that bin. The subsequent worksheets are weekly inventories. All dates are in Julian date format. What I am trying to do is use either vLOOKUP or INDEX/MATCH to populate the correct bin location for each product in the weekly inventories. Some products are removed and some new products are added periodically so the product ID list may not be the same from week to week. The logic that I am trying to put into code is: If the product ID in the weekly inventory matches a product ID in the "Bin Locations" worksheet AND the weekly inventory worksheet's name (Julian date) is equal to or greater than the Julian date in column "C" of the "Bin Locations" worksheet, then enter the Bin Location in the appropriate cell in the weekly inventory worksheet. The catch is that it also needs to check to see that it's the most current bin location, without exceeding the Julian date of that weekly inventory. Any help would be greatly appreciated.
Sample "Bin Locations" worksheet:
<tbody>
</tbody>
Each of the weekly inventory worksheets are named with the week's starting Julian date (JD 156, JD 163, JD 170, JD 177...):
<tbody>
</tbody>
Tom
I have a spreadsheet that I am trying to write a macro for. The first worksheet contains a master list of each product ID, its bin location, and the date it was moved to that bin. The subsequent worksheets are weekly inventories. All dates are in Julian date format. What I am trying to do is use either vLOOKUP or INDEX/MATCH to populate the correct bin location for each product in the weekly inventories. Some products are removed and some new products are added periodically so the product ID list may not be the same from week to week. The logic that I am trying to put into code is: If the product ID in the weekly inventory matches a product ID in the "Bin Locations" worksheet AND the weekly inventory worksheet's name (Julian date) is equal to or greater than the Julian date in column "C" of the "Bin Locations" worksheet, then enter the Bin Location in the appropriate cell in the weekly inventory worksheet. The catch is that it also needs to check to see that it's the most current bin location, without exceeding the Julian date of that weekly inventory. Any help would be greatly appreciated.
Sample "Bin Locations" worksheet:
| Prod ID | Bin Location | As Of Julian Date |
| b111 | 1 | 152 |
| b111 | 4 | 167 |
| c905 | 2 | 152 |
| j543 | 2 | 152 |
| k444 | 2 | 152 |
| m009 | 2 | 152 |
| m009 | 3 | 159 |
| s123 | 1 | 152 |
| s123 | 5 | 156 |
| C545 | 3 | 167 |
<tbody>
</tbody>
Each of the weekly inventory worksheets are named with the week's starting Julian date (JD 156, JD 163, JD 170, JD 177...):
| Prod ID | Bin Location | Quantity |
| b111 | 1 | 3 |
| c905 | 2 | 5 |
| j543 | 2 | 4 |
| k444 | 2 | 55 |
| m009 | 2 | 3 |
| s123 | 5 | 6 |
<tbody>
</tbody>
Tom
