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
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