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 !!
If you could upload your worksheet with some sample and realistic test data, that will help you get a much quicker response.
Thank you for the suggestion!
I have upload a sample report and a very rough draft of what I imagine my database should look like.
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 I was wondering, is there a way to extract data from a cell that's not always in the same location?
For example, in the Sample report, the Expenditure can be found in D138, with the word "Expenditure" written right about it in D137. However, some reports come back with extra lines added above, which moves the Expenditure to D1XX (e.g. D143).
Once again, thank you for your help!
