Is what I'd like to do even possible?

[Ergocorp]

Hi, I've taken plenty of advise from this forum, so much so that I've only needed to register now, so thank you all.

I get data from external sources in the form of a table that has every month of a year. I'm looking for a way to automate the transfer of a specific month's sum to another spreadsheet, and then again with certain filters on. The table might increase in number of rows every month I receive it eg.

Name	Filter 1	Filter 2	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
1	a	x	5		5
2	a	y				2
3	a	z		3	2	3
4	a	z	1	2
5	b	z	5	4	5
6	b	y	2	2	2	2
7	b	x	4	1
8	a	x			4	5
Total			17	12	18	12

<tbody>
</tbody>

Is this even possible with VBA and a macro? Would I need to clean the table somehow?

Thanks.

 

[mumps]

How do you decide which month and where on the other sheet do you want to transfer the month's sum?

 

[Ergocorp]

I do a monthly report, so I get that data every month. In essence the data I get is the same as the previous month with an extra month. Because this is a report on the previous month, I'd want current month -1. I get 10 sets of data each a tiny bit different depending on their own systems and little quirks. I have an aggregation work book where I collate the data, sums up the filters across at 10 etc. I then have a master workbook that I transfer that data to using macros that forms a dashboard. I'm conscious I could lose the aggregation workbook and take the data directly, but one step at a time

 

[mumps]

To clarify: This is the month of May so you want to find the total for April from the row that has "Total" in column A. If this is correct, I'm still not clear on where you want to paste this total for April. Do you want to transfer the total to your master workbook? If so, what is the full name of the master workbook including extension (xlsx, xlsm), what is the name of the destination worksheet in the Master and in what cell in that sheet do you want the total?

 

[Ergocorp]

I'm not really able to broadcast that on the internet, so I've tried to as generic as possible. It's not just May for example. When I come to do the report for say September, I want to total up all the rows for that month, and for November's report to count up the rows for that one.

There's already a total, why don't I just use that? Not all of the external data sources use a total.


Would I just be better creating specific macros for each source, then doing some sort of aggregation macro? I think so. I'm waffling here as I think I'm coming up with a way to do it that way I want, sorry for wasting your time.

How do you mark this as answered?

 

[mumps]

Unfortunately, you can't mark the thread as solved in this Forum. Not to worry. You didn't waste my time.

 

[Ergocorp]

Ok, so I have been able to do some of the Formulae. Using INDIRECT and COUNT I was able to create a sum range that counts the number of rows, but I'm struggling when it comes to the column. Ignoring that data transfer to another work book element, assuming we don't have a total row, the above example would allow for a variable number of rows:

=SUM(G2:INDIRECT("G"&COUNT(A:A)))

However my next step is a variable column. The columns are headed with the month, as per the above example. I get a new version every month, increasing in row number (thus the need for that variable number), but filling out a new column of data. Si in this case, it was Apr. Replace 'G' with 'H' and you'd get May etc. Any one any idea how I pick out a specific column with a catch all macro?