Dear Excel experts

I have a workbook with Sheet X and Z.

On Sheet X, there are these coloums

Class Number of Students Male Female

AA 10 3 7
YY
ZZ 0
NN
OO 20 5 15

(More data at a later date)

How do I have Excel 2003 fill data on Sheet Z, so that it will Print out
those Classes that have number of students (ignore zeros and null)?

Result:

Class Number of Students
AA 10
OO 20

Please remember that Sheet Z will be added with more data as time goes by
and I want SHeet Z be able to keep spitting out Classes that have students.

Thank you for your advice.

Use the code below. It needs to go into the code module for your "Sheet Z".
To get it the
Open the workbook, select Sheet Z and right-click on its name tab. Select
the [View Code] option from the list that appears.
Copy the code below and paste it into the module that appeared.
Modify any of the Const values that need to be modified. Close the VB Editor.

After that, each time you select Sheet Z, the list on it will be updated
from the list on Sheet X, so it will always be current based on the contents
of Sheet X.

There's also a line of code that is currently commented out that you can
turn into an active statement once you are sure things are working properly.
When you do that, it will update SheetZ much faster.

Here's the code, hope it helps:

Private Sub Worksheet_Activate()
'auto update from SheetX
'
'change these constants to
'match worksheet names and
'columns/rows used
'
Const sheetXName = "SheetX"
Const xClassColumn = "A"
Const xClassStudentsCol = "B"
Const xFirstClassRow = 2
'these have to do with
'this sheet (sheetZ)
Const zClassColumn = "A"
Const zFirstClassRow = 2
'end of user defined constants
Dim xSheet As Worksheet
Dim xSheetClassList As Range
Dim anyxSheetClass As Range
Dim offsetToStudentCount As Integer
Dim zRow As Long
Dim zBaseCell As Range

'
'once you are sure it is working,
'remove the apostrophe from the beginning of
'the next instruction to improve speed
'Application.ScreenUpdating = False

'start by erasing old entries on SheetZ
'and rebuilding the labels in row 1
Cells.ClearContents
Range("A1") = "Class"
Range("B1") = "No. Students"
Set xSheet = ThisWorkbook.Worksheets(sheetXName)
If xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Row _
< xFirstClassRow Then
'no classes on SheetX, do nothing
Set xSheet = Nothing
Exit Sub
End If
Set xSheetClassList = xSheet.Range(xClassColumn & _
xFirstClassRow & ":" & _
xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Address)
offsetToStudentCount = Range(xClassStudentsCol & 1).Column - _
Range(xClassColumn & 1).Column
Set zBaseCell = Range(zClassColumn & zFirstClassRow)
zRow = 0
For Each anyxSheetClass In xSheetClassList
If anyxSheetClass.Offset(0, _
offsetToStudentCount) 0 Then
zBaseCell.Offset(zRow, 0) = anyxSheetClass
zBaseCell.Offset(zRow, offsetToStudentCount) = _
anyxSheetClass.Offset(0, offsetToStudentCount)
zRow = zRow + 1
End If
Next
'housekeeping
Set xSheetClassList = Nothing
Set xSheet = Nothing
Set zBaseCell = Nothing
End Sub


"apache007" wrote:

Dear Excel experts

I have a workbook with Sheet X and Z.

On Sheet X, there are these coloums

Class Number of Students Male Female

AA 10 3 7
YY
ZZ 0
NN
OO 20 5 15

(More data at a later date)

How do I have Excel 2003 fill data on Sheet Z, so that it will Print out
those Classes that have number of students (ignore zeros and null)?

Result:

Class Number of Students
AA 10
OO 20

Please remember that Sheet Z will be added with more data as time goes by
and I want SHeet Z be able to keep spitting out Classes that have students.

Thank you for your advice.

Jlatham,

Thanks for the advice. It works perfectly.

Boy...I do not expect I will need to do it through programming. Is there a
way doing that using just on Excel's feature???

And is there any good website to brush up my programming skill on VB
specializing on EXCEL???



"JLatham" wrote:

Use the code below. It needs to go into the code module for your "Sheet Z".
To get it the
Open the workbook, select Sheet Z and right-click on its name tab. Select
the [View Code] option from the list that appears.
Copy the code below and paste it into the module that appeared.
Modify any of the Const values that need to be modified. Close the VB Editor.

After that, each time you select Sheet Z, the list on it will be updated
from the list on Sheet X, so it will always be current based on the contents
of Sheet X.

There's also a line of code that is currently commented out that you can
turn into an active statement once you are sure things are working properly.
When you do that, it will update SheetZ much faster.

Here's the code, hope it helps:

Private Sub Worksheet_Activate()
'auto update from SheetX
'
'change these constants to
'match worksheet names and
'columns/rows used
'
Const sheetXName = "SheetX"
Const xClassColumn = "A"
Const xClassStudentsCol = "B"
Const xFirstClassRow = 2
'these have to do with
'this sheet (sheetZ)
Const zClassColumn = "A"
Const zFirstClassRow = 2
'end of user defined constants
Dim xSheet As Worksheet
Dim xSheetClassList As Range
Dim anyxSheetClass As Range
Dim offsetToStudentCount As Integer
Dim zRow As Long
Dim zBaseCell As Range

'
'once you are sure it is working,
'remove the apostrophe from the beginning of
'the next instruction to improve speed
'Application.ScreenUpdating = False

'start by erasing old entries on SheetZ
'and rebuilding the labels in row 1
Cells.ClearContents
Range("A1") = "Class"
Range("B1") = "No. Students"
Set xSheet = ThisWorkbook.Worksheets(sheetXName)
If xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Row _
< xFirstClassRow Then
'no classes on SheetX, do nothing
Set xSheet = Nothing
Exit Sub
End If
Set xSheetClassList = xSheet.Range(xClassColumn & _
xFirstClassRow & ":" & _
xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Address)
offsetToStudentCount = Range(xClassStudentsCol & 1).Column - _
Range(xClassColumn & 1).Column
Set zBaseCell = Range(zClassColumn & zFirstClassRow)
zRow = 0
For Each anyxSheetClass In xSheetClassList
If anyxSheetClass.Offset(0, _
offsetToStudentCount) 0 Then
zBaseCell.Offset(zRow, 0) = anyxSheetClass
zBaseCell.Offset(zRow, offsetToStudentCount) = _
anyxSheetClass.Offset(0, offsetToStudentCount)
zRow = zRow + 1
End If
Next
'housekeeping
Set xSheetClassList = Nothing
Set xSheet = Nothing
Set zBaseCell = Nothing
End Sub


"apache007" wrote:

Dear Excel experts

I have a workbook with Sheet X and Z.

On Sheet X, there are these coloums

Class Number of Students Male Female

AA 10 3 7
YY
ZZ 0
NN
OO 20 5 15

(More data at a later date)

How do I have Excel 2003 fill data on Sheet Z, so that it will Print out
those Classes that have number of students (ignore zeros and null)?

Result:

Class Number of Students
AA 10
OO 20

Please remember that Sheet Z will be added with more data as time goes by
and I want SHeet Z be able to keep spitting out Classes that have students.

Thank you for your advice.

One of the people in here that's a lot sharper than I am with Excel worksheet
functions might be able to come up with formulas for the second sheet to
update from the first, but I'm not that person.

One problem is that your second sheet has to know, somehow, how many entries
are on the first sheet - about the only way to do that with functions is to
fill lots of empty rows with the formulas in anticipation of future entries
on the source sheet. Then the formulas have to 'close ranks' - i.e., not
pick up entries except when there is a value to be brought over without
skipping rows.

Worksheet functions can't add other functions to a sheet, and they cannot do
things like cause a sheet to be filtered or hide/display rows/columns. So
for me, the easy path was to code up the solution - and since I'm a coder by
nature, that's the route I took.

Learning VBA
there are a number of site around the net to help.
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.the-excel-advisor.com/exc...-tutorial.html
http://class.et.byu.edu/ce270/vbaexcel_primer/intro.htm
http://www.exceltip.com/excel_links.html
there are other sites that provide usefull information about specific issues.
http://www.contextures.com/
http://www.cpearson.com/
http://www.j-walk.com/
http://www.mcgimpsey.com/
http://www.rondebruin.nl/
http://www.mrexcel.com
http://www.mvps.org/

I've had a project in progress for some time to write an "intro to Excel VBA
programming" and while I've made good progress with it, it keeps getting
pushed to the back burner and so I haven't got it to a point I feel
comfortable sticking it up on the internet as another free source of learning
(that would hopefully actually be useful also).



"apache007" wrote:

Jlatham,

Thanks for the advice. It works perfectly.

Boy...I do not expect I will need to do it through programming. Is there a
way doing that using just on Excel's feature???

And is there any good website to brush up my programming skill on VB
specializing on EXCEL???



"JLatham" wrote:

Use the code below. It needs to go into the code module for your "Sheet Z".
To get it the
Open the workbook, select Sheet Z and right-click on its name tab. Select
the [View Code] option from the list that appears.
Copy the code below and paste it into the module that appeared.
Modify any of the Const values that need to be modified. Close the VB Editor.

After that, each time you select Sheet Z, the list on it will be updated
from the list on Sheet X, so it will always be current based on the contents
of Sheet X.

There's also a line of code that is currently commented out that you can
turn into an active statement once you are sure things are working properly.
When you do that, it will update SheetZ much faster.

Here's the code, hope it helps:

Private Sub Worksheet_Activate()
'auto update from SheetX
'
'change these constants to
'match worksheet names and
'columns/rows used
'
Const sheetXName = "SheetX"
Const xClassColumn = "A"
Const xClassStudentsCol = "B"
Const xFirstClassRow = 2
'these have to do with
'this sheet (sheetZ)
Const zClassColumn = "A"
Const zFirstClassRow = 2
'end of user defined constants
Dim xSheet As Worksheet
Dim xSheetClassList As Range
Dim anyxSheetClass As Range
Dim offsetToStudentCount As Integer
Dim zRow As Long
Dim zBaseCell As Range

'
'once you are sure it is working,
'remove the apostrophe from the beginning of
'the next instruction to improve speed
'Application.ScreenUpdating = False

'start by erasing old entries on SheetZ
'and rebuilding the labels in row 1
Cells.ClearContents
Range("A1") = "Class"
Range("B1") = "No. Students"
Set xSheet = ThisWorkbook.Worksheets(sheetXName)
If xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Row _
< xFirstClassRow Then
'no classes on SheetX, do nothing
Set xSheet = Nothing
Exit Sub
End If
Set xSheetClassList = xSheet.Range(xClassColumn & _
xFirstClassRow & ":" & _
xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Address)
offsetToStudentCount = Range(xClassStudentsCol & 1).Column - _
Range(xClassColumn & 1).Column
Set zBaseCell = Range(zClassColumn & zFirstClassRow)
zRow = 0
For Each anyxSheetClass In xSheetClassList
If anyxSheetClass.Offset(0, _
offsetToStudentCount) 0 Then
zBaseCell.Offset(zRow, 0) = anyxSheetClass
zBaseCell.Offset(zRow, offsetToStudentCount) = _
anyxSheetClass.Offset(0, offsetToStudentCount)
zRow = zRow + 1
End If
Next
'housekeeping
Set xSheetClassList = Nothing
Set xSheet = Nothing
Set zBaseCell = Nothing
End Sub


"apache007" wrote:

Dear Excel experts

I have a workbook with Sheet X and Z.

On Sheet X, there are these coloums

Class Number of Students Male Female

AA 10 3 7
YY
ZZ 0
NN
OO 20 5 15

(More data at a later date)

How do I have Excel 2003 fill data on Sheet Z, so that it will Print out
those Classes that have number of students (ignore zeros and null)?

Result:

Class Number of Students
AA 10
OO 20

Please remember that Sheet Z will be added with more data as time goes by
and I want SHeet Z be able to keep spitting out Classes that have students.

Thank you for your advice.

Thanks, Jlatham. I will start digging those sites on my free time.
If you are done with Intro to Excel VBA, that will help out a lot.
Please let me know if that ever published.


"JLatham" wrote:

One of the people in here that's a lot sharper than I am with Excel worksheet
functions might be able to come up with formulas for the second sheet to
update from the first, but I'm not that person.

One problem is that your second sheet has to know, somehow, how many entries
are on the first sheet - about the only way to do that with functions is to
fill lots of empty rows with the formulas in anticipation of future entries
on the source sheet. Then the formulas have to 'close ranks' - i.e., not
pick up entries except when there is a value to be brought over without
skipping rows.

Worksheet functions can't add other functions to a sheet, and they cannot do
things like cause a sheet to be filtered or hide/display rows/columns. So
for me, the easy path was to code up the solution - and since I'm a coder by
nature, that's the route I took.

Learning VBA
there are a number of site around the net to help.
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.the-excel-advisor.com/exc...-tutorial.html
http://class.et.byu.edu/ce270/vbaexcel_primer/intro.htm
http://www.exceltip.com/excel_links.html
there are other sites that provide usefull information about specific issues.
http://www.contextures.com/
http://www.cpearson.com/
http://www.j-walk.com/
http://www.mcgimpsey.com/
http://www.rondebruin.nl/
http://www.mrexcel.com
http://www.mvps.org/

I've had a project in progress for some time to write an "intro to Excel VBA
programming" and while I've made good progress with it, it keeps getting
pushed to the back burner and so I haven't got it to a point I feel
comfortable sticking it up on the internet as another free source of learning
(that would hopefully actually be useful also).



"apache007" wrote:

Jlatham,

Thanks for the advice. It works perfectly.

Boy...I do not expect I will need to do it through programming. Is there a
way doing that using just on Excel's feature???

And is there any good website to brush up my programming skill on VB
specializing on EXCEL???



"JLatham" wrote:

Use the code below. It needs to go into the code module for your "Sheet Z".
To get it the
Open the workbook, select Sheet Z and right-click on its name tab. Select
the [View Code] option from the list that appears.
Copy the code below and paste it into the module that appeared.
Modify any of the Const values that need to be modified. Close the VB Editor.

After that, each time you select Sheet Z, the list on it will be updated
from the list on Sheet X, so it will always be current based on the contents
of Sheet X.

There's also a line of code that is currently commented out that you can
turn into an active statement once you are sure things are working properly.
When you do that, it will update SheetZ much faster.

Here's the code, hope it helps:

Private Sub Worksheet_Activate()
'auto update from SheetX
'
'change these constants to
'match worksheet names and
'columns/rows used
'
Const sheetXName = "SheetX"
Const xClassColumn = "A"
Const xClassStudentsCol = "B"
Const xFirstClassRow = 2
'these have to do with
'this sheet (sheetZ)
Const zClassColumn = "A"
Const zFirstClassRow = 2
'end of user defined constants
Dim xSheet As Worksheet
Dim xSheetClassList As Range
Dim anyxSheetClass As Range
Dim offsetToStudentCount As Integer
Dim zRow As Long
Dim zBaseCell As Range

'
'once you are sure it is working,
'remove the apostrophe from the beginning of
'the next instruction to improve speed
'Application.ScreenUpdating = False

'start by erasing old entries on SheetZ
'and rebuilding the labels in row 1
Cells.ClearContents
Range("A1") = "Class"
Range("B1") = "No. Students"
Set xSheet = ThisWorkbook.Worksheets(sheetXName)
If xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Row _
< xFirstClassRow Then
'no classes on SheetX, do nothing
Set xSheet = Nothing
Exit Sub
End If
Set xSheetClassList = xSheet.Range(xClassColumn & _
xFirstClassRow & ":" & _
xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Address)
offsetToStudentCount = Range(xClassStudentsCol & 1).Column - _
Range(xClassColumn & 1).Column
Set zBaseCell = Range(zClassColumn & zFirstClassRow)
zRow = 0
For Each anyxSheetClass In xSheetClassList
If anyxSheetClass.Offset(0, _
offsetToStudentCount) 0 Then
zBaseCell.Offset(zRow, 0) = anyxSheetClass
zBaseCell.Offset(zRow, offsetToStudentCount) = _
anyxSheetClass.Offset(0, offsetToStudentCount)
zRow = zRow + 1
End If
Next
'housekeeping
Set xSheetClassList = Nothing
Set xSheet = Nothing
Set zBaseCell = Nothing
End Sub


"apache007" wrote:

Dear Excel experts

I have a workbook with Sheet X and Z.

On Sheet X, there are these coloums

Class Number of Students Male Female

AA 10 3 7
YY
ZZ 0
NN
OO 20 5 15

(More data at a later date)

How do I have Excel 2003 fill data on Sheet Z, so that it will Print out
those Classes that have number of students (ignore zeros and null)?

Result:

Class Number of Students
AA 10
OO 20

Please remember that Sheet Z will be added with more data as time goes by
and I want SHeet Z be able to keep spitting out Classes that have students.

Thank you for your advice.

I am NOT 'done' with the intro to VBA, but I think that perhaps there's
something of use in it even now. Either there is, or I've written almost 50
pages of useless gibberish! You're welcome to take a peek at it.

The most current version of the book may be downloaded, free of charge, from:

http://www.jlathamsite.com/LearningPage.htm

Look for the link to the .pdf document just below the heading "Introduction
to VBA Programming". I recommend right-clicking the link and choosing "Save
Target As" to get a copy of it onto your system.

My Excel-MVP-persona email address is in the book, and you're welcome to use
it to provide me any feedback for it. Just in case, the email addy is
(remove spaces)
Help From @ jlathamsite. com
If the links on the page I provided give you problems, let me know, I just
revised that LearningPage.htm today and haven't tested them all, but the link
to the .pdf file did work properly for me.

"apache007" wrote:

Thanks, Jlatham. I will start digging those sites on my free time.
If you are done with Intro to Excel VBA, that will help out a lot.
Please let me know if that ever published.


"JLatham" wrote:

One of the people in here that's a lot sharper than I am with Excel worksheet
functions might be able to come up with formulas for the second sheet to
update from the first, but I'm not that person.

One problem is that your second sheet has to know, somehow, how many entries
are on the first sheet - about the only way to do that with functions is to
fill lots of empty rows with the formulas in anticipation of future entries
on the source sheet. Then the formulas have to 'close ranks' - i.e., not
pick up entries except when there is a value to be brought over without
skipping rows.

Worksheet functions can't add other functions to a sheet, and they cannot do
things like cause a sheet to be filtered or hide/display rows/columns. So
for me, the easy path was to code up the solution - and since I'm a coder by
nature, that's the route I took.

Learning VBA
there are a number of site around the net to help.
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.the-excel-advisor.com/exc...-tutorial.html
http://class.et.byu.edu/ce270/vbaexcel_primer/intro.htm
http://www.exceltip.com/excel_links.html
there are other sites that provide usefull information about specific issues.
http://www.contextures.com/
http://www.cpearson.com/
http://www.j-walk.com/
http://www.mcgimpsey.com/
http://www.rondebruin.nl/
http://www.mrexcel.com
http://www.mvps.org/

I've had a project in progress for some time to write an "intro to Excel VBA
programming" and while I've made good progress with it, it keeps getting
pushed to the back burner and so I haven't got it to a point I feel
comfortable sticking it up on the internet as another free source of learning
(that would hopefully actually be useful also).



"apache007" wrote:

Jlatham,

Thanks for the advice. It works perfectly.

Boy...I do not expect I will need to do it through programming. Is there a
way doing that using just on Excel's feature???

And is there any good website to brush up my programming skill on VB
specializing on EXCEL???



"JLatham" wrote:

Use the code below. It needs to go into the code module for your "Sheet Z".
To get it the
Open the workbook, select Sheet Z and right-click on its name tab. Select
the [View Code] option from the list that appears.
Copy the code below and paste it into the module that appeared.
Modify any of the Const values that need to be modified. Close the VB Editor.

After that, each time you select Sheet Z, the list on it will be updated
from the list on Sheet X, so it will always be current based on the contents
of Sheet X.

There's also a line of code that is currently commented out that you can
turn into an active statement once you are sure things are working properly.
When you do that, it will update SheetZ much faster.

Here's the code, hope it helps:

Private Sub Worksheet_Activate()
'auto update from SheetX
'
'change these constants to
'match worksheet names and
'columns/rows used
'
Const sheetXName = "SheetX"
Const xClassColumn = "A"
Const xClassStudentsCol = "B"
Const xFirstClassRow = 2
'these have to do with
'this sheet (sheetZ)
Const zClassColumn = "A"
Const zFirstClassRow = 2
'end of user defined constants
Dim xSheet As Worksheet
Dim xSheetClassList As Range
Dim anyxSheetClass As Range
Dim offsetToStudentCount As Integer
Dim zRow As Long
Dim zBaseCell As Range

'
'once you are sure it is working,
'remove the apostrophe from the beginning of
'the next instruction to improve speed
'Application.ScreenUpdating = False

'start by erasing old entries on SheetZ
'and rebuilding the labels in row 1
Cells.ClearContents
Range("A1") = "Class"
Range("B1") = "No. Students"
Set xSheet = ThisWorkbook.Worksheets(sheetXName)
If xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Row _
< xFirstClassRow Then
'no classes on SheetX, do nothing
Set xSheet = Nothing
Exit Sub
End If
Set xSheetClassList = xSheet.Range(xClassColumn & _
xFirstClassRow & ":" & _
xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Address)
offsetToStudentCount = Range(xClassStudentsCol & 1).Column - _
Range(xClassColumn & 1).Column
Set zBaseCell = Range(zClassColumn & zFirstClassRow)
zRow = 0
For Each anyxSheetClass In xSheetClassList
If anyxSheetClass.Offset(0, _
offsetToStudentCount) 0 Then
zBaseCell.Offset(zRow, 0) = anyxSheetClass
zBaseCell.Offset(zRow, offsetToStudentCount) = _
anyxSheetClass.Offset(0, offsetToStudentCount)
zRow = zRow + 1
End If
Next
'housekeeping
Set xSheetClassList = Nothing
Set xSheet = Nothing
Set zBaseCell = Nothing
End Sub


"apache007" wrote:

Dear Excel experts

I have a workbook with Sheet X and Z.

On Sheet X, there are these coloums

Class Number of Students Male Female

AA 10 3 7
YY
ZZ 0
NN
OO 20 5 15

(More data at a later date)

How do I have Excel 2003 fill data on Sheet Z, so that it will Print out
those Classes that have number of students (ignore zeros and null)?

Result:

Class Number of Students
AA 10
OO 20

Please remember that Sheet Z will be added with more data as time goes by
and I want SHeet Z be able to keep spitting out Classes that have students.

Thank you for your advice.