PDA

View Full Version : Getting values from specific cells in a dynamic range



itsraghus
02-18-2019, 04:19 AM
Hello Team,

I am working on building an Automation ROI template.
I populating the savings details for each cycle dynamically. This is shown in table 2. The savings details after ever year need to be populated in table 1. In this example, number of years are 3 and number of cycles in a year are 5. The number of years and number of cycles are dynamic in nature.

I have marked these cell values in red.

The questions to be addressed are:
1. How to do this when my number of cycles are dynamic and changed based on total number of years and number of cycles in a year.
2. The savings for each year to be displayed depending on the number of years.
3. Totals in table 2 at the end of the table to be populated dynamically as well.

Table 1



A
B
C
D




Year 1
Year 1
Year 3


Savings per year
2250
135000
247500






A
B
C
D
E
F
G


Cycles
Automatable test cases
Manual Productivity
Automation Productivity
Manual Effort
Automation Effort
Savings


1
100
4
10
25
10
-78750


2
100
4
10
25
10
-33750


3
100
4
10
25
10
0


4
100
4
10
25
10
1140


5
100
4
10
25
10
2250


6
100
4
10
25
10
45000


7
100
4
10
25
10
67500


8
100
4
10
25
10
101250


9
100
4
10
25
10
123750


10
100
4
10
25
10
135000


11
100
4
10
25
10
157500


12
100
4
10
25
10
180000


13
100
4
10
25
10
205000


14
100
4
10
25
10
225000


15
100
4
10
25
10
247500


Totals










Request you to kindly provide your valuable inputs to solve this problem.

Many thanks in advance!!
Raghuram Kolli