Data Validation

[Harvey12]

Hi All,

I am currently working on a confidential file (so unfortunately cannot do any kind of file drop) so I will try to explain my issue to the best of my ability.

In cell C3 I have a prefix for a part number, for example: 9831.
In cell D3 I have a suffix for a part number, for example: 0000
In cell E3, I have the numbers combined, using this formula: =C3&TEXT(D3,"0000") To create: 98310000

To create a whole part number in cell F3 I used the formula: =LEFT(E3,4)&"-"&RIGHT(E3,4) To create: 9831-0000
This formula is only in place to put a hyphen in the number as that is what a correct part number looks like.

The suffix of the part number increases in increments of 50. EG: 0000, 0050, 0100, 0150, and so on until, 9950. Once it reaches 9950 that is the end of that prefix of part numbers.

What I then what to do is create a formula which is constantly checking the combined cell (E3) to check to see if that number is unique. So when we get to the end of the suffix numbers eg: 9950, I then want those suffix numbers to start again, (0000) and add 10 to the prefix part of the part number (9841) to create 98410000 and so on.

If the number is not unique I need it to not let that number be imputed.

If you have any questions please ask.

Harvey

 

[etaf]

what data is input ?

not sure i see how you want this to work

are you entering the info in any cells and creating the partnumber or is the system doing it all

lost me when you then asked

What I then what to do is create a formula which is constantly checking the combined cell (E3) to check to see if that number is unique. So when we get to the end of the suffix numbers eg: 9950, I then want those suffix numbers to start again, (0000) and add 10 to the prefix part of the part number (9841) to create 98410000 and so on.

you can use a countif() to test if the number is duplicated and flag that up

also do you now remove the - ?
when you start to use the new prefix

 

[Harvey12]

The data in the sheet in about different types of manual for excavators, such as: Operator manual, service manual and a parts book, similar to the manual you would find in the glovebox of your car.

So the data input is a part number for the manuals broken down in numerous ways.

In column C I have the part number prefix. EG. 9841
In column D I have the part number suffix. EG. 0000
In column E is the combined part number minus the "-". EG. 98410000
In column F is the full part number for the manual which would look like this (OM-9841-0000) The OM section is because it's an operator manual, SM would be for service manual and PB for parts book. Each manual type has a separate tab on the excel sheet.

In terms of the Data we have already created a 'data validation' list for all the possible PREFIX's part number since we have an allotted section of part numbers we can use. (The amount is not infinite)
In terms of the suffix. The first one has been imputed for the purposes of the document but since we know that this number always increases in definite increments of 50 it is easy to write a simple formula of (EG. =Sum(D3+50)) This will of course increase the previous number by 50 so we have that automated also.

Of course this will continue counting in this way... 0000,0050,0100,0150..... 9850, 9900, 9950. After 9950, the formula will obviously count 10,000. The issue with this is our SUFFIX can only be a 4 number suffix. So because this can only be 4 digits we increase the prefix by 10. So what needs to happen is as follows:

9841-9900
9841-9950
9851-0000
9851-0050

And so on until:

9851-9900
9851-9950
9861-0000
9861-0050

You catch the drift. I need the sheet to be able to do that automatically. No the "-" Needs to remain.

Many thanks