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.