Hi guys I recently decided to put in my students' attendance into a
worksheet. A sample of one of the student's sheets can be seen at
http://i291.photobucket.com/albums/l...attendance.jpg (at lot
easier to upload a screenshot than to explain ;))

Anyway I also consolidate the particular stundet's payment records as seen
at http://i291.photobucket.com/albums/l...em/payment.jpg

At this moment however, I manually enter data for BOTH the sheets.
(sub-total for the payment sheet is calculated automatically however).
However I would like to only fill up the attendance sheet while the payment
automatically grabs the necessary info. As can be seen, it needs to grab the
date, amount and receipt number. (I guess I could edit it later to include
the others such as payment mode and so on)

However, it should only grab data when there is actually some sort of
information (i.e don't grab when amount due is listed as NA). How would this
be possible? Thank you in advance.

One simple formulas set-up which should deliver it for you ..

Illustrated in this sample:
http://www.freefilehosting.net/download/3gg7c
Extract only lines with amt from other sht.xls

Source data assumed in sheet: x, cols A to D,
where the key col = col C ("Payment due")

In another sheet: y,
In A2: =IF(ISNUMBER(x!C2),ROW(),"")
Leave A1 blank

In B2:
=IF(ROWS($1:1)COUNT($A:$A),"",INDEX(x!B:B,SMALL($ A:$A,ROWS($1:1))))
Copy B2 to D2. Select A2:D2, copy down to cover the max expected extent of
source data in x. Format col B as date, col C as currency to taste. Cols B to
D returns the required results: dates, amt due & receipt#, with all results
neatly bunched at the top
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"prem" wrote:
Hi guys I recently decided to put in my students' attendance into a
worksheet. A sample of one of the student's sheets can be seen at
http://i291.photobucket.com/albums/l...attendance.jpg (at lot
easier to upload a screenshot than to explain ;))

Anyway I also consolidate the particular stundet's payment records as seen
at http://i291.photobucket.com/albums/l...em/payment.jpg

At this moment however, I manually enter data for BOTH the sheets.
(sub-total for the payment sheet is calculated automatically however).
However I would like to only fill up the attendance sheet while the payment
automatically grabs the necessary info. As can be seen, it needs to grab the
date, amount and receipt number. (I guess I could edit it later to include
the others such as payment mode and so on)

However, it should only grab data when there is actually some sort of
information (i.e don't grab when amount due is listed as NA). How would this
be possible? Thank you in advance.