Dynamic range name on derived list

[titandronic]

Hi all,

My first post...

My question is how to define a dynamic name range on an already derived (through formulae) list.
Let me give you some background and description of the problem.
I am appending two lists into a third one and then I want to do further manipulation with this list. In order to do it I want to have a dynamic name defined.
I have list of e-mails from two different sources. I append the two sources into a "Result" list using (in C2)

=IFERROR(IF(ROWS(C$2:C2)<=MATCH(REPT("z",255),List1),INDEX(List1,ROWS(C$2:C2)),INDEX(List2,ROWS(C$2:C2)-MATCH(REPT("z",255),List1))),"")

The "List1" and "List2" are already dynamic range names.
Maybe there is a more elegant way to do append of two lists via the formula... if there are any ideas, they are very welcome.

So C is my derived list. Now I need to define my "Result" dynamic range. I use

=$C$2:INDEX($C$2:$C$20,MATCH(REPT("z",255),$C$2:$C$20))

Now to the problem. This dynamic range will always return $C$2:$C$20 range regardless of the displayed range. My suspicion is somehow formulas in $C$7:$C$20 are picked up.
I want the name to return $C$2:$C$6 even though $C$7:$C$20 has formulas but displays empty string.

I have thought about using the other method (OFFSET, COUNTA combo), but can't as I can have empty cells in my two original lists (List1 or List2)

How to get around it would be most helpful.

Thanks

Andrej

SysInfo: Excel 2007 on Win XP Pro SP3

Here are my data for your refernce.

[TABLE="width: 300"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]List1[/TD]
[TD]List2[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD]AA[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]B[/TD]
[TD]AB[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]C[/TD]
[TD][/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]AA[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]AB[/TD]
[/TR]
</tbody>[/TABLE]

 

[hurgadion]

Hi,
try using the array Formula (in C2):

=INDEX($A$1:$B$10,(SMALL(IF($A$2:$B$10<>"",COLUMN($A$2:$B$10)+ROW($A$2:$B$10)/1000,""),ROW()-1)-TRUNC(SMALL(IF($A$2:$B$10<>"",COLUMN($A$2:$B$10)+ROW($A$2:$B$10)/1000,""),ROW()-1)))*1000,TRUNC(SMALL(IF($A$2:$B$10<>"",COLUMN($A$2:$B$10)+ROW($A$2:$B$10)/1000,""),ROW()-1)))

Best regards.

 

[titandronic]

Hi,

I find your idea in computing absolute positions of no-empty cells interesting. However, it would need to be adjusted to translate the absolute positions (gotten from ROW and COLUMN commands) to the relative specifications for the array in the INDEX.

Here is the edit that works as the array formula (Shift+Ctrl+enter) - there are different ranges as my test data was positioned differently in the sheet:

<code>=INDEX($H$9:$I$19,ROUND((SMALL(IF($H$9:$I$19<>"",(COLUMN($H$9:$I$19)-COLUMN($H$9)+1)+(ROW($H$9:$I$19)-ROW($H$9)+1)/1000,""),ROWS($L$9:L9))-TRUNC(SMALL(IF($H$9:$I$19<>"",(COLUMN($H$9:$I$19)-COLUMN($H$9)+1)+(ROW($H$9:$I$19)-ROW($H$9)+1)/1000,""),ROWS($L$9:L9))))*1000,0),TRUNC(SMALL(IF($H$9:$I$19<>"",(COLUMN($H$9:$I$19)-COLUMN($H$9)+1)+(ROW($H$9:$I$19)-ROW($H$9)+1)/1000,""),ROWS($L$9:L9))))</code>

But moving on. My problem is not so much in my append command (the one residing in C2 in my example). This works just fine, because it is working off non-derived (either entered or pasted form other sources) lists. My beef is with referencing the so come result in the C column for further work. As this list is now a result of the formula either mine or yours. And the dynamic range name does not pick up the correct range of the list, but rather all of the range where the formula extends to. Is this part clear?

Andrej