Dynamic Table

[ManUBlueJay]

I have a very large Date Table 8 Cols wide 10,000 rows long. (Called Table1)
I am trying to make a new table 20 rows long (Called Table2) that will index a certain part of this table, it will be dynamic based on a Chosen name.
This will allow me to work on a small table.
I am able to extract the range from the large table and have named it TestA
I would like Table2 starting with the first non header row represent what is in TestA range.
Table2, row 1 would be the first line of TestA range.
Table2 row 2 would be the 2nd line of TestA range.............

Thanks in advance

 

[jasonb75]

What sort of solution are you looking for, formula based or vba? Which version of excel (please update your profile to show this rather than just posting it here so that it is there for people to see with all of your questions not just this one).

In office 365 it is as simple as typing =TestA into the top left cell of Table2 then the dynamic array functionality will do the rest for you, in other versions it will take a bit more effort.
That said curiosity has me wondering why you don't simply filter the original table

 

[ManUBlueJay]

Thanks for the heads-up. Changed profile but for those viewing here it is Windows 16, 64bit.
I would prefer a formula based solution in Table2.
I am running a log complicated controlled update, and it is quite time consuming to go back and forth. I will do further filtration in Table 2. If I just filter Table1 the new filtration Data would need to be on the whole table.

 

[jasonb75]

Now I follow.

See if this works. Enter this into the top left cell of Table2, changing all references to A2 to the same cell that the formula is into, making sure that the three $ signs are positioned the same. Once entered, used the fill handle to drag right and down as needed.

=IF(ROWS(A$2:A2)>ROWS(TestA),"",INDEX(TestA,ROWS(A$2:A2),COLUMNS($A2:A2)))

 

[ManUBlueJay]

Thank you. If I use your formula straight up it brings back an error. Digging further index is not recognizing my TestA range, it seems to brings back my formula as text suronding it with Quatation marks. When I view it Formulas Name Manager it selects my range. This is my Formula to find my Range: OFFSET(Data!$C$1,MATCH(CurrentFamilySortOrder,A_DataFamilySortOrderRange,0),0,COUNTIF(A_DataFamilySortOrderRange,CurrentFamilySortOrder)-1)
You could also possible build it into your index. The Unique number for this family is CurrentFamilySortOrder.
I just don't quite understand how index works to be able to continue by myself

 

[ManUBlueJay]

I have solved it by putting my TestA formula right into your index formula.
Thanks a lot

 

[jasonb75]

Seeing the range as text in the named range is normal, I'm not sure why it has worked with the definition in the formula but not in the name manager.

It's not how I would have done it, but if it works and you're happy with it then all is well.

 

[ManUBlueJay]

How would you find a unique range in a long list. I have used the offset/match using Height and Width many times because I understand how it works and use very simple functions. But I am always open to finding better ways. Ultimitaly I want to find the sortcode in my data range. The names I am after are 3 Cols to the left of that sort code, and however many sortcodes (exact match) long

 

[jasonb75]

Without having the data to evaluate, this is best guess based on what I can see from your current formulas. I'm going to use row 1 for a few extra formulas and start table2 in row 3 (leaving row 2 for headers if required).

In A1,

=MATCH(CurrentFamilySortOrder,A_DataFamilySortOrderRange,0)

In B1,

=COUNTIF(A_DataFamilySortOrderRange,CurrentFamilySortOrder)

In A3, then filled right and down as needed.

=IF(ROWS(A$3:A3)>$B$1,"",INDEX(Data!C:C,$A$1+ROWS(A$3:A3)-1))

When I looked at your formulas it appeared that you were skipping the first row of the matching part of the table, if that is correct then the -1 at the end of the third formula will need to be removed and put at the end of the countif formula instead.

By having COUNTIF and MATCH each in one cell instead of both in every cell there is a lot less calculation effort required, also by removing the OFFSET function the formula is now non- volatile so will result in less frequent recalculation.

 

[ManUBlueJay]

Brill
You interpreted it perfect.