Dynamic Range: return values based on other cell value

Hi,

Is this possible? I have 2 dynamic ranges, one for Groups and one for Options. They're 'relational':

Groups (Named Range = "ServiceGroups"):

Name | ID
Group 1 | 1
Group 2 | 2
Group 3 | 3

Options (Named Range = "ServiceOptions"):

Group_ID | Name
1 | Option 1
1 | Option 2
1 | Option 3
2 | Option 4
2 | Option 5
3 | Option 6


In the database world the relationship would be between Groups.ID and Options.Group_ID.

I have a Data Validation List drop-down that uses ServiceGroups Named Range for it's values and depending on what group is chosen, this should show the various options belonging to that group in another cell drop-down. For example if somebody selects Group 1 from the first drop-down, the 2nd drop-down should show Options 1, 2, 3

If I set the 2nd drop-drown to use ServiceOptions for it's values it obviously show all the options, so I need to adjust the Named Range to be adaptive to the value in the first drop-down.

Never done this before.

Thanks

In the process of redacting it first

OK, I've really simplified it.

Pricing sheet has just the 2 bits, and the Data sheet shows the relationships.

Thanks so much!

Is it possible to use Dynamic Ranges within that, instead of absolute cell references? This breaks it - interestingly it works OK with just a named range as the first parameter for VLOOKUP (the 'ServiceGroup' one)

=OFFSET(Data!$J$4,MATCH(VLOOKUP(ServiceGroup,ServiceGroupNames,2,),ServiceOptionNames,),,COUNTIF(ServiceOptionNames,VLOOKUP(ServiceGroup,ServiceGroupNames,2,)))

This is what ServiceGroupNames is: =OFFSET(Data!$F$4,1,0,COUNTA(Data!$F:$F)-2,1)

Thanks

Worked it out. My named ranges were wrong.

Thanks again