Hi All,

Here is the data I work with - this is being generated by our ERP:

applied accepted confirmed rejected
Math 100 50 20 30
English 40 0 3
7
Biology 34 4 5 34
Physics 34 56 6 7
Spanish 10 0 0 0
English 0 0 0 0

This gets updated daily as we have students confirming/hanging thier
status on a daily basis.
In all we have about 40 rows of data similar to the above (except that
the last rows of zeros is suppressed).

For management reporting purposes, only the Confirmed numbers are
reported, and then are manually pulled out an entered into a new
spreadsheet that looks like this:
Summary year 0 year1
Math 30 20
English 40 3
Biology 50 5
Physics 40 6
Spanish 30 0
English 20 0

Where year1 is the current years confirmed above.

Is there a way that I can pull these numbers directly into a sumary
sheet.
Sheet1.a1=sheet2.a2 would not work as the the row position on these
change based on zerro suppression that can happen at any time. Is
there a way, where I can say something like =math.confirmed ...so
that my summary is dynamic?


Thanks for your help

Have you tried using Vlookup,where the criteria would be the subject, the
datatable would be the ERP download in erach tab,and the offset the Confirmed
column.

John

"KW" wrote:

Hi All,

Here is the data I work with - this is being generated by our ERP:

applied accepted confirmed rejected
Math 100 50 20 30
English 40 0 3
7
Biology 34 4 5 34
Physics 34 56 6 7
Spanish 10 0 0 0
English 0 0 0 0

This gets updated daily as we have students confirming/hanging thier
status on a daily basis.
In all we have about 40 rows of data similar to the above (except that
the last rows of zeros is suppressed).

For management reporting purposes, only the Confirmed numbers are
reported, and then are manually pulled out an entered into a new
spreadsheet that looks like this:
Summary year 0 year1
Math 30 20
English 40 3
Biology 50 5
Physics 40 6
Spanish 30 0
English 20 0

Where year1 is the current years confirmed above.

Is there a way that I can pull these numbers directly into a sumary
sheet.
Sheet1.a1=sheet2.a2 would not work as the the row position on these
change based on zerro suppression that can happen at any time. Is
there a way, where I can say something like =math.confirmed ...so
that my summary is dynamic?


Thanks for your help