• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help summarizing 12 months data

F

ferocious12

Member
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

https://dl.dropboxusercontent.com/u/...0template.xlsx



Many thanks !
 
Wow. Thank you !

Can you pls explain this formula to me as I have to do this for number of classes located in different columns of monthly data. Is there a way to include finding class & then returning a value e.g. Class A, Class A Series Y upload_2016-9-7_9-50-14.png

=INDIRECT("'T2." & MONTH($A16 & 0) & " Allocation " & LEFT($A16, 3) & "'!" & ADDRESS(MATCH(G$14,'T2.2 Allocation Feb'!$B:$B,0),MATCH($A$13,'T2.2 Allocation Feb'!$6:$6,0)))
 
I was able to evaluate & change the formula for another class and worked fine !

Still need an explanation for the various components of the formula. Also can you please provide guidance to tweak the formula when I will add 12 months worksheets instead of 2. Does the worksheet name matters ?
 
Hi ,

The formula which has been used is :

=INDIRECT("'T2." & MONTH($A16 & 0) & " Allocation " & LEFT($A16, 3) & "'!" & ADDRESS(MATCH(G$14,'T2.2 Allocation Feb'!$B:$B,0),MATCH($A$13,'T2.2 Allocation Feb'!$6:$6,0)))

1. The two parts colored in magenta assume that all your worksheets have the same structure , with the row and column labels in the same locations.

Instead of the tab name which has been used , any other tab name could have been used.

2. Another assumption which has been made is that the tab names follow the same naming convention of the text T2.2 Allocation , followed by a three letter suffix signifying the month.

If you are going to use tab names which do not follow a convention , then the above formula will have to be changed ; you will need to have a list of tab names entered somewhere in your workbook , and the formula will have to make use of this list.

3. The formula is independent of the number of tabs ; even if you add tabs for the remaining months of the year , the formula will not need to be changed.

4. The part colored in blue needs to be changed to refer to the location which has the text Class A , Class A Series Y , and so on.

Narayan
 
Super ! I have two more months. The problem is that a class may be added during any month in the year so the column position is not fixed. Is it possible to make it dynamic so it will look at row 6 (that always contains names of the classes in each column) and find the corresponding value in row 15 of that column ?

I have added Apr & May tabs. In May tabs it is not picking up the right data as the column number is different for "Class A series Y".

Also I need to understand the changes I have to make if the names of the worksheets has different naming convention, If you can help there as well !

https://dl.dropboxusercontent.com/u/10995251/allocation template (1).xlsx
 
Hi ,

See the uploaded file.

The two things you have to ensure are :

1. All worksheet tabs follow an identical structure ; all row labels and column labels have to be in the same locations in every tab.

2. Every relevant tab name is entered in the separate tab which has been inserted for this purpose.

Narayan
 

Attachments

Many thanks Narayan !

I am using the following code to copy worksheets (with the same name) from multiple workbooks and save it in a new workbook.

I am getting series of screen alerts (few screenshots below)and I have to press Yes for all of them except for the first one. Is there anything I can do to the code to avoid all these screen alerts ?

Thanks
Sophia
upload_2016-9-9_12-41-50.png

upload_2016-9-9_12-41-57.png
upload_2016-9-9_12-42-7.png

Code: 
Sub CombineSheets()
    Dim sPath As String
    Dim sFname As String
    Dim wBk As Workbook
    Dim wSht As Variant

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    sPath = InputBox("Enter a full path to workbooks")
    ChDir sPath
    sFname = InputBox("Enter a filename pattern")
    sFname = Dir(sPath & "\" & sFname & ".xl*", vbNormal)
    wSht = InputBox("Enter a worksheet name to copy")
    Do Until sFname = ""
        Set wBk = Workbooks.Open(sFname)
        Windows(sFname).Activate
        Sheets(wSht).Copy Before:=ThisWorkbook.Sheets(1)
        wBk.Close False
        sFname = Dir()
    Loop
    ActiveWorkbook.Save
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
 
Hi ,

When you post fresh questions , start a new thread for each new question , unless the new question is in any way related to the thread in which you are posting it.

In the question you have posted , there are 2 problems , one of which relates to code , and the other which is unrelated to the code.

For the first one , you can use the statement :

Application.DisplayAlerts = False

at the start , and :

Application.DisplayAlerts = True

at the end ; this should ensure that you don't get the Yes / No prompts.

The second problem is that the named range has been given a name UIT2 , which can also refer to a worksheet cell ! That is the reason the prompt mentions _UIT2 ; in this case , the better option might be to manually rename the named range before you do the copy.

As far as possible , ensure that named ranges have slightly longer names , so that you are sure they cannot refer to worksheet cells.

Narayan
 
You must log in or register to reply here.
Back
Top