Validity: Using index and match to pick a sub set of a cell range

[Erika]

I found a validity script years ago that lets you pick a sub set of items on the same line of a primary set (column). I am trying to use it in a new spreadsheet and if I copy the script or the sheet and move it to a new sheet or file it breaks!!

I set the options:calc:calculate on the new sheet the same as the old. I do not have any ranges set in the original sheet but have tried setting ranges to fix it, to no avail.
The script - INDEX(type,MATCH(A5,family,0))

I have read up on INDEX and MATCH and am starting to get a basic idea on there use. My next though is to substute family for the the cel range (b4:e9), but why does the original work?
I think there may be a setting for calc I have missed that lets this work in the original but not in a copy, I have looked but can not find it?

Included:
test1.ods (11.8 KB)

test2.ods (11.1 KB)
Test1 is the sheet that works
Test2 is the sheet I copied test1 to and tried to make a sheet from scratch
Test1.png is the calc settings
Any idea on what I missed or what the script is doing that I need to adjust? Thanks
Libre 6.3.3

[schiavinatto]

Define names for data areas and hit the formula in validation.

test2_GS.ods (17.7 KB)

The validation formula can also be like this:
IF(Range=G4;RangeB;"")
test2_GS2.ods (17.7 KB)

[Erika]

Thanks, I worked on this during the last couple of days, trying all sorts of options, but I never got it 100%.
What troubled me, is the original has no named ranges but seems to use the names for the data areas? Is there a different option to name the the data area besides the Data:Define Range?

[schiavinatto]

(1) Select range
(2) Name and Enter

[Erika]

Now I got the whole picture, I forgot about the named ranges, although I don’t remember knowing about them in the first place!!. I could not figure where it was getting the named range!! Thanks Again