Need a Dynamic Named Range; Offset/Match using 2 Columns

Using List Data Validation
=OFFSET(Sheet1!$D$2,0,0,MATCH("*",Sheet1!$D$2:$D$20000,-1),1)
Gives me every thing but a Dynamic Named Range using 2 columns. The extra column contain Yes, No, or Blank values. The new list needs to use this extra column to narrow down the Data Validation List selections.

Need to make a list for just X; based on Yes in column B
Need to make a list for just Y; based on Yes in column C

Must be done with a Dynamic Named Range


View Excel file. View what is in Name Manager. View Selection in F7. Selection in G7 must be selections based on column B Yes value and column D's value. (I can figure out the rest)

Originally Posted by FDibbins

2 questions....

1. "Must be done with a Dynamic Named Range"....why?
2. what exactly are you trying to do, what is your expected outcome?

I use the Dynamic Named Range in cells using Data Validation Lists. Like in the example workbook cell F7. From there I am doing vlookup/s on the value in F7. The list has to be dynamic based on changing values, values being added/removed, and the contents of the 2nd column changing from Yes to No or to Blank.

Named Ranges are easier for me to deal with when I'm working with 100+ different ranges.

Originally Posted by Tony Valko

If I understand what you want you'd need to extract the relevant data into new contiguous lists then use these new lists as the source for the drop downs.

On a side note, I have relatives in Crestview!

It doesn't need to be contiguous per say... just needs to not show / allow values with a "No" in the offset column. A list like such would be fine;

123
""
""
1234
""
""

etc...

Hi Kalithro

Please check the attachment if this is what you want..

you can hide the refered list column if don't want them displayed..

Regards

Ajit
Dynamic Named Range.xlsx

I added this in a hidden worksheet and added the following to my data worksheet via Deactivated

Please Login or Register  to view this content.

Works like a champ now!

Anyway's you got the head wind to carry it forward, so i think deserve one reputation.


Cheers