Dynamic range name on derived list

titandronic

New Member
Joined
Jul 12, 2012
Messages
4
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)

Code:
=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
Code:
=$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.

ABC
1List1List2Result
2AAAA
3BABB
4CC
5AA
6AB

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

hurgadion

Active Member
Joined
Mar 19, 2010
Messages
426
Hi,
try using the array Formula (in C2):
Code:
=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.
 
Last edited:

titandronic

New Member
Joined
Jul 12, 2012
Messages
4
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:
<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
 

Watch MrExcel Video

Forum statistics

Threads
1,126,911
Messages
5,621,595
Members
415,847
Latest member
AlpinoHirsch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top