How to automatically update formula if there is no Table

[daliye]

Hi,
How do we let formulas in the yellow cells update automatically if the left cell has a value?
The reason for my question is the left cell is part of an array formula, and if the table got updated the new cell is generated with a new cell. Still, the cell in the yellow area does not get updated, and I can not apply the table because it will break the array.
How to achieve it.
When I created it 1st time I dragged the cell vertically manually but what about later updates, I do not want to do it manually.
This is why I chose the array approach for the left cell.
Thanks

First block

1st Level

Please Login or Register  to view this content.

2nd Level

Please Login or Register  to view this content.

and dragged manually from N3 to N10.

Second Block

2nd Level

Please Login or Register  to view this content.

3rd Level

Please Login or Register  to view this content.

and dragged manually from R3 to R10.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Data												Help				Help
2	1st Level	2nd Level	3rd Level	4th Level	5th Level	6th Level	7th Level	8th Level	9th Level	10th Level	11th Level		1st Level	2nd Level			2nd Level	3rd Level
3	Alaric	Blackthoon	Percival	Thornfield	Thorne	Emberstone	Orion	Nightfall	Vesper	Nightshade	Jasper		Alaric	Blackthoon			Blackthoon	Percival
4	Asher	Stormrider	Thalia	Nightshade	Selene	Starlight	Isadora	Frostbloom	Bastian	Stormwatch	Selene		Asher	Stormrider			Blackthorn	Seraphiel
5	Astrid	Marlowe	Verity	Blaise	Victor	Storm	Odalys	Silvermoon	Peregrine	Storm	Twilight		Astrid	Marlowe			Creedsong	Odessa
6	Atlas	Hawthornesong	Xander	Eclipsesong	Nightshade	Orion	Augustus	Peregrine	Frost	Stardustwhisper	Isadora		Atlas	Hawthornesong			Crowe	Amara
7	Atticus	Frost	Maximilian	Ravenwood	Zephyr	Hawthorne	Odalys	Silvermoon	Peregrine	Storm	Leopold		Atticus	Frost	Frostwood		Fox	Seraphim
8	Atticus	Frostwood	Isolde	Frostwood	Thorne	Blackthorn	Isolde	Ravenwood	Twilightwhisper	Nightingale	Valerian		Augustus	Blackthorn			Frost	Maximilian	Oberon
9	Augustus	Blackthorn	Seraphiel	Ember	Seraphina	Nova	Xander	Winter	Sapphira	Iliad	Odalys		Bastian	Wilder			Frostwood	Isolde
10	Bastian	Wilder	Vesper	Frost	Lysandra	Frost	Seraphiel	Phoenix	Zephyrine	Starwhisper	Hawkmoon		Blackthorn	Lilith			Hawthornesong	Xander
11	Blackthorn	Lilith	Shadow	Seraphiel	Nova	Cassius	Frost	Orion	Shadow	Seraphina	Everhart		Briar				Lilith	Shadow
12	Briar	Phoenix	Thorne	Twilight	Odessa	Obsidian	Lysandra	Frost	Frost	Moonwhisper	Lysandra		Calista				Marlowe	Verity
13	Calista	Fox	Seraphim	Stardust	Thorne	Rowan	Evangeline	Frost	Sapphira	Winters	Dusk		Calypso				Nightshade
14	Calista	Nightshade	Valencia	Hawkmoon	Thistle	Skywhisper	Amara	Quillon	Shadow	Lyra	Oberon		Casimir				Phoenix
15	Calypso	Frost	Oberon	Frost	Quillon	Shadow	Xander	Nightshade	Cassiopeia	Frostfire	Moonstone		Caspian				Stormrider
16	Calypso	Nightshade	Briar	Obsidian	Rowan	Rosewood	Orion	Emberstorm	Crowesong	Frost	Eclipse						Stormwhisper
17	Casimir	Creedsong	Odessa	Frost	Storm	Zephyrine	Caspian	Asher	Hawthornesong	Selene	Seraphina						Wilder
18	Casimir	Crowe	Amara	Creed	Bastian	Rain	Orion	Ember	Ember	Twilightwhisper	Valencia
19	Caspian	Stormwhisper	Orion	Frostwhisper	Winter	Veridian	Thorne	Octavia	Frost	Thornwhisper	Odessa

Sheet: Sheet1

[AliGW]

Not sure I follow completely, but in M3:

=SORT(UNIQUE(FILTER(tbl_Data,tbl_Data[#Headers]=M2)))

and in N3 copied down as far as you like:

=IFERROR(LET(l,FILTER(tbl_Data,tbl_Data[#Headers]=M$2),TRANSPOSE(FILTER(FILTER(tbl_Data,(tbl_Data[#Headers]=N$2)),l=M3))),"")

[daliye]

in M3:

Please Login or Register  to view this content.

This one is better then mine, and I will use it.

in N3 copied down as far as you like:

Please Login or Register  to view this content.

If I do not want to copy manually, is it possible to set it like, if there is an entry in the following M, to copy the cell otherwise not.
For example, if we are adding the formula in N3 according to the data in M3 that is being used.
Is it possible to include anything in the formula in N3 that if M4 has any info or is not empty ...etc to copy the formula down to N4 ?

[AliGW]

One way:

=LET(a,FILTER(tbl_Data,tbl_Data[#Headers]=M2),b,FILTER(tbl_Data,tbl_Data[#Headers]=N2),TRANSPOSE(IFERROR(DROP(REDUCE("",M3#,LAMBDA(z,y,HSTACK(z,FILTER(b,a=y)))),,1),"")))

[daliye]

Brilliant.
Solved!

You must spread some Reputation around before giving it to AliGW again.

[AliGW]

Glad to have helped.

[daliye]

Please Login or Register  to view this content.

Please if your time allows.
I do not know if it is allowed or not, and it is indeed worked like magic.
Is it possible to divide the formula parts so I can understand, I tried since your last reply to figure out the steps or reproduce it so I can learn it, but lost.
I mean from the left-to-right you used:

1. LET
2. FILTER
3. FILTER
4. TRANSPOSE
5. IFERROR
6. DROP
7. REDUCE
8. LAMBDA
9. HSTACK
10. FILTER

Is it possible to tell what each part did?
I am sorry in advance if my request annoyed you, but this is how I learn everything by dividing and redoing so I can learn it correctly.
Sorry once again and thanks in advance.

[AliGW]

Follow the links here:

https://bettersolutions.com/excel/fu...-functions.htm

and here:

https://bettersolutions.com/excel/fu...-functions.htm

Try to reverse engineer it yourself (it will stick better if you do). Then, if there are any parts you can't work out, come back and ask.

FILTER, TRANSPOSE (which you'd used yourself) and IFERROR are pretty straightforward.

[AliGW]

In the attached, on the right, I have broken down the formula into sections to show what each function does.

[daliye]

I am speechless indeed.
Thanks a lot my dear friend

[AliGW]

Let me know if anything needs further explanation.

[DJunqueira]

The excelent solution of AliGW in one formula.

Please Login or Register  to view this content.