Forum Discussion
How to automatically extract data from a monthly report to a dataframe
Hello,
I am a fairly new excel user on mac,
I receive monthly reports that all have the same format(but are not in a table).
I wanted to set up a data frame to automatically collect and store important data from these reports into a clean table.
I am able to copy these reports into one excel file as separate worksheets,
but I am unable to set up a system that would fill out each row with data from each worksheet(individual report).
Do you have any suggestions on how to set up this up?
or idea on better ways to set up a dataframe?
As suggested by Kodipady I have attached a sample report along with a rough draft of the data frame.
Thanks in advance!
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 !!
9 Replies
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 !!
