Forum Discussion
How to automatically extract data from a monthly report to a dataframe
I updated few cells in the data frame tab with a possible solution( which is only partial solution). I am assuming that you will have one row per month in Dataframe. if yes, you can update the first column "sheet name" - this is the tab where you will extract data. the cell references need to be updated in the formule in B2, C2, D2 etc (first row). for example for country i assumed that L3 cell in 2019JUN tab is the source, hence the formula in B2 is =IFERROR(INDIRECT($A2&"!L3"),"")
to complete this, you will have to populate the 1st row, with formula like in B2 and C2, then copy /paste these formula to the next rows. of course, the first column "sheet name" needs to be updated as well.
hope it helps !!
I updated few cells in the data frame tab with a possible solution( which is only partial solution). I am assuming that you will have one row per month in Dataframe. if yes, you can update the first column "sheet name" - this is the tab where you will extract data. the cell references need to be updated in the formule in B2, C2, D2 etc (first row). for example for country i assumed that L3 cell in 2019JUN tab is the source, hence the formula in B2 is =IFERROR(INDIRECT($A2&"!L3"),"")
to complete this, you will have to populate the 1st row, with formula like in B2 and C2, then copy /paste these formula to the next rows. of course, the first column "sheet name" needs to be updated as well.
hope it helps !!
Kodipady Thank you so much!
This is a very elegant solution, and a useful tool to have!
Yesterday, I had written a VBA to accomplished a similar task, but this method is easier
and more convenient.
Thanks!
Here is what I did with the VBA.
I created a Variable input for the sheet name, and made the macros
copy the data onto the next open line.
Private Sub Plugdata()
'#Define Variables
Dim Reportname As String, Country As String, Year As Integer, Month As String, Regular_EMP As Integer
Reportname = InputBox("Type the Report Name eg. 2017Jan", "Type Report Name", "Type Report Name Here")
Worksheets(Reportname).Select
Country = Range("L3")
Year = Range("H1")
Month = Range("G1")
Regular_EMP = Range("D10")
'#Select Paste Location
Worksheets("Data").Select
Worksheets("Data").Range("A1").Select
If Worksheets("Data").Range("A1").Offset(1, 0) <> "" Then
Worksheets("Data").Range("A1").End(xlDown).Select
End If
'#Enter Data
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = Country
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Year
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Month
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Regular_EMP
End Sub