Create table for seperate worksheet data

[RobSA]

Hi Folks,

I have a standard excel sheet which allows team members to enter data from several sites.

My challenge is the consoildation of this data to a single worksheet in sequential rows.

Is there perhaps someone out there that can provide advice / example?

Regards
Rob

 

[Lymm]

Hi Rob , If you provide sample worksheets of the data before and after Im sure one of the Ninjas will be able to suggest a method to help you do this. They need to know how your data is laid out and what you want in the consolidated sheet.

 

[RobSA]

Hi Rob , If you provide sample worksheets of the data before and after Im sure one of the Ninjas will be able to suggest a method to help you do this. They need to know how your data is laid out and what you want in the consolidated sheet.

 

[SirJB7]

Hi, RobSA!

Give a look at this:
http://chandoo.org/forum/threads/me...ets-in-a-master-sheet.11074/page-3#post-91617

It consolidates all worksheets on a workbook except summary worksheet, copying into this last one all the info of the other worksheets, one below another.

If it helps, just advise if any issue.

Regards!

PS: Pointed to that specific post since the whole thread is very large.

 

[RobSA]

Thanks for the reply. The Macro button works well.

It however does not perform the function I had intended and that is when I receive monthly data in a form as seen in the JAN14 tab, the data that is entered for the "this month" column as well as the "workplace detail" be captured in rows forming a new data worksheet without duplicate information.

Currently I am doing most of this by hand which makes room for error.

Regards
Rob

 

[SirJB7]

Hi, RobSA!

Giving a look at your sample file, if all the related worksheets between START and END worksheets have the same layout, then you can try what follows. Check the uploaded file.

A5: =FECHA(2014;FILA()-4;1) -----> in english: =DATE(2014,ROW()-4,1)
B5: =SI.ERROR(INDIRECTO(MAYUSC(TEXTO($A5;"mmmyy")&"!D"&COLUMNA()+2));"") -----> in english: =IFERROR(INDIRECT(UPPER(TEXT($A5,"mmmyy")&"!D"&COLUMN()+2)),"")
Copy B5 across thru C5:D5.
F5: =SI.ERROR(INDIRECTO(MAYUSC(TEXTO($A5;"mmmyy")&"!D"&COLUMNA()+3));"") -----> in english: =IFERROR(INDIRECT(UPPER(TEXT($A5,"mmmyy")&"!D"&COLUMN()+3)),"")
Copy F5 across thru G5:AQ5.
Then copy A5:AQ5 down thru 6:16.

What's E column (yellow shaded)? The formula it's a SUM of a cell with text data. Not considered.

The main constraint is that you have to name your worksheets as the name of the months in the same language as your regional configuration; that'd become a problem when sending the workbook overseas, for example.

Just advise if any issue.

Regards!

 

[RobSA]

Dear SirJB7,

Thank you for your response and the work done so far in adding value to the proposed final product.

I have uploaded a revised version of your file and made some comments within that may help us in going forward.

I look forward to your replies.

Regards
Rob

 

[SirJB7]

Hi, RobSA!

My comments to your uploaded and slightly updated file:

a) Column changes.
I replaced obsolete column E by 2 columns, so as to keep only one formula for all columns, since the difference between earlier B:D vs F:AQ it was only a "+2" vs a "+3". Then I hid actual E:F columns.

b) What worksheets to include or exclude.
I don't like too much your idea of including worksheets to be considered within 2 empty (or not, it's the same) worksheets, since a user can unwillingly drag and drop worksheets from their tabs and changing it's inclusion status. Besides in this case what will rule if a worksheet is considered or not are 2 things:
- entries in column A
- it's name, which is built from entries in column A
So no matter if the worksheet is within the range START-END if it doesn't fulfill these 2 conditions (about this, for example I don't get displayed anything for January, as the worksheet is named JAN14 because of English January but in Spanish it's Enero, so row 5 formulas are pointing to worksheet ENE14 that doesn't exist and doesn't find worksheet JAN14... this is regarding my earlier comment about names vs. regional settings).

c) Protection.
I'd protect the worksheet EXAMPLE leaving only unlocked cells where users could enter data, but nothing more. I'd also do that with summary worksheet, leaving only unlocked column A data and the 2 cells of e).

d) Which worksheets to process.
I'd add a command button in summary worksheet to create non existing worksheets for entered dates at column A, copying the worksheet TEMPLATE with the proper name. If you do this, you can get rid of the range START-END and of both worksheets.
If not then you don't have the chance to control the correct naming convention for the worksheets and you leave more manual work to the end user.

e) Parameters.
There should be 2 cells holding start date and end date to select which period you're going to process, let us say B1 and B2. More indeed, if you go with this alternative, then these 2 should be the only modifiable cells and no more those of column A, which will be created if they don't exist for the whole months range.

Regards!

 

[RobSA]

Dear SirJB7,

Thank you for your prompt reply.

Your advice is appreciated.

In reply I have:

1. Removed the column E that was in previous sheets and changed the formula "=IFERROR(INDIRECT(UPPER(TEXT($A5;"mmmyy")&"!D"&COLUMN()+4));"")"
2. Removed the sheet "START" & "END"
3. Changing the TAB numbers to avoid conflict across international boundaries is a consideration but the formula "mmmyy" would have to change to something that would suit say something numerical (i.e. 1,2,3,4,,etc)
4. The example sheet is deleted removed.

Regards
Rob

 

[SirJB7]

Hi, RobSA!

I had time to play a bit with your file and did this:
a) Kept changes of previous a), 2 hidden columns E:F
b) Kept parameters Date Start and Date End of previous e), and added WS Format
c) Set up protection for all cells in summary worksheet except those of b)
d) There added 3 buttons, Create Missing, Delete Residual and Clear All
e) For template worksheet, set up protection for all non-editable cells
f) For template worksheet, defined 2 named ranges with worksheet scope for the form and the date (K14)
g) There added 1 button, Clear form

How does this work?
- at open and close time all worksheets get protected
- worksheet details: 1 summary, 1 template, n monthly
- n monthly are tied to start/end dates
- green button Create Missing copies template worksheet as YYYY-MM and sets date (K14) to 01/MM/YYYY for each month in start/end dates range, and adds rows properly in summary table
- red button Delete residual delete worksheets with names out of start/end dates range, and deletes rows properly in summary table
- yellow button Clear All clears editable cells in all monthly worksheets

I almost forget about this: Excel security is like Santa, Batman or Spiderman, just exists in the movies. But an anti-dumb/curious/newbie unwilling workbook spoil can be prevented password protecting the worksheets, and in this case the VBA project too (where the actual password for the worksheets is stored). I used the name of that mountain with three volcanic cones Kibo, Mawenzi and Shira. In capital letters.

Once you find out it (I guess it'll be easy for you), if you want to change it proceed as follows:
- from the VBA editor, immediate window, type "UnprotectWS"
- from the VBA editor, left panel, VBA project, properties, protection, check block and enter new password.

I think that I'm not missing anything, but the code. Here it is:

Option Explicit

' global constants
'  security
Const gksPassword = "Kilimanjaro"
'  worksheets
Const gksWSSummary = "RETURN CONSOLIDATED"
Const gksWSTemplate = "Template"
'  ranges
Const gksRngDateStart = "DateStartCell"
Const gksRngDateEnd = "DateEndCell"
Const gksRngWSFormat = "WSFormatCell"
Const gksRngSummary = "SummaryTable"
Const gksRngDate = "DateList"
Const gksRngTemplate = "TemplateTable"
Const gksRngReportMonth = "ReportMonthCell"

' global declarations
'  ranges
Dim grngS As Range, grngD As Range, grngT As Range
'  dates
Dim gdStart As Date, gdEnd As Date, gsFormat As String

Sub CreateMissing()
    ' constants
    ' declarations
    Dim rng As Range
    Dim I As Integer, A As String, D As Date
    ' start
    Set grngS = Worksheets(gksWSSummary).Range(gksRngSummary)
    Set grngD = Worksheets(gksWSSummary).Range(gksRngDate)
    gdStart = Worksheets(gksWSSummary).Range(gksRngDateStart).Value
    gdEnd = Worksheets(gksWSSummary).Range(gksRngDateEnd).Value
    gsFormat = Worksheets(gksWSSummary).Range(gksRngWSFormat).Value
    UnprotectWS gksWSSummary
    ' process
    '  main
    D = gdStart - Day(gdStart) + 1
    Do While D <= DateSerial(Year(gdEnd), Month(gdEnd), 1)
        ' date
        A = Format(D, gsFormat)
        ' ws
        Set rng = Nothing
        On Error Resume Next
        Set rng = Worksheets(A).Cells(1, 1)
        On Error GoTo 0
        If rng Is Nothing Then
            Worksheets(gksWSTemplate).Copy After:=Worksheets(Worksheets.Count)
            With ActiveSheet
                .Name = A
                .Range(gksRngReportMonth).Value = D
                .[D4].Select
            End With
        End If
        ' table row
        UnprotectWS A
        With grngS
            For I = 2 To .Rows.Count
                If .Cells(I, 1).Value = D Then Exit For
            Next I
            If I > .Rows.Count Then
                If .Rows.Count > 1 Then .Rows(2).Copy .Rows(I)
                .Cells(I, 1).Value = D
                Set grngS = .Resize(.Rows.Count + 1)
            End If
        End With
        ProtectWS A
        ' cycle
        D = DateSerial(Year(D), Month(D) + 1, 1)
    Loop
    '  sort
    Set grngD = grngD.Resize(grngS.Rows.Count)
    With grngD.Parent
        With .Sort
            With .SortFields
                .Clear
                .Add Key:=grngD, SortOn:=xlSortOnValues, Order:=xlAscending, _
                    DataOption:=xlSortNormal
            End With
            .SetRange grngD
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
    ' end
    With Worksheets(gksWSSummary)
        .Activate
        .Cells(1, 1).Select
    End With
    ProtectWS gksWSSummary
    Set rng = Nothing
    Set grngD = Nothing
    Set grngS = Nothing
    Beep
End Sub

Sub DeleteResidual()
    ' constants
    Const ksSeparator = "-"
    ' declarations
    Dim I As Integer, J As Integer, D As Date
    ' start
    Set grngS = Worksheets(gksWSSummary).Range(gksRngSummary)
    gdStart = Worksheets(gksWSSummary).Range(gksRngDateStart).Value
    gdEnd = Worksheets(gksWSSummary).Range(gksRngDateEnd).Value
    UnprotectWS gksWSSummary
    ' process
    For I = Worksheets.Count To 1 Step -1
        With Worksheets(I)
            If .Name <> gksWSSummary And .Name <> gksWSTemplate Then
                ' date
                D = DateValue(.Name & ksSeparator & 1)
                If D < gdStart - Day(gdStart) + 1 Or D > gdEnd - Day(gdEnd) + 1 Then
                    ' ws
                    Application.DisplayAlerts = False
                    .Delete
                    Application.DisplayAlerts = True
                    ' table row
                    For J = 2 To grngS.Rows.Count
                        If grngS.Cells(J, 1).Value = D Then Exit For
                    Next J
                    If J <= grngS.Rows.Count Then
                        If grngS.Rows.Count > 2 Then
                            grngS.Rows(J).Delete Shift:=xlShiftUp
                        Else
                            grngS.Cells(J, 1).ClearContents
                        End If
                    End If
                End If
            End If
        End With
    Next I
    ' end
    Worksheets(gksWSSummary).Activate
    ProtectWS gksWSSummary
    Set grngS = Nothing
    Beep
End Sub

Sub ClearAll()
    ' constants
    ' declarations
    ' start
    ' process
    ClearWS
    ' end
    Beep
End Sub

Sub ClearWS(Optional pvWS As Variant)
    ' constants
    ' declarations
    Dim sWS As String
    Dim I As Integer
    ' start
    If IsMissing(pvWS) Then sWS = "" Else sWS = CStr(pvWS)
    ' process
    For I = 1 To Worksheets.Count
        With Worksheets(I)
            If (sWS = "" Or sWS = .Name) And _
                .Name <> gksWSSummary And .Name <> gksWSTemplate Then
                UnprotectWS .Name
                Union(.[D4:D6], .[K4:K6], .[D9:F46], .[G10], .[G21], .[G27], _
                    .[G34], .[G38], .[C54:L58]).Select
                Selection.ClearContents
                .[D4].Select
                ProtectWS .Name
            End If
        End With
    Next I
    ' end
    Beep
End Sub

Sub ProtectWS(Optional pvWS As Variant)
    ' constants
    ' declarations
    Dim sWS As String
    Dim I As Integer
    ' start
    If IsMissing(pvWS) Then sWS = "" Else sWS = CStr(pvWS)
    ' process
    For I = 1 To Worksheets.Count
        With Worksheets(I)
            If (sWS = "" Or sWS = .Name) Then
                Worksheets(I).Protect gksPassword
            End If
        End With
    Next I
    ' end
    If sWS = "" Then Beep
End Sub

Sub UnprotectWS(Optional pvWS As Variant)
    ' constants
    ' declarations
    Dim sWS As String
    Dim I As Integer
    ' start
    If IsMissing(pvWS) Then sWS = "" Else sWS = CStr(pvWS)
    ' process
    For I = 1 To Worksheets.Count
        With Worksheets(I)
            If (sWS = "" Or sWS = .Name) Then
                Worksheets(I).Unprotect gksPassword
            End If
        End With
    Next I
    ' end
    If sWS = "" Then Beep
End Sub

Give a look at it and just advise if any issue or ask if any doubt.

Regards!

 

[RobSA]

Dear SirJB7,

Absolutely amazing how you have put this all together in so little time - WELL DONE.

If I may make some comment:

1. The TABS are not the prescribing determinants for the information found in column A "Report Month". The report month information is provided by the inputs provided in the form, in combined cells (K7:L7)
2. Some background on how this will all work.
3. The form as you know it is presented to several people on different projects and they enter the data in the unprotected cells. On completion and on a monthly basis they submit the completed form to me for consolidation into a larger report.
4. Currently I take this information from each site and manually retype the data and that is just to time consuming, hence the thought of simplification.
5. The data is then gathered into a worksheet that presents as we are doing on the "Return Consolidated" Worksheet. The reason why we cannot have dates in the TABS is that there will be several entries for a month going forward, so the TABS should have a numerical value, say 1,2,3,etc.
6. The consolidated data is then sorted by Project and then by month, so I would then see the various data received over the months under each project.
7. The data is then presented in a separate form that will reflect the annual rolling month - so taking all the data for a year between MAR2013 and APR2014 (12 months)

I hope this provides some inputs to my challenge. IF you wish me to send my current full worksheet I'd be more than happy to send privately.

Thank you so much for your support.

Regards
Rob

 

[SirJB7]

Hi, RobSA!

About your points:
1. Which names should the tabs have? Aren't they related to cell K4 (and not K7:L7) or each form?
2. Let us see...
3. You send a worksheet with the form to each people, they fill it and send back to you. How? By workbook with one worksheet, if so what are the WB and WS names?
4. What do you mean by taking the info from each site? Isn't that automatable via web query or web service (2013 version?)
5. This macro should do the job.
6. Will require an actual file.
7. APR2013 - MAR2014, I guess. Same as 6.

I'll start a conversation with you.

Regards!