Populate (or copy and paste) range of cells to another sheet according to value of a cell

[twd154]

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
[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH]_Row\^Col[/TH]
[TH]

A​

[/TH]
[TH]

B​

[/TH]
[TH]

C​

[/TH]
[TH]

D​

[/TH]
[TH]

E​

[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

1​

[/TD]
[TD="bgcolor: #DCE6F1"]Sl. No.[/TD]
[TD="bgcolor: #DCE6F1"]Project name[/TD]
[TD="bgcolor: #DCE6F1"]Project no.[/TD]
[TD="bgcolor: #DCE6F1"]Year Started[/TD]
[TD="bgcolor: #DCE6F1"]Total Area[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

2​

[/TD]
[TD="bgcolor: #B8CCE4"]

1​

[/TD]
[TD="bgcolor: #B8CCE4"]Project 1[/TD]
[TD="bgcolor: #B8CCE4"]

275500​

[/TD]
[TD="bgcolor: #B8CCE4"]

2005​

[/TD]
[TD="bgcolor: #B8CCE4"]

11.50​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

3​

[/TD]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD="bgcolor: #DCE6F1"]project 2[/TD]
[TD="bgcolor: #DCE6F1"]

295839​

[/TD]
[TD="bgcolor: #DCE6F1"]

2006​

[/TD]
[TD="bgcolor: #DCE6F1"]

12.42​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

4​

[/TD]
[TD="bgcolor: #B8CCE4"]

3​

[/TD]
[TD="bgcolor: #B8CCE4"]Project 3[/TD]
[TD="bgcolor: #B8CCE4"]

295839​

[/TD]
[TD="bgcolor: #B8CCE4"]

2006​

[/TD]
[TD="bgcolor: #B8CCE4"]

118.00​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

5​

[/TD]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD="bgcolor: #DCE6F1"]Project 4[/TD]
[TD="bgcolor: #DCE6F1"]

3955​

[/TD]
[TD="bgcolor: #DCE6F1"]

2010​

[/TD]
[TD="bgcolor: #DCE6F1"]

89.43​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

6​

[/TD]
[TD="bgcolor: #B8CCE4"]

5​

[/TD]
[TD="bgcolor: #B8CCE4"]Project 5[/TD]
[TD="bgcolor: #B8CCE4"]

4184​

[/TD]
[TD="bgcolor: #B8CCE4"]

2011​

[/TD]
[TD="bgcolor: #B8CCE4"]

32.12​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

7​

[/TD]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD="bgcolor: #DCE6F1"]Project 6[/TD]
[TD="bgcolor: #DCE6F1"]

988​

[/TD]
[TD="bgcolor: #DCE6F1"]

2011​

[/TD]
[TD="bgcolor: #DCE6F1"]

38.00​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

8​

[/TD]
[TD="bgcolor: #B8CCE4"]

7​

[/TD]
[TD="bgcolor: #B8CCE4"]Project 7[/TD]
[TD="bgcolor: #B8CCE4"]

1042​

[/TD]
[TD="bgcolor: #B8CCE4"]

2011​

[/TD]
[TD="bgcolor: #B8CCE4"]

34.20​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

9​

[/TD]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD="bgcolor: #DCE6F1"]Project 8[/TD]
[TD="bgcolor: #DCE6F1"]

4536​

[/TD]
[TD="bgcolor: #DCE6F1"]

2015​

[/TD]
[TD="bgcolor: #DCE6F1"]

61.80​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

10​

[/TD]
[TD="bgcolor: #B8CCE4"][/TD]
[TD="bgcolor: #B8CCE4"][/TD]
[TD="bgcolor: #B8CCE4"][/TD]
[TD="bgcolor: #B8CCE4"][/TD]
[TD="bgcolor: #B8CCE4"][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

11​

[/TD]
[TD="bgcolor: #DCE6F1"]Total[/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

397.47​

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Mr Excel Sample1[/TD]
[/TR]
</tbody>[/TABLE]

Spreadsheet2:
Excel 2010
[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH]_Row\^Col[/TH]
[TH]

A​

[/TH]
[TH]

B​

[/TH]
[TH]

C​

[/TH]
[TH]

D​

[/TH]
[TH]

E​

[/TH]
[TH]

F​

[/TH]
[TH]

G​

[/TH]
[TH]

H​

[/TH]
[TH]

I​

[/TH]
[TH]

J​

[/TH]
[TH]

K​

[/TH]
[TH]

L​

[/TH]
[TH]

M​

[/TH]
[TH]

N​

[/TH]
[TH]

O​

[/TH]
[TH]

P​

[/TH]
[TH]

Q​

[/TH]
[TH]

R​

[/TH]
[TH]

S​

[/TH]
[TH]

T​

[/TH]
[TH]

U​

[/TH]
[TH]

V​

[/TH]
[TH]

W​

[/TH]
[TH]

X​

[/TH]
[TH]

Y​

[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

1​

[/TD]
[TD][/TD]
[TD]

Activities​

[/TD]
[TD]

Activities frequency per management year​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

-1​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[TD]

3​

[/TD]
[TD]

4​

[/TD]
[TD]

5​

[/TD]
[TD]

6​

[/TD]
[TD]

7​

[/TD]
[TD]

8​

[/TD]
[TD]

9​

[/TD]
[TD]

10​

[/TD]
[TD]

11​

[/TD]
[TD]

12​

[/TD]
[TD]

13​

[/TD]
[TD]

14​

[/TD]
[TD]

15​

[/TD]
[TD]

16​

[/TD]
[TD]

17​

[/TD]
[TD]

18​

[/TD]
[TD]

19​

[/TD]
[TD]

20​

[/TD]
[TD]

21​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

3​

[/TD]
[TD]1[/TD]
[TD]Pre-establishment:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

4​

[/TD]
[TD]1.1[/TD]
[TD] Activities 1[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

5​

[/TD]
[TD]1.2[/TD]
[TD] Activities 2[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

6​

[/TD]
[TD]1.3[/TD]
[TD] Activities 3[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

7​

[/TD]
[TD]1.4[/TD]
[TD] Activities 4[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

8​

[/TD]
[TD]1.5[/TD]
[TD] Activities 5[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

9​

[/TD]
[TD]1.6[/TD]
[TD] Others (specify)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

10​

[/TD]
[TD]2[/TD]
[TD]Establishment:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

11​

[/TD]
[TD]2.1[/TD]
[TD] Activities 1[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

12​

[/TD]
[TD]2.2[/TD]
[TD] Activities 2[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

13​

[/TD]
[TD]2.3[/TD]
[TD] Activities 3[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

14​

[/TD]
[TD]2.4[/TD]
[TD] Activities 4[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

15​

[/TD]
[TD]2.5[/TD]
[TD] Activities 5[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

16​

[/TD]
[TD]2.6[/TD]
[TD] Activities 6[/TD]
[TD][/TD]
[TD]

R​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

17​

[/TD]
[TD]2.7[/TD]
[TD] Activities 7[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

18​

[/TD]
[TD]2.8[/TD]
[TD] Activities 8[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

19​

[/TD]
[TD]2.9[/TD]
[TD] Others (specify)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

20​

[/TD]
[TD]3[/TD]
[TD]Post-establishment:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

21​

[/TD]
[TD]3.1[/TD]
[TD] Activities 1[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD]

?​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

22​

[/TD]
[TD]3.2[/TD]
[TD] Activities 2[/TD]
[TD][/TD]
[TD][/TD]
[TD]

3​

[/TD]
[TD]

3​

[/TD]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

23​

[/TD]
[TD]3.3[/TD]
[TD] Activities 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

?​

[/TD]
[TD]

1​

[/TD]
[TD]

?​

[/TD]
[TD]

?​

[/TD]
[TD]

1​

[/TD]
[TD]

?​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

24​

[/TD]
[TD]3.3.1[/TD]
[TD] Activities 3.1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

25​

[/TD]
[TD]3.3.2[/TD]
[TD] Activities 3.2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

26​

[/TD]
[TD]3.3.3[/TD]
[TD] Activities 3.3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

27​

[/TD]
[TD]3.3.4[/TD]
[TD] Activities 3.4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

28​

[/TD]
[TD]3.3.5[/TD]
[TD] Activities 3.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

29​

[/TD]
[TD]3.4[/TD]
[TD] Activities 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

30​

[/TD]
[TD]3.5[/TD]
[TD] Activities 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

31​

[/TD]
[TD]2.6[/TD]
[TD] Others (specify)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

32​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

33​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]Legend:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

34​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]R = Replaceable with mechanic/manual work[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

35​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]? = Depends on the situation[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Mr Excel Sample2[/TD]
[/TR]
</tbody>[/TABLE]

Spreadsheet3 (To save space, I have limited the year but it does goes much beyong that: at least 30 years):
Excel 2010
[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH]_Row\^Col[/TH]
[TH]

A​

[/TH]
[TH]

B​

[/TH]
[TH]

C​

[/TH]
[TH]

D​

[/TH]
[TH]

E​

[/TH]
[TH]

F​

[/TH]
[TH]

G​

[/TH]
[TH]

H​

[/TH]
[TH]

I​

[/TH]
[TH]

J​

[/TH]
[TH]

K​

[/TH]
[TH]

L​

[/TH]
[TH]

M​

[/TH]
[TH]

N​

[/TH]
[TH]

O​

[/TH]
[TH]

P​

[/TH]
[TH]

Q​

[/TH]
[TH]

R​

[/TH]
[TH]

S​

[/TH]
[TH]

T​

[/TH]
[TH]

U​

[/TH]
[TH]

V​

[/TH]
[TH]

W​

[/TH]
[TH]

X​

[/TH]
[TH]

Y​

[/TH]
[TH]

Z​

[/TH]
[TH]

AA​

[/TH]
[TH]

AB​

[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

1​

[/TD]
[TD]Project name & Activities[/TD]
[TD][/TD]
[TD]Project number[/TD]
[TD]Year
started[/TD]
[TD]Project
area (ha.)[/TD]
[TD]

2003​

[/TD]
[TD]

2004​

[/TD]
[TD]

2005​

[/TD]
[TD]

2006​

[/TD]
[TD]

2007​

[/TD]
[TD]

2008​

[/TD]
[TD]

2009​

[/TD]
[TD]

2010​

[/TD]
[TD]

2011​

[/TD]
[TD]

2012​

[/TD]
[TD]

2013​

[/TD]
[TD]

2014​

[/TD]
[TD]

2015​

[/TD]
[TD]

2016​

[/TD]
[TD]

2017​

[/TD]
[TD]

2018​

[/TD]
[TD]

2019​

[/TD]
[TD]

2020​

[/TD]
[TD]

2021​

[/TD]
[TD]

2022​

[/TD]
[TD]

2023​

[/TD]
[TD]

2024​

[/TD]
[TD]

2025​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

2​

[/TD]
[TD]Project 1[/TD]
[TD][/TD]
[TD]

275500​

[/TD]
[TD]

2005​

[/TD]
[TD]

12.00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Mr Excel Sample3[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[twd154]

Hi Forum members,

I was little sad to learn that no one response to my request for help. Maybe it's too lengthy to bother or go through or my explanation is not clear. I will try once again to explain what I am looking for.

Basically, I need to populate range of cells from spreadsheet (ss) 1 and 2 into 3 according to value of a cell in ss3. I have created a drop down (project name) list in cell A2 of ss3 from the ss1's B2 to B9, and selecting any of project name from the drop down list should populate the related data from cell C2 to E9 of ss1 to ss3 into cell C2 to E2 of ss3 (attention: cell are not contiguous in my real excel data). Now the hectic part (for me), according to the value in D2 (Year started column) of ss3: cell range from A3 to B31 and C3 to Y31 of ss2 needs to populate in cell A3 to B31 and F3 to AB31 of ss3. Attention: filling of cell F3 to AB31 of ss3 should be adjusted according to the value of cell D2 of ss3 and needs to be filled under concern years' cell range from F1 to AB1 of ss3.

Hope this helps to help me to get at least some help from generous forum members.

 

[csuanes]

Hello twd154,

I think i may have found a solution for you. Not sure if this approach will fit your needs.
Kindly download the sample file based on your data and check. Validation/Drop Down List will be in Sheet 3.

https://www.dropbox.com/s/vh3qi32uwzk68py/Populate Range of Cells Sample.xlsx?dl=0

 

[twd154]

Hi csuanes,

Thank you so much for your generous help. Sorry that I couldn't response earlier. Been really busy with my work loads and somehow in between I found much easier solution. I will surely look through your approach, and it will surely help in my future work.

I actually, instead of populating the required data, I just put an simple + 1 formula to adjust the calendar year at the top row of activities frequencies year and copy-paste the cell range A3 to Y31 (not my original plan but it serve the purpose at the moment).

Thanks once again