PDA

View Full Version : Gathering data from one book for summary in another



Virginal
04-04-2012, 02:07 PM
I find myself out of my depth as I have never worked with more than one workbook before coupled with the fact that I am gathering data with variables.

Basically I have two workbooks Book1 which will display the output and contains the code and Book2 which which is a long list of sales data in a CSV file.

I have managed to open this and sort it by person and reverse date order. What I want to do is gather the data and produce an average of the last 30 sales of each individiual from Book2 and display it in Book 1.

I have no problem ascertaining the start position of each sales person and therefore calculating the cell references required, just averaging it using variables and displaying it in Book1.

All help and examples very welcome.

CatDaddy
04-05-2012, 12:49 PM
post sample workbooks for faster response!

Virginal
04-05-2012, 12:57 PM
Things have moved on a bit, I just need to define some average ranges with variables and the code below doesn't work, please help.

It's just meant to work through a long list of data blocks defined by column 8 changing. At that point I want to calculate the average of the first 20 entries in columns 12, 13, 14, 15, 16, 17 in that particular block.

I can achieve the sort and calculate the block start rows OK, just can't get the average definition right.


Option Base 1
Dim c(6)

Sub calc_averages()

For x = 1 To 10000
rowcount = 0

'test for change into new data block
Do While Cells(x + rowcount, 8) = Cells(x + rowcount + 1, 8)
rowcount = rowcount + 1
Loop

'get average of first 20 rows in columns 12,13,14,15,16 & 17 into C()
For z = 1 To 6
c(z) = average(Cells(x, 12 + z - 1), Cells(x + 20, 12 + z - 1))
Next z

x = x + rowcount

Next x

End Sub