Filling a table automatically

**Gaz_m2k5** · 08-31-2011, 05:39 AM

On sheet 1 I have a table with columns
Test Date Result

On multiple other sheets I have tables with
Date Result

Each sheet from 2 onwards, is for a different test. Sheet 1 is a summary of all the tests.

I'd like the table from Sheet 1 (the summary) to pull in data in chronological order from all the other tests. Also to get the type of test from the top of sheets 2+ to put in the "test" column on sheet 1.

Is this possible?

**Domski** · 08-31-2011, 05:46 AM

Sure but you would need to use VBA. If you could post a sample workbook showing the layout and desired result I'll have a go at writing it.

Dom

**Gaz_m2k5** · 08-31-2011, 06:24 AM

Originally Posted by Domski

Sure but you would need to use VBA. If you could post a sample workbook showing the layout and desired result I'll have a go at writing it.

Dom

If you could that'd be awesome, but it might be a big job!

I've attached the sample file.

So what I want is for the Summary tab to pull in results from the other tabs, but the "result" column will have different formats.

For test 1, I'd like the summary tab to retrieve the "normalised result"
For test 2 it to get the result
For Test 3, I'd like the summary tab to make 2 rows, one for result 1 and one for result 2. I'd also like the "Test" column on the summary tab to read "Test 3 (Result 1)" or "Test 3 (Result 2)"
For Test 4 pull the result.
For test 5 I want it to put the "Time" colum in the summary tab's result column. I'd also like it to use the same format, ie ## "s"
For Test 6 the result
For Test 7 I'd like the summary tab's result column to display just as cell F4 (on Test 7 sheet) is displaying now.

And of course for each sheet I'd like it to retrieve the variant, date, and any notes, and all this information in chronological order.

It's asking a lot, so if you don't have time for all that I'd appreciate any help towards it!

**Domski** · 08-31-2011, 06:48 AM

It would have been quite straightforward if, as I wrongly assumed, all the test sheets were the same layout.

Have you covered all the possible variations of sheet layout in your example?

I'll have a look a bit later.

Dom

**Gaz_m2k5** · 08-31-2011, 07:09 AM

Originally Posted by Domski

It would have been quite straightforward if, as I wrongly assumed, all the test sheets were the same layout.

Have you covered all the possible variations of sheet layout in your example?

I'll have a look a bit later.

Dom

I'm usually alright at understanding a code once looking at it, so if I need to add or change anything I should be able to figure it out myself after looking at the code. I may well be adding more tests in later, but none of them should be any different to the examples I've given, so I can work off those if I need to add stuff.

There's a couple of things I forgot though:
If you need a rule to determine what goes into the summary table: Only add to summary table if a result is input
For test 1, if benchmark = 0, use "result" for the summary rather than "normalised result"

All these tests do get a bit of a mess with their different formats, hence why I'm trying to create a summary table to neaten them up! Thanks for any help you can give.

EDIT: If need be for test 7, instead of incorporating the format ("0.00 N / 0.00 N") into VBA code, you could just use column F and hide it, as I don't intend for that column to be shown on Sheet 7, only in the summary sheet.

EDIT2: Actually the code just needs to import the data to the table, it doesn't need to sort it by date. That can be done by highlighting the table and pressing filter, right? Should be easier, and then I can use filters and sort it differently if I need to.