Excel tabs Automation to overcome the manual efforts while applying formulas in Cells

[Bhupesh_Gupta]

Hi Folks

I have a Requirement where i have to fill the Data in Excel Output sheet using Multiple Tabs and output should be in same excel sheet just in different tab.
Requirement.
Sheet1 Input: Contains Monthly Total Income
Sheet2 Input: Contains Salary of each Team member
Sheet3 Input: Contains Other expense Details of each Team Member.

Now in Sheet 4 we need output based on Breakup in all 3 Input sheets.
Attached the excel.

can any Dev, Please suggest how i can start.

thanksReports.xlsx (23.1 KB)

@ImPratham45

[VB_THA (V.Bradescu)]

Hi @Bhupesh_Gupta

In my honest opinion, is more easier with a macro instead an robot.

Is need to be a robot to do the job?

[ppr (Peter Preuss)]

@Bhupesh_Gupta
have a look here for initial block parsing:

Dynamic Extraction of Data Blocks from Excel

@NAJAA_BAZILAH I worked out a sample for you showcasing on how an extraction could be done. With some alignments I made the implementation flexible and stressed it with this as well: [grafik] Extraction was possible for your and my tables. Constraints: no overlapping data within the cols. Does mean we can detect blocks and if a block starts on Column B, then no Block will start on Column C (Overlappings) Find XAML here: NAJAA_BAZILAH.xaml (16.0 KB) Excel Here: Book2_PPR.xlsx (12.6 KB…

extracting the different blocks can help to pull out the different portions needed in output 4

[Bhupesh_Gupta]

Yes we need robot as we have requirement

[Bhupesh_Gupta]

@ImPratham45 can you provide some suggestion

[ImPratham45 (Prathamesh Patil)]

Currently busy
will check nd let u know
i am not getting it by first look

[Bhupesh_Gupta]

@Bhupesh_Gupta

Requirement.
Sheet1 Input: Contains Monthly Total Income
Sheet2 Input: Contains Salary of each Team member
Sheet3 Input: Contains Other expense Details of each Team Member.

Output
Now in Sheet 4 we need output based on Breakup in all 3 Input sheets.

any .xaml suggestion for same
@ImPratham45 @ppr

[Bhupesh_Gupta]

If you get the chance or any suggestion @ImPratham45

[ImPratham45 (Prathamesh Patil)]

i am not getting the pattern …which data comes in output

[Bhupesh_Gupta]

Ok So the sheet4 (Output) is the output sheet

where In Column A and B is copy paste of Column A and C from Sheet 1(Input)

and Column C, D and E is calculated from Sheet 2(Input)

Row 5
Number of employees 12 3 5 4 is total of all teams

Other calculation i.e. Domestic, outside, training is calculated as
Calculation of Domestic income (Team1) = 24,00,000×3/12=600000
Calculation of Domestic income (Team2) = 24,00,000×5/12=1000000
Calculation of Domestic income (HR) = 24,00,000×4/12=800000

Attached the ReportsReports.xlsx (23.8 KB)

@ImPratham45

[ImPratham45 (Prathamesh Patil)]

will check nd let u know

[Bhupesh_Gupta]

Thank you @ImPratham45

[ImPratham45 (Prathamesh Patil)]

Is output sheet data is static???

[Bhupesh_Gupta]

Nope as it depends on Input sheet. I mean if there is increase in team members or maybe new team than the data should be based on that input. So for output sheet Data flow should be accordingly.

[ImPratham45 (Prathamesh Patil)]

No i am talking about row data

[Bhupesh_Gupta]

yep that is static

[ImPratham45 (Prathamesh Patil)]

Then u can directly use write cell and write range with little logic dear

[Bhupesh_Gupta]

But how i can manage the Teams that is coming from other tabs? any suggestion

[ImPratham45 (Prathamesh Patil)]

If this is continuation for previous then u have those list of temas ryt?

[Bhupesh_Gupta]

Yes thats ryt