Hi,
I have been goggling a lot for the last many days to find a specific Excel solution for the project I am working right now that needs to be finish within a week. I hope forum members will be kind enough to help me on this. And to be frank, I am more of a reader than poster but this time; I couldn't find the solution anywhere.
I have 3 spreadsheets, namely Project List on spreadsheet1, Standard Project Activities on spreadsheet2 and Calender Based Project Name and Activities on spreadsheet3. Following 3 are the sample of my spreadsheets (sorry, it's quite lengthy).
Spreadsheet1:
Excel 2010
<tbody>
</tbody>
<tbody>
</tbody>
Spreadsheet2:
Excel 2010
<tbody>
</tbody>
<tbody>
</tbody>
Spreadsheet3 (To save space, I have limited the year but it does goes much beyong that: at least 30 years):
Excel 2010
<tbody>
</tbody>
<tbody>
</tbody>
Spreadsheet 1 and 2's values are always manually inputted. Activities in Spreadsheet2 are standard and are applicable to all the projects listed in Spreadsheet1.
Now, what I am looking for is: once one of the drop down list of projects in cell A3 in Spreadsheet3 is selected, the concern project number, year started and project area need to be populated from the speadsheet1 data (which I actually did with Data Validation and Vlookup formula; advise me if there are better choices), and cell range A3 to Y31 from Spreadsheet2 needs to be automatically populated in relevant cells in Spreadsheet3.
Filling of cell range in spreadheet3 will not be contiguous as opposed to in spreadsheet2. Cell range from A3 to B31 and C3 to Y31 of Spreadsheet2 needs to be populated in Spreadsheet3's cell range A3 to B31 and depending on value in D2 of SS3 (Year started) from cell F3 to AB31.
Please help me find the solution.
My other concern is, is it possible with above spreadsheets to keep records of all the activities of concern project and save those changes. I need to document which activities are done and how many of proposed activities' frequencies are executed (it may vary sometimes)?
Sorry for the lengthy first post and redundancy, if any. Just trying to present my situation as clearly as possible.
Many thanks in advance
I have been goggling a lot for the last many days to find a specific Excel solution for the project I am working right now that needs to be finish within a week. I hope forum members will be kind enough to help me on this. And to be frank, I am more of a reader than poster but this time; I couldn't find the solution anywhere.
I have 3 spreadsheets, namely Project List on spreadsheet1, Standard Project Activities on spreadsheet2 and Calender Based Project Name and Activities on spreadsheet3. Following 3 are the sample of my spreadsheets (sorry, it's quite lengthy).
Spreadsheet1:
Excel 2010
| Row\Col | A | B | C | D | E |
|---|---|---|---|---|---|
1 | Sl. No. | Project name | Project no. | Year Started | Total Area |
2 | 1 | Project 1 | 275500 | 2005 | 11.50 |
3 | 2 | project 2 | 295839 | 2006 | 12.42 |
4 | 3 | Project 3 | 295839 | 2006 | 118.00 |
5 | 4 | Project 4 | 3955 | 2010 | 89.43 |
6 | 5 | Project 5 | 4184 | 2011 | 32.12 |
7 | 6 | Project 6 | 988 | 2011 | 38.00 |
8 | 7 | Project 7 | 1042 | 2011 | 34.20 |
9 | 8 | Project 8 | 4536 | 2015 | 61.80 |
10 | |||||
11 | Total | 397.47 |
<tbody>
</tbody>
| Sheet: Mr Excel Sample1 |
<tbody>
</tbody>
Spreadsheet2:
Excel 2010
| Row\Col | A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | Activities | Activities frequency per management year | |||||||||||||||||||||||
2 | -1 | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | ||
3 | 1 | Pre-establishment: | |||||||||||||||||||||||
4 | 1.1 | Activities 1 | 1 | ||||||||||||||||||||||
5 | 1.2 | Activities 2 | 1 | ||||||||||||||||||||||
6 | 1.3 | Activities 3 | 1 | ||||||||||||||||||||||
7 | 1.4 | Activities 4 | 1 | ||||||||||||||||||||||
8 | 1.5 | Activities 5 | 1 | ||||||||||||||||||||||
9 | 1.6 | Others (specify) | |||||||||||||||||||||||
10 | 2 | Establishment: | |||||||||||||||||||||||
11 | 2.1 | Activities 1 | 1 | ||||||||||||||||||||||
12 | 2.2 | Activities 2 | 1 | ||||||||||||||||||||||
13 | 2.3 | Activities 3 | 1 | ||||||||||||||||||||||
14 | 2.4 | Activities 4 | 1 | ||||||||||||||||||||||
15 | 2.5 | Activities 5 | 1 | ||||||||||||||||||||||
16 | 2.6 | Activities 6 | R | ||||||||||||||||||||||
17 | 2.7 | Activities 7 | 1 | ||||||||||||||||||||||
18 | 2.8 | Activities 8 | 1 | ||||||||||||||||||||||
19 | 2.9 | Others (specify) | |||||||||||||||||||||||
20 | 3 | Post-establishment: | |||||||||||||||||||||||
21 | 3.1 | Activities 1 | 1 | ? | |||||||||||||||||||||
22 | 3.2 | Activities 2 | 3 | 3 | 2 | 1 | |||||||||||||||||||
23 | 3.3 | Activities 3 | ? | 1 | ? | ? | 1 | ? | 1 | 1 | 1 | ||||||||||||||
24 | 3.3.1 | Activities 3.1 | 1 | 1 | 1 | 1 | 1 | ||||||||||||||||||
25 | 3.3.2 | Activities 3.2 | 1 | 1 | 1 | 1 | 1 | ||||||||||||||||||
26 | 3.3.3 | Activities 3.3 | 1 | 1 | 1 | 1 | 1 | ||||||||||||||||||
27 | 3.3.4 | Activities 3.4 | 1 | 1 | 1 | 1 | 1 | ||||||||||||||||||
28 | 3.3.5 | Activities 3.5 | 1 | 1 | 1 | 1 | 1 | ||||||||||||||||||
29 | 3.4 | Activities 4 | 1 | 1 | 1 | 1 | |||||||||||||||||||
30 | 3.5 | Activities 5 | |||||||||||||||||||||||
31 | 2.6 | Others (specify) | |||||||||||||||||||||||
32 | |||||||||||||||||||||||||
33 | Legend: | ||||||||||||||||||||||||
34 | R = Replaceable with mechanic/manual work | ||||||||||||||||||||||||
35 | ? = Depends on the situation |
<tbody>
</tbody>
| Sheet: Mr Excel Sample2 |
<tbody>
</tbody>
Spreadsheet3 (To save space, I have limited the year but it does goes much beyong that: at least 30 years):
Excel 2010
| Row\Col | A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | Project name & Activities | Project number | Year started | Project area (ha.) | 2003 | 2004 | 2005 | 2006 | 2007 | 2008 | 2009 | 2010 | 2011 | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | 2023 | 2024 | 2025 | |
2 | Project 1 | 275500 | 2005 | 12.00 |
<tbody>
</tbody>
| Sheet: Mr Excel Sample3 |
<tbody>
</tbody>
Spreadsheet 1 and 2's values are always manually inputted. Activities in Spreadsheet2 are standard and are applicable to all the projects listed in Spreadsheet1.
Now, what I am looking for is: once one of the drop down list of projects in cell A3 in Spreadsheet3 is selected, the concern project number, year started and project area need to be populated from the speadsheet1 data (which I actually did with Data Validation and Vlookup formula; advise me if there are better choices), and cell range A3 to Y31 from Spreadsheet2 needs to be automatically populated in relevant cells in Spreadsheet3.
Filling of cell range in spreadheet3 will not be contiguous as opposed to in spreadsheet2. Cell range from A3 to B31 and C3 to Y31 of Spreadsheet2 needs to be populated in Spreadsheet3's cell range A3 to B31 and depending on value in D2 of SS3 (Year started) from cell F3 to AB31.
Please help me find the solution.
My other concern is, is it possible with above spreadsheets to keep records of all the activities of concern project and save those changes. I need to document which activities are done and how many of proposed activities' frequencies are executed (it may vary sometimes)?
Sorry for the lengthy first post and redundancy, if any. Just trying to present my situation as clearly as possible.
Many thanks in advance
