hello all,
I am trying to help me finance department to consolidate the monthly report data.
they give me a sample or the monthly report data which contain 5 department and each department into 1 excel with multiple worksheet ((1)Profit and Lost /(2) Balance Sheet) for each department.
I have use power automate desktop to manage to get the multiple required cell from the execl and set it as different variables to loop in each worksheet with P&L and BalanceSheet for each month.
in my flow setting
(1)launch excel ,list of all the excel worksheet in the Monthly Report Data.(P&L_Department1,BS_Department1, P&L_Department2,BS_Department2,P&L_Department3,BS_Department3,P&L_Department4,BS_Department4,P&L_Department5,BS_Department5)
(2)For each Sheetname contain PL_, Run subflow For_Sheet_PL, For each Sheetname contain BS_, Run subflow For_Sheet_BS.
(3)For_Sheet_PL, i will get the latest month from row 3 column 1 and convert it to short month to find the column that contain Mar 2023. (This will alway increase to April May June july onwards.
4) I will then set variable for the Short month column so that when i search the wording from Column 1, i will then get the value from the cell matching the Mar 2023 column and the wording from column 1. for example. Column D, Row 9 and set VarSales.
5) this will be also done for all the other values i need from Mar 2023 Column.
now my question is how do i find and set in the consolidate excel report to put the value into the correct column for each month.
for example in this first picture is for PL sheet and it for Department1.
i need to put watever values that i have gotten it from the previous flows and add them into specific cells each month (Mar,April,May June etc.
in the same consolidated report will contain department2/department3/department4/department5
Thank you.
