how to limit a cell to different set of digits

[srs243]

I need to assign three sets of number for a cell, depending on the data entry at another location in the sheet.

For Eg- if the data entry in cell A4 is Q, the number range in C10 should lie between 1-10, say

for data entry at A4 as R, the number range should lie between 10 and 100 and so on

This number has to be added by the user, returning an error if it exceeds the specified limit..


Please guide...

 

[vasim]

This can be achieved using If as well..


=IF(A4="Q",RANDBETWEEN(1,10),IF(A4="R",RANDBETWEEN(10,100)))


OR


=REPT(RANDBETWEEN(1,10),A4="Q")&REPT(RANDBETWEEN(10,100),A4="R")&REPT(RANDBETWEEN(100,500),A4="Y")

 

[srs243]

The number has to be user entered, I wish to set the limit as per the data in other cell as Q, R

 

[vasim]

Ohh I see....


Apply Data Validation to the cell, in the dropdown

Allow select Whole Number...

Data select Between...

Minimum -- Cell range (assume A1)

Maximum -- Cell range (assume A2)


In A1 enter formula

=IF(A4="Q",1,IF(A4="R",10))

In A2

=IF(A4="Q",10,IF(A4="R",100))

 

[Luke M]

To expand on Indian's idea, if you have a large set of number with various ranges, I'd suggest building a lookup table somewhere. That way you don't need to build long IF formulas.