3rd digit fixed input DV

[Zacheryryan93]

I'm trying to set up a sheet for part numbers and new to DV formats in excel.

the format I need should look like "##1###", Where the 3rd digit is a fixed number, and always 6 digits. I will have 9 columns where the 3rd digit is 1-9 in each. I'm capable enough to change the values between columns myself but having trouble getting the base format down. i have tried piecing together parts of similar formats with no success. I Know its not as complicated as it is being, I'm just unaccustomed to the logic of excel and haven't messed with any type of coding for 10+ years

Thanks in advance

 

[jtakw]

Hi,

Assuming your 6 digit numbers will Not have leading 0 (zeros), and assuming you're starting from Column A to Column I (you say 9 columns), select A1:I#, where # is your last row for DV
Use this formula:

If your Columns for DV is elsewhere, formula will need to be modified, in that case, please specify which columns needs the DV.

Book3.xlsx
	A	B	C	D	E	F	G	H	I
1	121456	122456	123456	124567	235789	366541	727895	628754	619855
Sheet1063

Cells with Data Validation
Cell	Allow	Criteria
A1:I1	Custom	=AND(LEN(A1)=6,ISNUMBER(A1),MID(A1,3,1)+0=COLUMNS($A1:A1))

 

[Zacheryryan93]

Hi,

Assuming your 6 digit numbers will Not have leading 0 (zeros), and assuming you're starting from Column A to Column I (you say 9 columns), select A1:I#, where # is your last row for DV
Use this formula:

If your Columns for DV is elsewhere, formula will need to be modified, in that case, please specify which columns needs the DV.

Book3.xlsx
	A	B	C	D	E	F	G	H	I
1	121456	122456	123456	124567	235789	366541	727895	628754	619855
Sheet1063

Cells with Data Validation
Cell	Allow	Criteria
A1:I1	Custom	=AND(LEN(A1)=6,ISNUMBER(A1),MID(A1,3,1)+0=COLUMNS($A1:A1))

Pops up in error, But if I bypass and use it anyways, it does work on the first row of the column. Also to clarify, columns with input specifications are as follows, G, 1; H, 2; I/J, 5; K/L, 3; M/N/O/P/Q (unsure if easier to script together or just copy/paste with change to information on column), 6; R, 4; S, 8/9 (7 is unused) and all columns may, though uncommon, lead with a zero. I am currently on row 1600 and working with a sheet that was implemented at the beginning of last year by someone who knows less than I know myself. The main reason and issue with this are that information is being entered into the wrong columns. We have implemented a color coordination with this as well and if this becomes too big of an ask, that will at least help the ones who aren't getting with the program slightly.

Apologizing for there being more to this than I initially led on, but I've since realized myself that it wasn't as simple as I initially thought.

 

[jtakw]

Please clarify, is the below correct?

Book3.xlsx
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	3rd digit must be>	1	2	5	5	3	3	6	6	6	6	6	4	8 or 9
Sheet1063

And, if the "numbers" may start with 0 or 0s, then the cells must be formatted as Text, is that also correct?

 

[Zacheryryan93]

Please clarify, is the below correct?

Book3.xlsx
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	3rd digit must be>	1	2	5	5	3	3	6	6	6	6	6	4	8 or 9
Sheet1063

And, if the "numbers" may start with 0 or 0s, then the cells must be formatted as Text, is that also correct?

That is all correct! Sorry for the delayed response. I do not have access to company email outside of the facility.

 

[jtakw]

This is a Single DV formula for your Range Column G:S, assuming you're starting at G1600
Select your range G1600:S####, where #### is the last row for DV.
Change/adjust cell reference accordingly.
DV, Custom, enter formula below.

=AND(LEN(G1600)=6,ISNUMBER(G1600+0),IF(COLUMN()=19,OR(MID(G1600,3,1)+0=8,MID(G1600,3,1)+0=9),MID(G1600,3,1)+0=CHOOSE(COLUMN()-6,1,2,5,5,3,3,6,6,6,6,6,4)))

Formula can be Much shorter if we do individual formulas for Each Column G:S, but you're going to end up with 13 different formulas, and it's probably more hassle than using just 1 long formula.

 

[Zacheryryan93]

This is a Single DV formula for your Range Column G:S, assuming you're starting at G1600
Select your range G1600:S####, where #### is the last row for DV.
Change/adjust cell reference accordingly.
DV, Custom, enter formula below.

=AND(LEN(G1600)=6,ISNUMBER(G1600+0),IF(COLUMN()=19,OR(MID(G1600,3,1)+0=8,MID(G1600,3,1)+0=9),MID(G1600,3,1)+0=CHOOSE(COLUMN()-6,1,2,5,5,3,3,6,6,6,6,6,4)))

Formula can be Much shorter if we do individual formulas for Each Column G:S, but you're going to end up with 13 different formulas, and it's probably more hassle than using just 1 long formula.

Not getting that to work either. That's got to be pretty close. If you break it down each section comes up as testing okay but all together it comes back in error. I hope you're enjoying this as much as I am. This has burned up quite a bit of my down time haha

 

[jtakw]

What Exactly is your starting cell, and what Exactly is the Range you're applying DV to ?
The formula has to be written and placed correctly according to the range.

 

[Zacheryryan93]

What Exactly is your starting cell, and what Exactly is the Range you're applying DV to ?
The formula has to be written and placed correctly according to the range.

just as test I started with G2000 and the range was to 2300

 

[jtakw]

And the Column Range is G:S ??
Did you change the Starting cell from G1600 to G2000 in the formula ???