Autocopy of data filtered by drop down list
 

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.

Autocopy of data filtered by drop down list
 

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.

Autocopy of data filtered by drop down list
 

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.

Autocopy of data filtered by drop down list
 

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