Automatic Location Allocation & Unique Reference Insertion

  • Posted:
  • Proposals: 1
  • Remote
  • #664486
  • Completed
Simon P. has already sent a proposal.
  • 0

Description

Experience Level: Intermediate
Estimated Job duration: 1 day or less
Description of excel work: 1 - Based on the information inserted into Column A of the 'Allocations' sheet, insert the next free location for that particular tub size (locations tab contains all locations and a free location is indicated by a '1' in the cell, the size of the location is indicated in the cell next to this as S1, S2, S3 etc)

2 - Once this location has been allocated, insert location into column C of the 'Allocations' sheet.

3 - Now i need a unique reference assigned to each row. This is known as the fast find ref. There is a sheet for this also. We need to copy the 'supplier code' from 'Allocations' and past this into the next available reference number available on the 'Fast Find Ref' sheet. This will be identified as it will be a blank cell in column B

4 - Once the reference has been allocated, it needs to be populated in the relevant cell in the 'allocations' sheet (column D)

I need to be able to add new locations to the 'Locations' spreadsheet should we expand our warehouse. And i also need to be able to add additional tub sizes should we need to get new size tubs for new products. A complete running total of the number of free spaces for each size of tub needs to be kept.

Attached is the spreadsheet i currently use. As you can see its all manual at the moment apart from the totals which is a simple formula.

Tub sizes are S1, S2, S4, S4, S5, S6 for now.
Extra notes:

When the fast find ref is generated next to the product location on the 'locations' sheet, it needs to have a small a followed by a hyphon inserted to prefix the fast find ref. e.g. if it had a ref of 546789, when allocated to a location it needs to go in as 'a-546789'

New Proposal

Create an account now and send a proposal now to get this job.

Sign up

Clarification Board Ask a Question

    There are no clarification messages.