Forum Discussion
Solved
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 i...
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 !!
