rows to different worksheet

[Antonio]

Hi all

Need help for the following:

Have a worksheet with 3000 rows by 7 columns, filled with numbers that range
from 1 to 3000. Need a way to move a row to a different worksheet if the
number in one specific cell is either odd or even

Can anyone help?

Tks in advance

[Ken Wright]

You can use VLOOKUP though that will be a lot of formulas and a lot of
overhead, or if you just want all the rows on another sheet, then copy your
sheet in it's entirety, add a helper field that uses a formula such as
=ISEVEN(D1) and copy downa s far as your data goes (assuming your field was
in Col D), then sort on the helper column which will now contain just TRUEs
and FALSEs

Delete the FALSEs and you will be left with what you wanted.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"Antonio" <[email protected]> wrote in message
news:[email protected]...
> Hi all
>
> Need help for the following:
>
> Have a worksheet with 3000 rows by 7 columns, filled with numbers that
> range
> from 1 to 3000. Need a way to move a row to a different worksheet if the
> number in one specific cell is either odd or even
>
> Can anyone help?
>
> Tks in advance
>

[Antonio]

Tks Ken

Tks for your prompt reply. It does work but my excel worksheet
has now 7 MB and growing... is there any other way to do it?
a macro maybe?

Tks once again for your kind help
António

"Ken Wright" wrote:

> You can use VLOOKUP though that will be a lot of formulas and a lot of
> overhead, or if you just want all the rows on another sheet, then copy your
> sheet in it's entirety, add a helper field that uses a formula such as
> =ISEVEN(D1) and copy downa s far as your data goes (assuming your field was
> in Col D), then sort on the helper column which will now contain just TRUEs
> and FALSEs
>
> Delete the FALSEs and you will be left with what you wanted.
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> ------------------------------Â*------------------------------Â*----------------
> It's easier to beg forgiveness than ask permission :-)
> ------------------------------Â*------------------------------Â*----------------
>
>
> "Antonio" <[email protected]> wrote in message
> news:[email protected]...
> > Hi all
> >
> > Need help for the following:
> >
> > Have a worksheet with 3000 rows by 7 columns, filled with numbers that
> > range
> > from 1 to 3000. Need a way to move a row to a different worksheet if the
> > number in one specific cell is either odd or even
> >
> > Can anyone help?
> >
> > Tks in advance
> >
>
>
>

[Ken Wright]

Which way did you go? - Was this a one time deal, because once the data has
been transferred you don't need the formulas any more?

Regards
Ken....................


"Antonio" <[email protected]> wrote in message
news:[email protected]...
> Tks Ken
>
> Tks for your prompt reply. It does work but my excel worksheet
> has now 7 MB and growing... is there any other way to do it?
> a macro maybe?
>
> Tks once again for your kind help
> António
>
> "Ken Wright" wrote:
>
>> You can use VLOOKUP though that will be a lot of formulas and a lot of
>> overhead, or if you just want all the rows on another sheet, then copy
>> your
>> sheet in it's entirety, add a helper field that uses a formula such as
>> =ISEVEN(D1) and copy downa s far as your data goes (assuming your field
>> was
>> in Col D), then sort on the helper column which will now contain just
>> TRUEs
>> and FALSEs
>>
>> Delete the FALSEs and you will be left with what you wanted.
>>
>> --
>> Regards
>> Ken....................... Microsoft MVP - Excel
>> Sys Spec - Win XP Pro / XL 97/00/02/03
>>
>> ------------------------------*------------------------------*----------------
>> It's easier to beg forgiveness than ask permission :-)
>> ------------------------------*------------------------------*----------------
>>
>>
>> "Antonio" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi all
>> >
>> > Need help for the following:
>> >
>> > Have a worksheet with 3000 rows by 7 columns, filled with numbers that
>> > range
>> > from 1 to 3000. Need a way to move a row to a different worksheet if
>> > the
>> > number in one specific cell is either odd or even
>> >
>> > Can anyone help?
>> >
>> > Tks in advance
>> >
>>
>>
>>

[Ron de Bruin]

Hi Antonio

Why not use Advanced Filter and copy the visible cells
to another sheet.

You only have to add one formula in your sheet

See
http://www.contextures.com/xladvfilter02.html



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Antonio" <[email protected]> wrote in message news:[email protected]...
> Tks Ken
>
> Tks for your prompt reply. It does work but my excel worksheet
> has now 7 MB and growing... is there any other way to do it?
> a macro maybe?
>
> Tks once again for your kind help
> António
>
> "Ken Wright" wrote:
>
>> You can use VLOOKUP though that will be a lot of formulas and a lot of
>> overhead, or if you just want all the rows on another sheet, then copy your
>> sheet in it's entirety, add a helper field that uses a formula such as
>> =ISEVEN(D1) and copy downa s far as your data goes (assuming your field was
>> in Col D), then sort on the helper column which will now contain just TRUEs
>> and FALSEs
>>
>> Delete the FALSEs and you will be left with what you wanted.
>>
>> --
>> Regards
>> Ken....................... Microsoft MVP - Excel
>> Sys Spec - Win XP Pro / XL 97/00/02/03
>>
>> ------------------------------*------------------------------*----------------
>> It's easier to beg forgiveness than ask permission :-)
>> ------------------------------*------------------------------*----------------
>>
>>
>> "Antonio" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi all
>> >
>> > Need help for the following:
>> >
>> > Have a worksheet with 3000 rows by 7 columns, filled with numbers that
>> > range
>> > from 1 to 3000. Need a way to move a row to a different worksheet if the
>> > number in one specific cell is either odd or even
>> >
>> > Can anyone help?
>> >
>> > Tks in advance
>> >
>>
>>
>>

[Antonio]

Tks to both.

Both ways are workable. Problem is that each time I update the information,
have to re-write the formulas, and copy paste to 40 diferent worksheets.
Is there a way to, when sheet 1 is updated, all others are automaticaly
updated also??

"Antonio" wrote:

> Hi all
>
> Need help for the following:
>
> Have a worksheet with 3000 rows by 7 columns, filled with numbers that range
> from 1 to 3000. Need a way to move a row to a different worksheet if the
> number in one specific cell is either odd or even
>
> Can anyone help?
>
> Tks in advance
>

[Ken Wright]

Yes - Pivot table with a dynamic data source. Wouldn't consider any other
method for that many sheets (other than maybe VBA, but personally prefer
Pivots). Formulas will likely kill your workbook.

Regards
Ken.............................

"Antonio" <[email protected]> wrote in message
news:[email protected]...
> Tks to both.
>
> Both ways are workable. Problem is that each time I update the
> information,
> have to re-write the formulas, and copy paste to 40 diferent worksheets.
> Is there a way to, when sheet 1 is updated, all others are automaticaly
> updated also??
>
> "Antonio" wrote:
>
>> Hi all
>>
>> Need help for the following:
>>
>> Have a worksheet with 3000 rows by 7 columns, filled with numbers that
>> range
>> from 1 to 3000. Need a way to move a row to a different worksheet if
>> the
>> number in one specific cell is either odd or even
>>
>> Can anyone help?
>>
>> Tks in advance
>>

[Antonio]

Hi, me again

Takes too long to do it manually, is there any "formula" to automate the
task??

Kind regards


"Ken Wright" wrote:

> Yes - Pivot table with a dynamic data source. Wouldn't consider any other
> method for that many sheets (other than maybe VBA, but personally prefer
> Pivots). Formulas will likely kill your workbook.
>
> Regards
> Ken.............................
>
> "Antonio" <[email protected]> wrote in message
> news:[email protected]...
> > Tks to both.
> >
> > Both ways are workable. Problem is that each time I update the
> > information,
> > have to re-write the formulas, and copy paste to 40 diferent worksheets.
> > Is there a way to, when sheet 1 is updated, all others are automaticaly
> > updated also??
> >
> > "Antonio" wrote:
> >
> >> Hi all
> >>
> >> Need help for the following:
> >>
> >> Have a worksheet with 3000 rows by 7 columns, filled with numbers that
> >> range
> >> from 1 to 3000. Need a way to move a row to a different worksheet if
> >> the
> >> number in one specific cell is either odd or even
> >>
> >> Can anyone help?
> >>
> >> Tks in advance
> >>
>
>
>

[Ken Wright]

What takes so long? If you use a Pivot table and throw whatever field you
want to use to give you the individual sheets into the page fields, then
just display the Pivot table toolbar and use the show pages option and
choose that field. In about 3 seconds it will create all 40 sheets for you.

Regards
Ken..................

"Antonio" <[email protected]> wrote in message
news:[email protected]...
> Hi, me again
>
> Takes too long to do it manually, is there any "formula" to automate the
> task??
>
> Kind regards
>
>
> "Ken Wright" wrote:
>
>> Yes - Pivot table with a dynamic data source. Wouldn't consider any
>> other
>> method for that many sheets (other than maybe VBA, but personally prefer
>> Pivots). Formulas will likely kill your workbook.
>>
>> Regards
>> Ken.............................
>>
>> "Antonio" <[email protected]> wrote in message
>> news:[email protected]...
>> > Tks to both.
>> >
>> > Both ways are workable. Problem is that each time I update the
>> > information,
>> > have to re-write the formulas, and copy paste to 40 diferent
>> > worksheets.
>> > Is there a way to, when sheet 1 is updated, all others are automaticaly
>> > updated also??
>> >
>> > "Antonio" wrote:
>> >
>> >> Hi all
>> >>
>> >> Need help for the following:
>> >>
>> >> Have a worksheet with 3000 rows by 7 columns, filled with numbers that
>> >> range
>> >> from 1 to 3000. Need a way to move a row to a different worksheet if
>> >> the
>> >> number in one specific cell is either odd or even
>> >>
>> >> Can anyone help?
>> >>
>> >> Tks in advance
>> >>
>>
>>
>>

[Antonio]

Hi Ken

Then I am doing something wrong......
Will try to figure out what!

Tks

"Ken Wright" wrote:

> What takes so long? If you use a Pivot table and throw whatever field you
> want to use to give you the individual sheets into the page fields, then
> just display the Pivot table toolbar and use the show pages option and
> choose that field. In about 3 seconds it will create all 40 sheets for you.
>
> Regards
> Ken..................
>
> "Antonio" <[email protected]> wrote in message
> news:[email protected]...
> > Hi, me again
> >
> > Takes too long to do it manually, is there any "formula" to automate the
> > task??
> >
> > Kind regards
> >
> >
> > "Ken Wright" wrote:
> >
> >> Yes - Pivot table with a dynamic data source. Wouldn't consider any
> >> other
> >> method for that many sheets (other than maybe VBA, but personally prefer
> >> Pivots). Formulas will likely kill your workbook.
> >>
> >> Regards
> >> Ken.............................
> >>
> >> "Antonio" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Tks to both.
> >> >
> >> > Both ways are workable. Problem is that each time I update the
> >> > information,
> >> > have to re-write the formulas, and copy paste to 40 diferent
> >> > worksheets.
> >> > Is there a way to, when sheet 1 is updated, all others are automaticaly
> >> > updated also??
> >> >
> >> > "Antonio" wrote:
> >> >
> >> >> Hi all
> >> >>
> >> >> Need help for the following:
> >> >>
> >> >> Have a worksheet with 3000 rows by 7 columns, filled with numbers that
> >> >> range
> >> >> from 1 to 3000. Need a way to move a row to a different worksheet if
> >> >> the
> >> >> number in one specific cell is either odd or even
> >> >>
> >> >> Can anyone help?
> >> >>
> >> >> Tks in advance
> >> >>
> >>
> >>
> >>
>
>
>

[Ken Wright]

When you create your pivot table you must i assume have a field in your data
that you would use to break the data out into sheets - lets call that field
xyz. Create your Pivot table just the way you want your report to look,
even though it probably contains data for all the values in field xyz.

Now drag field xyz into the page fields and your report will not have
changed at this point.

Now display the pivot table toolbar using view / Toolbars / Pivot table and
you may have to customise it to get it to display the 'Show pages' icon.
Once you find it, click it and it will give you a list of all the fields in
your page field section, and at the moment i would expect this to be just
field xyz. Click this, hit OK and it will instantly create a separate sheet
for every unique value in field xyz.

Regards
Ken....................


"Antonio" <[email protected]> wrote in message
news:[email protected]...
> Hi Ken
>
> Then I am doing something wrong......
> Will try to figure out what!
>
> Tks
>
> "Ken Wright" wrote:
>
>> What takes so long? If you use a Pivot table and throw whatever field
>> you
>> want to use to give you the individual sheets into the page fields, then
>> just display the Pivot table toolbar and use the show pages option and
>> choose that field. In about 3 seconds it will create all 40 sheets for
>> you.
>>
>> Regards
>> Ken..................
>>
>> "Antonio" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi, me again
>> >
>> > Takes too long to do it manually, is there any "formula" to automate
>> > the
>> > task??
>> >
>> > Kind regards
>> >
>> >
>> > "Ken Wright" wrote:
>> >
>> >> Yes - Pivot table with a dynamic data source. Wouldn't consider any
>> >> other
>> >> method for that many sheets (other than maybe VBA, but personally
>> >> prefer
>> >> Pivots). Formulas will likely kill your workbook.
>> >>
>> >> Regards
>> >> Ken.............................
>> >>
>> >> "Antonio" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > Tks to both.
>> >> >
>> >> > Both ways are workable. Problem is that each time I update the
>> >> > information,
>> >> > have to re-write the formulas, and copy paste to 40 diferent
>> >> > worksheets.
>> >> > Is there a way to, when sheet 1 is updated, all others are
>> >> > automaticaly
>> >> > updated also??
>> >> >
>> >> > "Antonio" wrote:
>> >> >
>> >> >> Hi all
>> >> >>
>> >> >> Need help for the following:
>> >> >>
>> >> >> Have a worksheet with 3000 rows by 7 columns, filled with numbers
>> >> >> that
>> >> >> range
>> >> >> from 1 to 3000. Need a way to move a row to a different worksheet
>> >> >> if
>> >> >> the
>> >> >> number in one specific cell is either odd or even
>> >> >>
>> >> >> Can anyone help?
>> >> >>
>> >> >> Tks in advance
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>