Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Help summarizing 12 months data

0

Hi Guys,

I have probably simple problem if you can help. 

- I have to summarize the 12 months data on one worksheet
- I have twelve monthly worksheets in a spreadsheet (I have attached two months + summary sheet as a sample in the file)
- Currently I am manually linking each figure
- I want a formula or a macro that go to each month spreadsheet and copy the relevant numbers e.g. subscription or redemptions and include it on the summary tab
- I am not sure if I will make excel find the heading in the very first column and then ask to fetch a number in specific column ? If you can suggest that a formula / code goes to each month worksheet search for two citieria e.g. column heading and row label (Class A & Additions) and return a number in the summary sheet from a specific column
- If you can tell me pattern for one figure say Additions for two months I will be able to replicate it for 12 months



Many thanks !

Answer
Discuss

Discussion

Can you combine all months data onto one worksheet? Or import each new month onto the same sheet? Then you could use relatively simple formulas to pull the data into the Summary sheet.
don (rep: 1989) Sep 7, '16 at 3:20 pm
Add to Discussion

Answers

1

This info comes a bit late, you may have already solved you problem, but here's my two-cents. In the file you provided I noticed many inconsistencies in the formulas. The worst cases were for the Class A - Series Y data. The formulas were referencing columns F,G, and N but on the Feb and Mar sheets included those columns don't exist. You also had many formulas grabbing data from another location (Conference Room\Downloads). Your formulas for YTD Row 27 and YTD Row 44 are different? Don't understand why? I feel they should be as I have them in Row 27. I have amended the formulas for Feb and Mar for the Class A and the Class A - Series Y. The entries on the monthly sheets appear to be manual entries - correct? I have attached your file with the changes I made. As it is now it is a stand-alone file and not dependant on data in another source (file). If this is what you were looking to achieve, just amend the formulas for the other months to pattern Feb & Mar. (The formulas in Rows 7 thru10 show a #REF error so I have not touched these)

Hope this helps.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login