Need to consolidate Daily Data into a separate workbook

[exc3l3arn3r]

I am working on a sheet through which I can consolidate the daily data for the entire year (2016) into a one single sheet/separate workbook.

The working required here seems simple but the problem is I have a very large data set (date-wise) for the entire year in separate workbooks and from there I have to consolidate it one sheet.

I have attached the working sheet in the post for viewing, the data may not appear correctly by you may get the general idea of the formula I am using to get the data from each day's sheet.

The daily data is saved in separate excel files with file name of that day's respective date for example the data for 01-01-2016 will be saved under a file name of 01-01-2016.xls and the formula I am using to fetch the data in the consolidation sheet is

'E:\Desktop Files\Operations Statement\MIS\2016\Jan''16\[19-01-2016.xls]Sheet1'!$F$9

The problem here is, I have to manually edit each cell to change the date in the formula in order to fetch the data from that date's respective workbook. I am wondering if there is someway I can automate this process or use a different approach rather than manually editing the date in each cell.

'E:\Desktop Files\Operations Statement\MIS\2016\Jan''16\[19-01-2016.xls]Sheet1'!$F$9
The problem here is, I have to manually edit each cell to change the date in the formula in order to fetch the data from that date's respective workbook. I am wondering if there is someway I can automate this process or use a different approach rather than manually editing the date in each cell.

__________________________________________________________________

Mod edit : thread moved to appropriate forum !​

​

 

[jindon]

Untested

Sub test()
    Dim r As Range, myDir As String
    myDir = "'E:\Desktop Files\Operations Statement\MIS\2016\Jan''16\["
    With [b3].CurrentRegion
        For Each r In .Columns(.Columns.Count + 2).Cells
            If (IsDate(r(, -14))) * (r(, -1) <> 0) * (r(, -1) <> "") Then
                r.Formula = "=" & myDir & Format$(r(, -14).Value, "dd-mm-yyyy") & ".xls]sheet1'!$f$9"
            End If
        Next
    End With
End Sub

 

[PCosta87]

Hi @exc3l3arn3r

I believe you could avoid VBA by using a combination of indirect and text functions...
You could also use a line up top to reference the cell and just copy across

Please see attachment, line 7

Note: not really sure why but I just realized you need to open the workbook with the original data once, after the formulas are in place, for this to work

Regards

 

[jindon]

Revised

Sub test()
    Dim r As Range, myDir As String, i As Long
    myDir = "'E:\Desktop Files\Operations Statement\MIS\2016\Jan''16\["
    For Each r In Range("c4", Range("c" & Rows.Count).End(xlUp))
        If (r.Interior.ColorIndex = xlNone) * (Not r.Value Like "S*") Then
            For i = 1 To 11
                r(, i + 1).Formula = "=" & myDir & Format$(r(, 0).Value, "dd-mm-yyyy") & ".xls]sheet1'!$f$" & i + 6
            Next
            r(, 15).Formula = "=" & myDir & Format$(r(, 0).Value, "dd-mm-yyyy") & ".xls]sheet1'!$f$20"
        End If
    Next
End Sub

 

[Biplab4444]

Hi....
You may solve this problem by using Power Query. Power Query able to consolidate data from different workbooks and you may visualize as per your needs.
You only need to save all the workbook in a particular folder and Power Query automatically add records in Data Model.
Remember you need similar data to consolidate.