Create drop down list to show numbers between 2 specified numbers on different sheet

[pdong]

I have 2 specified numbers on Sheet1 and list of numbers on another sheet. I want to create drop down list just to show the numbers between 2 specified numbers on sheet 1
example:
Sheet1:

sheet 2 named 'Serialization' like this:

I used the formula below and it works on excel, but when I copy to Data validation to create drop down, it doesn't work.

IF(AND(ISERROR(LEFT($A2,1)*1),LEN($A2)=4),LEFT($A2,1)&INDIRECT("'"&$C$2&"'!"&"D"&RIGHT($A2,3)+1&":$D$"&RIGHT($B2,3)+1),IF(LEN($A2)=4,INDIRECT(""&$C$2&"!$A$"&A2+1&":$A$"&B2+1),IF(LEN($A2)=5,INDIRECT(""&$C$2&"!$B$"&$A2+1&":$B$"&$B2+1),IF(LEFT($A2,1)="E","E"&INDIRECT(""&$C$2&"!$B$"&RIGHT($A2,5)+1&":$B$"&RIGHT($B2,5)+1),IF(LEFT($A2,1)="S","S"&INDIRECT(""&$C$2&"!$C$"&RIGHT($A2,7)+1&":$C$"&RIGHT($B2,7)+1),"")))))

I broke down the formula to make the length < 250 but I only works with this formula as shown down here:
IF(LEN($A2)=4,INDIRECT(""&$C$2&"!$A$"&A2+1&":$A$"&B2+1),IF(LEN($A2)=5,INDIRECT(""&$C$2&"!$B$"&$A2+1&":$B$"&$B2+1),"")

The rest of formula doesn't work:
IF(AND(ISERROR(LEFT($A2,1)*1),LEN($A2)=4),LEFT($A2,1)&INDIRECT("'"&$C$2&"'!"&"D"&RIGHT($A2,3)+1&":$D$"&RIGHT($B2,3)+1),"")

IF(LEFT($A2,1)="E","E"&INDIRECT(""&$C$2&"!$B$"&RIGHT($A2,5)+1&":$B$"&RIGHT($B2,5)+1),IF(LEFT($A2,1)="S","S"&INDIRECT(""&$C$2&"!$C$"&RIGHT($A2,7)+1&":$C$"&RIGHT($B2,7)+1),""))

Please help.
thanks

 

[CA_Punit]

I suppose you wont need such a long formula to return number between two Numbers

New Microsoft Excel Worksheet.xlsx
	M	N	O	P
7	250	255		250
8				251
9				252
10				253
11				254
12				255
Sheet2

Cell Formulas
Range	Formula
P7:P12	P7	=ROW(INDIRECT(M7&":"&N7))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
P7	Custom	=ROW(INDIRECT(M7&":"&N7))

And Drag it Down

Also once you have the Numbers Just Select the whole range and go to data validation and to create drop down list

 

[pdong]

Thank you,
but the lists are different format of numbers with multiple conditions per formula and I want to create a drop down list based on input from column A and column B on Sheet1.
Thanks

 

[pdong]

I encounter error message when I copy the below formula to data validation for drop down list

IF(AND(ISERROR(LEFT($A2,1)*1),LEN($A2)=4),LEFT($A2,1)&INDIRECT("'"&$C$2&"'!"&"D"&RIGHT($A2,3)+1&":$D$"&RIGHT($B2,3)+1),"")

the error said: the list source must be a delimited list, or a reference to single row or column
Please help