Automatically update sheets based on input entered in another sheet

[BP Excel]

I’m looking for a way to automatically update Sheet 2 and Sheet 3 based on information entered on Sheet 1.

Sheet1 is a very simple 3-column spreadsheet used for entering data. The first column in Sheet1 is used to enter names; the second column is used to enter a number of projects associated with each name; and third column is used to enter the total hours of all projects.

Sheet1:

Name	# of Projects	# of Hours
A	2	200
B	3	300
C	0	0
D	1	100
E	0	0

<tbody>
</tbody>

Sheet 2 displays the names, projects, and hours from Sheet1 that have at least 1 project. (Number of projects is greater than 0.)

Sheet2:

Name	# of Projects	# of Hours
A	2	200
B	3	300
D	1	100

<tbody>
</tbody>

Sheet 3 displays the names from Sheet 1 that have less than 1 project. (Number of projects is 0.)

Sheet3:

Name	# of Projects	# of Hours
C	0	0
E	0	0

<tbody>
</tbody>

Is there a way to automate this so whenever any information is entered on Sheet 1, the corresponding information on both Sheet 2 and Sheet 3 is automatically updated? This seems pretty straightforward, but I have very limited experience using Excel. I’ve searched the forums and was not able to find a way to do this. Appreciate any help.

Thanks,
BP

 

[Scott T]

Sheet 2:

	A	B	C	D
1	Name	#of Projects	# of Hours
2	A	2	200
3	b	3	300
4	D	1	100
5

<tbody>
</tbody>

All formulas below are array formulas and must be entered with Control+Shift+Enter. Excel will put curly brackets, {}, around round your formula.
In A2 copy down and across

{=IF(ROWS(A$2:A2)<=COUNTIF(Sheet1!$B:$B,">0"),INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$B:$B>0,ROW(Sheet1!A:A)),ROWS(A$2:A2)+1)),"")}

In Sheet 3 A2 copy down and across

{=IF(ROWS(A$2:A2)<=COUNTIF(Sheet1!$B:$B,"<1"),INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$B:$B<1,ROW(Sheet1!A:A)),ROWS(A$2:A2))),"")}

 

[BP Excel]

Thanks, Scott T. I wasn't able to get this to work quite right. The column headings copy over from Sheet1 to Sheet2, but each of the values underneath the column headings appear as "#NUM!".

Any ideas on what I'm doing wrong?

 

[BP Excel]

Scott T. After a few more attempts, I figured out what I was doing wrong. This works great!

I'm trying to make one more slight modification. The names on the Data Entry sheet are color coded. Is there an easy way for the colors to transfer across sheets as well?

Thanks,
BP

 

[Scott T]

Did you use Control+Shift+Enter when entering the formula? If you just use enter you will get a #NUM! error.
When you copy the formula delete the {} from the start and end.

 

[BP Excel]

Thanks! That worked.

I'm also wondering if there is any easy way to keep the cell borders and other formatting from the Data Entry sheet. Each of the names in Column A of the Data Entry sheet are color coded. Is there an easy way for the cell borders and/or the colors to transfer across sheets as well?

Thanks,
BP

 

[Scott T]

I am a newbie when it comes to VBA but a VBA solution may be able to do what you want and keep the color coding and formatting.

If the color coding is from conditional formatting then you can just apply the same conditional formatting rules to the new sheets.

 

[BP Excel]

Scott T,

Would you also be able to show me how to calculate the following values on another sheet:

1. Total number of Names from Sheet1
2. Total number of Names from Sheet1 with at least 1 project
3. Percentage of Names with at least 1 project
4. Total number of Projects from Sheet1
5. Total number of Hours from Sheet1

I understand how to calculate these values when the number of Names from Sheet1 is a fixed value...but not when the Number of Names from Sheet1 changes. Thanks again for all your help.

BP

 

[Scott T]

If you use VBA to update your other sheets then these calculates can also be included.

Or you could use dynamic ranges
Dynamic Named Range in Excel - EASY Excel Tutorial

Or use the whole column, for example =sum(A:A) sums the entire A column

 

[BP Excel]

I decided not to use VBA. Your suggestion to use =sum(A:A) works perfectly. Thanks so much!

BP