software

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 advanceYou 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 :-)
------------------------------*------------------------------*----------------quot;Antonioquot; gt; wrote in message
...
gt; Hi all
gt;
gt; Need help for the following:
gt;
gt; Have a worksheet with 3000 rows by 7 columns, filled with numbers that
gt; range
gt; from 1 to 3000. Need a way to move a row to a different worksheet if the
gt; number in one specific cell is either odd or even
gt;
gt; Can anyone help?
gt;
gt; Tks in advance
gt;
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

quot;Ken Wrightquot; wrote:

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

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

quot;Antonioquot; wrote:

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

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.............................

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

Takes too long to do it manually, is there any quot;formulaquot; to automate the
task??

Kind regardsquot;Ken Wrightquot; wrote:

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

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..................

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

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

Tks

quot;Ken Wrightquot; wrote:

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