I've created a Dynamic Range Name that uses the OFFSET() function with some embedded MATCH(), VLOOKUP(), & SUMIF() functions. That Dynamic Range is being used as a Validation list.

Basically, I'm trying to drive a Validation List that changes, depending on what value a user has selected into one of two adjacent cells. Those "driving" selections are based on static Validation Lists.

The user is only supposed to select into a cell within one of those two adjacent columns, but in the event that they select something in both, one of the columns prevails.

Depending on what's been selected within those two Validated cells (ideally only one user selection), my target Dynamic Range Validation List is supposed to return a contiguous subset of one large range of data.

So, for my two "driving" pick lists, one of them is a list of values & the other includes those values with a suffix, where the value & the suffix are delimited by a period (".").

My Dynamic Range checks for the presence of data in a cell that captures just the value. It it's there, then use that as the basis for the Dynamic Range Validation List.

If the cell that captures just the value ISBLANK(), then I fall back to the one that includes the suffix & apply a LEFT() function to lop off the suffix & the "." & then use that value to determine the basis for the Dynamic Range Validation List.

When I just specify the value, my Dynamic Range Validation List works beautifully.

However, when I try to use the column that includes the suffixes, my Dynamic Range contains -0- rows.

To test whether my nested MATCH() & SUMIF() formulas don't have an error, I've isolated those embedded formulas into their own cell to see what values they generate under the different driving list selections.

Those embedded formulas return the exact same values using either selection method. I can't for the life of me figure out why the Dynamic Range List works under one scenario but not the other.

Below, I've broken the OFFSET() formula into its separate components, separated by a carriage return. The MATCH() & SUMIF() formulas are pretty lengthy.

DynamicCurveList range formula
=OFFSET('Deal XRefs & validation'!$BP$1
,MATCH(VLOOKUP(IF(NOT(ISBLANK(OFFSET(Pricing!J2,0, COLUMN(Pricing!$H2)-COLUMN(Pricing!J2)))), OFFSET(Pricing!J2,0,COLUMN(Pricing!$H2)-COLUMN(Pricing!J2)),LEFT(OFFSET(Pricing!J2,0,COLUM N(Pricing!$G2)-COLUMN(Pricing!J2)),FIND(".",OFFSET(Pricing!J2,0,C OLUMN(Pricing!$G2)-COLUMN(Pricing!J2)))-1)),Provisions,3,FALSE),Base_Oils_Price_Curve_Grou ping,0)
,0
,SUMIF(Price_Curve_Grouping,"="&VLOOKUP(IF(NOT(ISB LANK(OFFSET(Pricing!J2,0,COLUMN(Pricing!$H2)-COLUMN(Pricing!J2)))),OFFSET(Pricing!J2,0,COLUMN(P ricing!$H2)-COLUMN(Pricing!J2)),LEFT(OFFSET(Pricing!J2,0,COLUM N(Pricing!$G2)-COLUMN(Pricing!J2)),FIND(".",OFFSET(Pricing!J2,0,C OLUMN(Pricing!$G2)-COLUMN(Pricing!J2)))-1)),Provisions,3,FALSE),Price_Curves_Counter)
,1)

As an alternative, the whole spreadsheet is 47kb, so I could email it if someone would be kind enough to take a look.

Have you considered using 'dependant' DV lists, where user selects 1st
column and this determines what appears in the 2nd column's DV, ..and
so on?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

Have you considered using 'dependant' DV lists, where user selects
1st column and this determines what appears in the 2nd column's DV,
..and so on?

For example...

http://www.contextures.com/xlDataVal02.html

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

On Friday, September 9, 2016 at 12:13:44 PM UTC-5, GS wrote:
Have you considered using 'dependant' DV lists, where user selects
1st column and this determines what appears in the 2nd column's DV,
..and so on?

For example...

http://www.contextures.com/xlDataVal02.html

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

That's almost what I'm trying to do, with a slight variation.

All of those examples require separate lists to drive the dependent Validation List. I don't need separate lists, I need the final picklist to be sourced from the same data. Even the lower examples where they differentiate between yellow & red fruit require separate lists.

Imagine, within the example on the page you forwarded, that the user can either pick (in column B, as shown) "Fruit", OR they can leave column B blank & instead pick (within column A) "Fruit.Grocer" or "Fruit.FarmersMarket".

The Dynamic pick list in Column C needs to be the sourced from the same data range, regardless of how they select it.

i.e. whether the user picks "Fruit" in column B or they pick "Fruit.Grocer" in column A, Column D needs to show "Apple, Banana, Lemon, Peach"

In my case, as long as the user picks within column B, everything is fine. However, if they pick from column A, even after I've isolated the portion of the column A selection that just says "Fruit", my dynamic list doesn't return any rows.

The puzzling thing is that when I break out the formulas that calculate the OFFSET parameter, both methods result in the same values. That leads me to conclude that I don't have errors in those formulas and that it's got to either be a limitation in Excel or a configuration somewhere that I've missed.

I even checked to see whether Range Names would accept the Array format (Ctrl+Shft+Enter). BTW, they don't accept those....

Those dependant DV lists have proven themselves over many years to work
'flawlessly' when properly configured. DV doesn't have the ability to
use filters, thus why your breakout formulas work. Rethink how your
project works and go with proven methods!<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

On Friday, September 9, 2016 at 4:11:05 PM UTC-5, GS wrote:
Those dependant DV lists have proven themselves over many years to work
'flawlessly' when properly configured. DV doesn't have the ability to
use filters, thus why your breakout formulas work. Rethink how your
project works and go with proven methods!<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

But I'm not really filtering anything, I'm adjusting the starting row shift & the depth of a single column OFFSET() range. The formulas behind the components of the OFFSET() function return the same integer values, regardless of how the selections are made, so why doesn't the Dynamic Range respond the same way?

I'm just trying to determine whether I've hit a limit of Excel functionality or I've got an error in my formulas/configuration.

My model depends upon asking other people to provide the data in that list (I know what it looks like & how to get it, but I don't have access to extract it myself) and it's likely the data will need to be refreshed a few times before it's finalized. I'd much rather give them a single select statement that they can save in a single results file than a dozen select statements that they have to save into a dozen files.

I understand that code is code & sometimes the arrangements of the 1's & 0's result in something that falls outside of design. Maybe that's where I'm at & I need to request an enhancement (that may or may not ever be incorporated into the product).

However, I don't advocate letting "the way we've always done things" stand in the way of "the way we ought to do things".

If we limit our thinking to "=A1+A2+A3" because it's a tried & true method, we'll never get:
=SUM(A1:A3)
=SUMIF(A1:A3, "0")
and {=SUM((A1:A30)*(A1:A3))}

Again, at present, I'm just trying to assess whether it's a software limit, or a logic/configuration error.

If it's a software limit, then it sounds like I'll have to break the data into separate lists, exactly as you've described. If I've simply not configured something correctly, a single list is more considerate to the folks I have to rely on.