# Dynamic range name on derived list

#### titandronic

##### New Member
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.

 A B C 1 List1 List2 Result 2 A AA A 3 B AB B 4 C C 5 AA 6 AB

<tbody>
</tbody>

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

##### Active Member
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
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

Replies
5
Views
105
Replies
5
Views
225
Replies
4
Views
114
Replies
9
Views
342
Replies
1
Views
244

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.

### Which adblocker are you using?

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

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