Data Validation Custom with V Lookup

[Scott R]

Hi Everyone, I am a bit confused here.. trying to have a cell validated to
only allow between 2 numbers (depending on the vlookup).. i have tried a
formula below which wont work.. not sure if i can even do such a validation?
any help on this would be greatly appreciated..

=IF(J41="hannan",'>=(VLOOKUP(C41,'Insert
Label'!W3:Y8,2,0))'<=(vlookup(c41,'insert label'!w3:y8,3,0))," ")

 

[T. Valko]

Well, this will be a kludge!

You can't directly refer to another sheet using data validation. You can
indirectly refer to cells on another sheet but you still can't indirectly
use a formula that refers to another sheet. So....

Put the VLOOKUP formulas on the sheet where you want the validation

trying to have a cell validated to
only allow between 2 numbers

What does: =IF(J41="hannan", have to do with it then?

Let's assume you want to validate cell A1 so that it accepts only values

=Lookup1 and <=Lookup2

Cell X1:
=VLOOKUP(C41,'Insert Label'!W3:Y8,2,0)

Cell X2:
=VLOOKUP(C41,'Insert Label'!W3:Y8,3,0)

Data validation formula for cell A1:
=AND(A1>=X1,A1<=X2)

 

[Arvi Laanemets]

Hi

Define lookup values as name (Insert>Name>Define), and use defined name as
source for Data Validation List, like =MyName, (this allow you to refer to
other sheets in workbook)


Arvi Laanemets

 

[T. Valko]

Yeah, do that for a drop down list but that's not what they want.

 

[Scott R]

Thanks guys I will try this and get back to you.. think its a bit out of my
league though.. appreciate your help..

 

[Arvi Laanemets]

But nothing prevents using it there! Entries will be limited, a drop-down is
an additional bonus. My advice is: When you want to limit entries to limited
number of discrete values, always use data validation list.

Arvi Laanemets

 

[T. Valko]

OK, I see what you meant.