Ask Your Question

Dependent dropdowns and dynamic named ranges

asked 2020-04-30 22:16:37 +0200

vockleya gravatar image

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.

C:\fakepath\Parts Test.ods

edit retag flag offensive close merge delete


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

SouthernWolf gravatar imageSouthernWolf ( 2020-04-30 22:26:30 +0200 )edit

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.

vockleya gravatar imagevockleya ( 2020-04-30 22:42:36 +0200 )edit

@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.

Opaque gravatar imageOpaque ( 2020-04-30 22:57:40 +0200 )edit

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

SouthernWolf gravatar imageSouthernWolf ( 2020-04-30 23:14:04 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-05-01 00:58:35 +0200

@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 Descrição da imagem to the left of the answer, to finish the question.

edit flag offensive delete link more


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

vockleya gravatar imagevockleya ( 2020-05-01 01:14:31 +0200 )edit

The Part Type column must be ordered.

Schiavinatto gravatar imageSchiavinatto ( 2020-05-01 01:22:47 +0200 )edit

By the way, the formula


looks a little shorter, but does the same

JohnSUN gravatar imageJohnSUN ( 2020-05-04 07:14:36 +0200 )edit

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

Schiavinatto gravatar imageSchiavinatto ( 2020-05-04 13:39:17 +0200 )edit
Login/Signup to Answer

Question Tools



Asked: 2020-04-30 22:16:37 +0200

Seen: 99 times

Last updated: May 01