Creating a dynamic sub-table

[dpw_1]

I have a data table with approximately 40 columns and potentially hundreds of rows. The data contains numbers, text, dates, times etc. I have no need to total any of the rows or columns.


Here is a simplified version with only a few columns and rows:


Col.A Col.B Col.C Col.D ColE Col. F Col.G

Don P M 100 11/25/1957 Y 12532

Tom P F 915 12/31/1999 Y 23975

Ed C F 126 12/18/1954 N 16781

Jim C M 982 7/5/2024 Y 12257


I am looking for a simple way to create a dynamic sub-table on another worksheet that would contain only the data from rows where the value in Col.F is "Y". In addition, the sub-table only needs to show a few columns (for the above example say Col.A, Col.B and Col.E).


Hope someone can help.

 

[NARAYANK991]

Hi ,


Can you check out the following link ?


https://skydrive.live.com/#!/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21140


Narayan

 

[dpw_1]

Thanks again Narayan,


Your solution works fine and I was on a similiar track. The only problem is that the sub-table does not expand dynamically with the original data table. I could add many rows to the sub-table and copy down the formulas (eliminating the #ref errors is easy) but this is not the best solution because our people actually print out the sub-table as a report and in many cases it will be pages longer than it needs to be.


Appreciate your help,


Don

 

[NARAYANK991]

Hi Don ,


Eliminating the errors is a simple matter of wrapping an =IFERROR around the existing formulae.


The named ranges Column_A , Column_B and Column_E are all dynamic , and as long as the =IFERROR wrappers are included , the error cells will all display blanks. Even the print range can be made dynamic , if required.


Narayan

 

[dpw_1]

Hi Narayan,


Yes, I see what you are saying. This will accomplish what I need although it's not as simple as I would have liked. There are some formatting issues that I will have to work around to get the sub-table to look the same as the original data table.


I don't mean to take up any more of your time on this but I will ask one more question. If I have a data table, is there a way to make a dynamic copy of that table (perhaps with fewer columns) on another worksheet and have it automatically update whenever changes are made to the original table? There would be no criteria such as I had previously, simply a dynamic copy of the same datat perhaps with fewer columns.


Again, many thanks for all of your help.


Don

 

[NARAYANK991]

Hi Don ,


I think one way is to use the camera tool ; of course it means that the copy cannot be manipulated , the way you normally manipulate cells , but it will faithfully display the original table , even on updates to the original data.


Narayan

 

[Luke M]

dpw_1,


It wouldn't necessarily be "live", but might I recommend using the Advanced Filter?

http://www.contextures.com/xladvfilter01.html

I think it's one of the better ways to handle filtering large amounts of data and transferring it somewhere else. You could make a short macro to re-run the filter and then have a button next to the sub-table, for easier updates. Just my 2 cents.

 

[dpw_1]

Can't thank you enough for the tips. I will try both suggestions.


I did figure out how to get the camera tool to be dynamic. So far, the only way to change the number of columns is to do each column individually. Not too bad though.


Regarding autofilter, that works too and the button idea might solve another problem. Our folks put together these workbooks in the field and they end up with so many work sheets it can be difficult for someone not familiar with what's going on to figure ut which sheet to enter the original data. Locking the sub-table sheets prevents data from being entered in the wrong place but it still leads to confusion.

 

[Sachin]

Why not use a pivot table where the report filter (or page filter) is the (Y/N) field?

 

[dpw_1]

Hi Narayan,


Sorry to bother you on this again but I have one more question on the sample sheet you sent. When I enter there is a blank cell (or if I clear the contents of a cell) in the original table the index formula for the sub table gives a 0 result. Is there a way around this?


I have encountered this before in when trying to create dynamic lists and sometimes the 0 value somehow prevents the dynamic functionality in successive rows.


Thanks much,


Don