-
Getting values from specific cells in a dynamic range
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules