Dependent dropdowns and dynamic named ranges

[vockleya]

I’m having an interesting issue with dynamic named ranges and the INDIRECT() function. I’m trying to use one dropdown to select which dynamic range appears in a second dropdown.

As far as I can tell, the dynamic range is being created correctly. If I take the equation that creates the dynamic named range and put it directly in the dropdown’s data validation equation, the list populates correctly.

The problem comes when I try to use INDIRECT() to use the 1st dropdown to choose what range the second dropdown displays. I just get a #REF error in the list.

I’ve attached a sample spreadsheet to illustrate my point, so I hope this makes sense.

I realize that a simple solution to this would simply be to separate out each part category to a different sheet, so that all ranges can be static ranges, but I really want to have all parts in a single sheet.

I feel like I may be running up against a feature limitation of LibreOffice Calc, as everything I can find makes it seem like I’m doing this correctly. Any help would be greatly appreciated.

Parts Test.ods

[SouthernWolf]

When you pull up the Validity dialog box do you have “cell range” selected in the allow: dropdown menu?

[vockleya]

Yep. When I put the formula that calculates the range directly in the validity dialog, it works correctly, It breaks when using INDIRECT() to get the range name from another cell.

[anon73440385]

@SouthernWolf - please do not request further information from OPs by using Add Answer but use add a comment instead. Answers on this site are reserved for solutions to the question. Thanks in advance.

[SouthernWolf]

@anon73440385 - Leave it up to me to screw up my first day here.

[schiavinatto]

@vockleya, I used named areas, check …

The Validation formula: OFFSET(TabPart;MATCH(A2;TabPartType;0)-1;0;COUNTIF(TabPartType;A2);1)

Test file

---

ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.

If the answer met your need, please click on the ball to the left of the answer, to finish the question.

[vockleya]

That’s so much simpler that I was making it. Thanks for the help.

[schiavinatto]

The Part Type column must be ordered.

[JohnSUN]

By the way, the formula

IF(TabPartType=$A2;TabPart;"") 

looks a little shorter, but does the same

[schiavinatto]

@JohnSUN, I hadn’t imagined using such a simple formula, thanks.