[Dropped] Formula with dynamic max value of merged range

[moocan]

Hello,

I'm not an expert, so I apologize in advance for my vocabulary.

I trying to update a previous file without "dynamic" drop-down menu (Data > Validity > Cell Range)
I have a working example with drop-down Validity which are "dynamic" and working but ...

Here is my example
Sheet: CONFIG

Code: Select all

ROW		Title	Header	Values...
10	    Menu.1	Planet	Pluton	Mars	Earth	Moon		
11		Menu.2	Color	Yellow	Orange	Green	Blue	Red	Purple
12		Menu.3	Fruits	Apple	Strawberry			

Title column has no effects just a label to remember.

Sheet: CONFIG: Named Ranges

Code: Select all

MENU1_HEADER : $CONFIG.$B$10
MENU2_HEADER : $CONFIG.$B$11
MENU3_HEADER : $CONFIG.$B$12
MENU1_VALUES : $CONFIG.$C$10:$N$10
MENU2_VALUES : $CONFIG.$C$11:$N$11
MENU3_VALUES : $CONFIG.$C$12:$N$12

Sheet: Sheet2

Code: Select all

ROW		Data > Validity > Cell Range > Source
B10		INDEX((MENU1_HEADER~MENU3_HEADER);0;0;{1;2})

Result = Drop down menu display these choices:
"Planet", "Fruits"

Code: Select all

ROW		Data > Validity > Cell Range > Source
B17		INDEX((MENU1_VALUES~MENU3_VALUES);0;0;MATCH(B10;INDEX((MENU1_HEADER~MENU3_HEADER);0;0;{1;2});0))

Result = Drop down menu display these choices
"Planet","Pluton","Mars", "Earth", "Moon" if I select "Planet" in B10
"Apple","Strawberry" if I select "Fruits" in B10"

I have choice this way because in my targted update I need some Drop-down menu which will mix as example
Menu.1 + Menu 2 or Menu.2 + Menu.3 or Menu.1 + Menu.3 or Menu.1 + Menu.2 + Menu.3
through different sheets.

Now what I trying to do with a sort of "dynamic range" in formula (getting the number of range merged in bold)
INDEX((MENU1_HEADER~MENU3_HEADER);0;0;{1;2})

INDEX((MENU1_HEADER~MENU3_HEADER);0;0;{1;2}) is Working.
INDEX((MENU1_HEADER~MENU2_HEADER~MENU3_HEADER);0;0;{1;3}) is Working.
but I need to fix 2 or 3 manually.

INDEX((MENU1_HEADER~MENU3_HEADER);0;0) Not working only First "element" is returned and not all.

When I'm trying these formulas using ROWS or AREAS ... err502 are starting

Code: Select all

INDEX((MENU1_HEADER~MENU3_HEADER);0;0;{1;ROWS(MENU1_HEADER;MENU3_HEADER)})
INDEX((MENU1_HEADER~MENU2_HEADER~MENU3_HEADER);0;0;{1;ROWS(MENU1_HEADER;MENU2_HEADER;MENU3_HEADER)})

Code: Select all

INDEX((MENU1_HEADER~MENU3_HEADER);0;0;{1;AREAS(MENU1_HEADER~MENU3_HEADER)})
INDEX((MENU1_HEADER~MENU2_HEADER~MENU3_HEADER);0;0;{1;AREAS(MENU1_HEADER~MENU2_HEADER~MENU3_HEADER)})

Inside a cells ROWS(...) or AREAS(...) are returning corresponding values such as 2 or 3
But inside the Index this does not work.

Is it possible to get dynamically the "Max value of merged range" ?

Thanks you for your help.
Regards

[MrProgrammer]

Hi, and welcome to the forum. Regretfully no one has responded for many months but I, and perhaps others, am having difficulty understanding your goal. You are more likely to get assistance if you attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself) since it would take 10 or 15 minutes to read through your description and replicate that, including the various defined names, into a spreadsheet for testing. Explain how and where you choose Menu.1 + Menu 2 or Menu.2 + Menu.3 or Menu.1 + Menu.3 or Menu.1 + Menu.2 + Menu.3.

I apologize in advance for my vocabulary.

I think your use of "merged range" does not mean a range merged with Format → Merge Cells, and this topic involves the Union operator, ~, the INDEX function, and array constants.

Is it possible to get dynamically the "Max value of merged range"?

"Max value" suggests the MAXIMUM function, but the rest of your post makes me believe that isn't what you want. Perhaps you want the last column of a set of ranges joined by the Union operator? Or the first and last columns of a set of ranges joined by the Union operator?

I'm not an expert …

I suspect you know much of this, but for reference:
[Tutorial] Ten concepts that every Calc user should know