I am creating a new form that takes employee expenses. On each line of the
expense form the employee can chose employee paid or company paid from a drop
down list.
What I have done is created an IF query to copy the data to a corresponding
sheet (one titled company paid and the other employee paid), depending on
whether company paid or employee paid was chosen, however the part I can not
work out how to do is to ensure that when the data is copied to the new sheet
that the next available line is used rather than a straight linked cell.

Is there anyway to automatically go to the next available line on each sheet
when the data qualifies rather than having blank lines inbetween when a
straight linking command is used and the IF criteria is satisfied.

Try this sample from my archives (full details inside):
http://savefile.com/files/9260836
AutoCopy_Data_To_Resp_Sheet_Non_Array_Approach.xls

It should be quite straightforward to adapt the sample to suit ...

Your 2 DV selections: company paid, employee paid
would correspond to the sample's key col A's colours in WS1 (the "master")

So you would, for eg: list the phrases across in K1:L1, viz.: company paid,
employee paid, then re-name 2 of the 3 "colour" sheets in the sample
correspondingly as: company paid, employee paid

And then whatever's entered progressively into WS1 (the "master")
would be autocopied directly into the 2 sheets by the key col A,
with all lines neatly bunched at the top
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jeff" wrote:
I am creating a new form that takes employee expenses. On each line of the
expense form the employee can chose employee paid or company paid from a drop
down list.
What I have done is created an IF query to copy the data to a corresponding
sheet (one titled company paid and the other employee paid), depending on
whether company paid or employee paid was chosen, however the part I can not
work out how to do is to ensure that when the data is copied to the new sheet
that the next available line is used rather than a straight linked cell.

Is there anyway to automatically go to the next available line on each sheet
when the data qualifies rather than having blank lines inbetween when a
straight linking command is used and the IF criteria is satisfied.

Max,

Thank you so much, that is absolutely perfect.

Cheers

Jeff

"Max" wrote:

Try this sample from my archives (full details inside):
http://savefile.com/files/9260836
AutoCopy_Data_To_Resp_Sheet_Non_Array_Approach.xls

It should be quite straightforward to adapt the sample to suit ...

Your 2 DV selections: company paid, employee paid
would correspond to the sample's key col A's colours in WS1 (the "master")

So you would, for eg: list the phrases across in K1:L1, viz.: company paid,
employee paid, then re-name 2 of the 3 "colour" sheets in the sample
correspondingly as: company paid, employee paid

And then whatever's entered progressively into WS1 (the "master")
would be autocopied directly into the 2 sheets by the key col A,
with all lines neatly bunched at the top
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jeff" wrote:
I am creating a new form that takes employee expenses. On each line of the
expense form the employee can chose employee paid or company paid from a drop
down list.
What I have done is created an IF query to copy the data to a corresponding
sheet (one titled company paid and the other employee paid), depending on
whether company paid or employee paid was chosen, however the part I can not
work out how to do is to ensure that when the data is copied to the new sheet
that the next available line is used rather than a straight linked cell.

Is there anyway to automatically go to the next available line on each sheet
when the data qualifies rather than having blank lines inbetween when a
straight linking command is used and the IF criteria is satisfied.

Glad it was a good fit, Jeff !
Thanks for calling back ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jeff" wrote:
Max,
Thank you so much, that is absolutely perfect.
Cheers
Jeff