creating a result table in separate sheet

**WasWodge** · 10-19-2010, 04:01 PM

If anyone can I would like some help with something I have seen done before but have no idea where to start from.
I am looking to create a VBA for the following;

On the attached workbook i have simplified the task needed,
On sheet 1 is some data that would be downloaded from an outside source. in this case name and No of moves.
On sheet 2 is a table with the name and colour associated with that name.
What I need the Vba to do is add the No of moves together for each each user to give a total, put in the associated colour and do a total numvber of moves for each colour in a table in sheet 3.
I have put in an example end result in sheet 3 just in case I haven't been iotally clear what I am trying to learn to do.
If anyone could point me in the right direction it would be very much appreciated

**TMS** · 10-19-2010, 05:47 PM

I'm not entirely sure where you want your table so I'll just generalise.

You can get a list of unique entries (names) using Advanced Filter and selecting unique and copy to a new area.

Getting the total number of moves is a straightforward SUMIF.

And you can probably use Conditional Formatting to apply the appropriate colours. Unless you mean you want the word added to the table, in which case you can use VLOOKUP.

Regards

**WasWodge** · 10-19-2010, 06:44 PM

Sorry I was hoping I had provided enough information while keeping it concise but I will need to elaborate a bit more.The table needs to appear in sheet 3, The table currently there is just an example of the layout. It needs to appear in a separate sheet as it willl eventually posted on a separate server (The rest of the data is purely for the user, as I said in my first post sheet 1 is an imported sheet). all that there will be in sheet 3 to start with is the blank table layout.The column labelled work will eventually be 15 columns of various activities by the user thoughout the day and the colours will in reality be differnt department names. the table in sheet 3 will be the overall summary by user for the day/week. Obviously (as far as I know) there isn't a way to filter accross different sheets.

What I need to to do from a cell in sheet 3 (say D6) is lookup the table in sheet one and retrieve the names (Column b) and create a single entry in sheet 3 (D6), then in this example cell E6 needs to lookup the name in D6 and add all the values against that name in column C OF Sheet 1 to produce a total in sheet 3 E6. E7 (sheet3) needs to lookup the name in D6 (sheet3)., find that name in column B (sheet 2) lookup the appropiate value in column C and return that value to F6 (sheet 3).

The bits from row 11 onwards in sheet 3 I should be able to extract from everything else above once it is in sheet 3.
I am afraid my VBA skills are virtually limited to recording at this stage and so any pointers to where to look for any of the actions I need to carry out above would be of great help.
I hope I haven't made what I am asking for more confusing.

**TMS** · 10-20-2010, 02:23 AM

I rather foolishly didn't look as far as Sheet3.

E6: =SUMIF(Sheet1!B5:B20,D6,Sheet1!C5:C20)

F6: =VLOOKUP(D6,Sheet2!$B$6:$C$9,2,FALSE)

E11: =SUMIF(F6:F9,D11,E6:E9)

and copy each formula down

Regards