Thread: Assemble table in new w/s from rows selected in other w/s

Hi All,

I think this might be a programming issue (not sure but I hope it can be solved).

I have three sheets, each containing a table. I'd like users to be able toggle or select rows from these tables for inclusion in a fourth table in a new worksheet.

Unfortunately I have zero knowledge of vba and 0.01 knowledge of macros. I'm attaching a sample of what I'm working with.

In the attached, the "price list," "likely to reference," and "optional" worksheets contain the source tables; I've added a column to the right of each called "include" (where I'd like a user to be able to select a desired row).

The "custom" worksheet is where I'd like an assembled worksheet to live.

If anyone could give me a sense of if this is possible, and how, that would be great.

Thanks in advance!

Have a look, this sheet will assemble itself in realtime on the "custom" sheet. You can put any value at all in column F on the first 3 sheets, it will trigger the "Key" column G as long as the cell isn't blank. The unique index values in the key columns translate to rows in the custom table.

I've added in a special new function that doesn't exist in Excel, it's called STRING CONCATENATION. This allows you to do a SUMIF() type analysis of a range of values, but instead of adding when values match, it gathers text strings together and lists them together in a cell.

The UDF I installed is called CONCATIF().
http://www.excelforum.com/excel-prog...in-cell.html#2 'explanation


I also added a new CUSTOM2 sheet that you can use to organize the first CUSTOM sheet into the order you want. Just list the items in column A in the order you wish to see them. The CONCATIF() formulas in the other columns will gather the matching items from CUSTOM sheet and display them altogether.

I also added a worksheet_activate event to that Custom2 sheet so the rows/columns autofit to display as good as possible each time you look at that sheet.

I chose these solutions each step in an attempt to satisfy your wants in terms of layout and my wants in terms of not creating a sheet that grinds to a snail's pace with array-crunching formulas all over the place. That's typically how these things are accomplished and on larger datasets can be truly unweildy.

So to get the items back together in an organized way on Custom2 without introducing too-hungry array formulas seemed best accomplished with the UDF we used.

Now, having CUSTOM or CUSTOM2 organize themselves into a pre-configured list where the list expands/contract on its own, I can't fathom any way without putting those arrays right back in, ugh and more ugh.

The UDF at least keeps it all in one spot and is evaluated one time and one way, always appearing in the same spot.

So, the short answer is, yes it probably could be done, but not without some rethinking of everything you've got so far.

Excel is great at number crunching, but this evaluation of pages of text strings really slows down the plumbing. Perhaps this whole thing would be simpler and more manageable in Access.

========EDIT:
Any chance you'd reconsider this multi-sheet thing and put it altogether in a single "Database" sheet. That would give us back a lot of power and options not having to try and span ordered answers of multiple sheets.

I did all that work taking out those merged cells to give you a parsable data set and now they're all back in again.

Database = 1 row is a complete and separate data line, it includes all the pertinent information for the record. 1 column represents 1 field. Merged fields are for not for databases.

This one's almost effortless compared to the other.

============
EDIT:
On the other hand, switching to this format also opens up the possibility of using an AUTOFILTER. If you turn on the DATA > FILTER > AUTOFILTER and then filter on column A for "x" only, it gives you the same result as the custom sheet, except you don't need the custom sheet to show it.