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))," ")

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)

--
Biff
Microsoft Excel MVP


"Scott R" wrote in message
...
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))," ")

Hi

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


Arvi Laanemets


"Scott R" wrote in message
...
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))," ")

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

--
Biff
Microsoft Excel MVP


"Arvi Laanemets" wrote in message
...
Hi

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


Arvi Laanemets


"Scott R" wrote in message
...
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))," ")

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

"Scott R" wrote:

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))," ")

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" wrote in message
...
Yeah, do that for a drop down list but that's not what they want.

--
Biff
Microsoft Excel MVP


"Arvi Laanemets" wrote in message
...
Hi

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


Arvi Laanemets


"Scott R" wrote in message
...
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))," ")

OK, I see what you meant.

--
Biff
Microsoft Excel MVP


"Arvi Laanemets" wrote in message
...
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" wrote in message
...
Yeah, do that for a drop down list but that's not what they want.

--
Biff
Microsoft Excel MVP


"Arvi Laanemets" wrote in message
...
Hi

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


Arvi Laanemets


"Scott R" wrote in message
...
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))," ")