Lookup and validation formulas

[jsw469]

I have a formula like this:
=LOOKUP(2,1/((Number=$A4)*(Wells=D$3)),Cum_Oil)

What I am trying to do is put a data Validation in cell A1 with a drop down box of several Named Ranges ie Cum_Gas, Cum_Water and others. Can I change the "Cum_Oil" in the formula to where the Validation is so that the lookup formulae will grab data from another Range say "Cum_Gas"?

 

[T. Valko]

I have a formula like this:
=LOOKUP(2,1/((Number=$A4)*(Wells=D$3)),Cum_Oil)

What I am trying to do is put a data Validation in cell A1 with a drop down box of several Named Ranges ie Cum_Gas, Cum_Water and others. Can I change the "Cum_Oil" in the formula to where the Validation is so that the lookup formulae will grab data from another Range say "Cum_Gas"?

You can do something like this...

=LOOKUP(2,1/((Number=$A4)*(Wells=D$3)),INDIRECT(A1))

However, this will not work if the named range is defined using functions like OFFSET.

 

[jsw469]

It is

 

[T. Valko]

It is

How many named ranges are there?

What version of Excel are you using?

 

[jsw469]

15 Name Ranges for now

2010

 

[T. Valko]

15 Name Ranges for now

2010

Well, that'll mean a semi-monster formula!

You would have to list the named ranges in a range of cells like this:

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 72px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>Z</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Range1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Range2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Range3</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Range4</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Range5</TD></TR></TBODY></TABLE>


Then your formula would become:

=LOOKUP(2,1/((Number=$A4)*(Wells=D$3)),CHOOSE(MATCH(A1,Z1:Z5,0),Range1,Range2,Range3,Range4,Range5))

The named ranges in the formula must be in the exact same order as they appear in the range Z1:Z5.

You can also use the range Z1:Z5 as the source for your drop down list.

 

[Aladin Akyurek]

I have a formula like this:
=LOOKUP(2,1/((Number=$A4)*(Wells=D$3)),Cum_Oil)

What I am trying to do is put a data Validation in cell A1 with a drop down box of several Named Ranges ie Cum_Gas, Cum_Water and others. Can I change the "Cum_Oil" in the formula to where the Validation is so that the lookup formulae will grab data from another Range say "Cum_Gas"?

Since INDIRECT is not applicable to dynamic named ranges, add the following code to your workbook as a module...

_________________________________________________
Public Function EVAL(theInput As Variant) As Variant
'
' if UDF evaluate the input string as though it was on this sheet
' else evaluate for activesheet
'
' Charles Williams aka FastExcel
'
Dim vEval As Variant
Application.Volatile
On Error GoTo funcfail
If not IsEmpty(theInput) then
If TypeOf Application.Caller.Parent Is Worksheet Then
vEval = Application.Caller.Parent.Evaluate(Cstr(theInput))
Else
vEval = Application.Evaluate(cstr(theInput))
End If
If IsError(vEval) Then
EVAL = CVErr(xlErrValue)
Else
EVAL = vEval
End If
End If
Exit Function
funcfail:
EVAL = CVErr(xlErrNA)
End Function
_________________________________________________

Now you can invoke...

=LOOKUP(2,1/((Number=$A4)*(Wells=D$3)),EVAL(A1))

 

[jsw469]

TY that worked great

 

[Aladin Akyurek]

TY that worked great

Glad to hear that. Thanks for providing feedback.