Conitional copying of a serie of rows from one spreadsheet to another

"david" wrote:
I have tried changing it but it doesn't seem to be working.
Would there be any chance of you looking at the spreadsheet if I email
it to you to see where I have gone wrong .

No problem*, but it'll be faster / better if you could upload** a small
sample (sanitized if necess.) & just post the link to it in response here.
Anyway pl drop me a line here to let me know if you've emailed.

*Email: demechanik at yahoo dot com
(Need to wait awhile, though, as I can only access
my yahoo account in about 9-10 hours time)

**Use either of the 3 free filehosts listed below to upload your sample and
then post the link to it in response here (the link is generated when you
upload, just copy and paste it here)

**
http://www.flypicture.com/
http://cjoint.com/index.php
http://www.savefile.com/index.php

For cjoint.com (it's in French), just click the "Browse" button,
navigate to folder select the file Open, then click the button centred
in the page below (labelled "Creer le lien Cjoint") and it'll generate the
link. Then copy & paste the generated link as part and parcel of your
response here.

Kindly note that no attachments should be posted *directly* to the newsgroup
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Mx

I am presuming (hoping!) here

I have uploaded the problem file to savefile.com

http://www.savefile.com/projects/674184

Thanks

david wrote:
Max

I have tried changing it but it doesn't seem to be working.

Would there be any chance of you looking at the spreadsheet if I email
it to you to see where I have gone wrong .

Thanks

David


Max wrote:
"david" wrote:
Thanks Max - you are a wonder - I now need to sit down with it and
amend it slightly to make it fit the exact data set I have.

You're welcome !

Presumably there would be no problem having both the 'x' criteria that
the selection are being made on and the start and end dates come from
specific cell references.

Yes, of course. I've provided a working sample, no? g. It's always
better to structure it to point to criteria input cells (generalize) instead
of hardcoding the criteria's specific values within the formula. That way, we
don't need to re-edit / re-fill formulas all over again should the specific
values for the criteria change.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

"david" wrote:
I have uploaded the problem file to savefile.com
http://www.savefile.com/projects/674184

Here's the implemented file:
http://cjoint.com/?hej7AWtVYv
Incident_Book_for_playY9.xls

In sheet: Printout,

In A6, A6 copied across to K6, then filled down to K100:
=IF(ROW(A1)COUNT($O:$O),"",INDEX(Y9Incidents!A:A, MATCH(SMALL($O:$O,ROW(A1)),$O:$O,0)+8))

We need to adjust with a "+8" to the result returned by MATCH since the
source data in sheet: Y9Incidents begins in row 14 and the formula's starting
row here is row 6.

The criteria col O
In O6 filled down to say O100 (say):
=IF(OR(Control!$C$9="",Control!$C$10="",Control!$C $10=""),"",IF(Y9Incidents!B14="","",IF(AND(Y9Incid ents!B14=Control!$C$9,Y9Incidents!D14=Control!$C$ 10,Y9Incidents!D14=Control!$C$11),ROW(),"")))
(Leave O1:O5 empty)

And for a neater look, we can suppress extraneous zeros in the sheet
via clicking: Tools Options View tab Uncheck Zero values OK
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Max

Thanks once again for you patience with someone who knows enough to
know that Excel can do something but not quite enough to get it to do
it.

David


Max wrote:
"david" wrote:
I have uploaded the problem file to savefile.com
http://www.savefile.com/projects/674184

Here's the implemented file:
http://cjoint.com/?hej7AWtVYv
Incident_Book_for_playY9.xls

In sheet: Printout,

In A6, A6 copied across to K6, then filled down to K100:
=IF(ROW(A1)COUNT($O:$O),"",INDEX(Y9Incidents!A:A, MATCH(SMALL($O:$O,ROW(A1)),$O:$O,0)+8))

We need to adjust with a "+8" to the result returned by MATCH since the
source data in sheet: Y9Incidents begins in row 14 and the formula's starting
row here is row 6.

The criteria col O
In O6 filled down to say O100 (say):
=IF(OR(Control!$C$9="",Control!$C$10="",Control!$C $10=""),"",IF(Y9Incidents!B14="","",IF(AND(Y9Incid ents!B14=Control!$C$9,Y9Incidents!D14=Control!$C$ 10,Y9Incidents!D14=Control!$C$11),ROW(),"")))
(Leave O1:O5 empty)

And for a neater look, we can suppress extraneous zeros in the sheet
via clicking: Tools Options View tab Uncheck Zero values OK
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Glad to help, David !
Thanks for feeding back ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"david" wrote:
Max

Thanks once again for you patience with someone who knows enough to
know that Excel can do something but not quite enough to get it to do
it.

David

Max

I am being a pain again!!!

The solution you sent me has a problem.

When I click on the 'New Entry' macro on the 'Y9_Incidents' sheet it
counts the cell reference number down in the 'O' column of the
'Printout' sheet.

I tried making it an absolute rather than a relative reference but I
can't get that to work.

David


Max wrote:
Glad to help, David !
Thanks for feeding back ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"david" wrote:
Max

Thanks once again for you patience with someone who knows enough to
know that Excel can do something but not quite enough to get it to do
it.

David

"david" wrote:
When I click on the 'New Entry' macro on the 'Y9_Incidents' sheet it
counts the cell reference number down in the 'O' column of the
'Printout' sheet.

I tried making it an absolute rather than a relative reference but I
can't get that to work.

In sheet: Printout,

Try this revised criteria formula* in O6, filled down to O100 (say):
=IF(OR(Control!$C$9="",Control!$C$10="",Control!$C $10=""),"",
IF(INDEX(INDIRECT("'Y9Incidents'!B14:B1000"),ROW(A 1))="","",
IF(AND(INDEX(INDIRECT("'Y9Incidents'!B14:B1000"),R OW(A1))=Control!$C$9,
INDEX(INDIRECT("'Y9Incidents'!D141000"),ROW(A1)) =Control!$C$10,
INDEX(INDIRECT("'Y9Incidents'!D141000"),ROW(A1)) =Control!$C$11),ROW(),"")))

*to cater for new row insertions by the macro in Y9Incidents

(No change to the other formulas in cols A to K)

An implemented sample is available at:
http://www.savefile.com/files/5487340
incident_Book_for_playY9_1.xls

Test it out (works ok here, under light testing)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

*to cater for new row insertions by the macro in Y9Incidents

Since the macro / it's impact wasn't part of your original post,
you do realize I'll be bumping up my bill to you, won't you? bg
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thanks Max this seems to be working OK

One final question and please ignore this if I am taking up too much of
your time.

I have tweaked the file to add summary pages for each of the 4 classes
together with the printout page which pulls its criteria from from the
'Control' sheet.

What I want to be able to do is print this 'Printout' page via a macro.
So I would select the form from the dropdown box on the control page
and then press a macro button. To do this I presume I need to be able
to detect which is the last line that there is any data in so the print
range can be set- how can I do this?


the file can be downloaded from

http://savefile.com/projects/857497

Thanks

David


Max wrote:
"david" wrote:
When I click on the 'New Entry' macro on the 'Y9_Incidents' sheet it
counts the cell reference number down in the 'O' column of the
'Printout' sheet.

I tried making it an absolute rather than a relative reference but I
can't get that to work.

In sheet: Printout,

Try this revised criteria formula* in O6, filled down to O100 (say):
=IF(OR(Control!$C$9="",Control!$C$10="",Control!$C $10=""),"",
IF(INDEX(INDIRECT("'Y9Incidents'!B14:B1000"),ROW(A 1))="","",
IF(AND(INDEX(INDIRECT("'Y9Incidents'!B14:B1000"),R OW(A1))=Control!$C$9,
INDEX(INDIRECT("'Y9Incidents'!D141000"),ROW(A1)) =Control!$C$10,
INDEX(INDIRECT("'Y9Incidents'!D141000"),ROW(A1)) =Control!$C$11),ROW(),"")))

*to cater for new row insertions by the macro in Y9Incidents

(No change to the other formulas in cols A to K)

An implemented sample is available at:
http://www.savefile.com/files/5487340
incident_Book_for_playY9_1.xls

Test it out (works ok here, under light testing)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Max

I (hopefully finally but I can't help fiddling with things) want to
create a page similar to the report for each class that could pick up
the student name from the 'Control' Page and only print the entries for
that student for the date ranges.

I anticipated a validation box on the control page that picks up the
student lists for that clas and then entering the start and end date
for this report.

I expected it would be easy to copy and print the previous report but I
get the student i want but also ohers as well.

I tried to upload thisversion to savefile.com but thre is an error and
I can't do it.

Many thanks

David


david wrote:

Thanks Max this seems to be working OK

One final question and please ignore this if I am taking up too much of
your time.

I have tweaked the file to add summary pages for each of the 4 classes
together with the printout page which pulls its criteria from from the
'Control' sheet.

What I want to be able to do is print this 'Printout' page via a macro.
So I would select the form from the dropdown box on the control page
and then press a macro button. To do this I presume I need to be able
to detect which is the last line that there is any data in so the print
range can be set- how can I do this?


the file can be downloaded from

http://savefile.com/projects/857497

Thanks

David


Max wrote:
"david" wrote:
When I click on the 'New Entry' macro on the 'Y9_Incidents' sheet it
counts the cell reference number down in the 'O' column of the
'Printout' sheet.

I tried making it an absolute rather than a relative reference but I
can't get that to work.

In sheet: Printout,

Try this revised criteria formula* in O6, filled down to O100 (say):
=IF(OR(Control!$C$9="",Control!$C$10="",Control!$C $10=""),"",
IF(INDEX(INDIRECT("'Y9Incidents'!B14:B1000"),ROW(A 1))="","",
IF(AND(INDEX(INDIRECT("'Y9Incidents'!B14:B1000"),R OW(A1))=Control!$C$9,
INDEX(INDIRECT("'Y9Incidents'!D141000"),ROW(A1)) =Control!$C$10,
INDEX(INDIRECT("'Y9Incidents'!D141000"),ROW(A1)) =Control!$C$11),ROW(),"")))

*to cater for new row insertions by the macro in Y9Incidents

(No change to the other formulas in cols A to K)

An implemented sample is available at:
http://www.savefile.com/files/5487340
incident_Book_for_playY9_1.xls

Test it out (works ok here, under light testing)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---