Copy data from one sheet to another using a formula

[Jeremy]

I am wanting to put the data in a specific row to worksheet 2 or 3 drivin by
a formula. If row one in the a column has a 2 I want the whole row of data
to copy to sheet 2 and if row one in the a column has a 3 I want the whole
row to copy to worksheet 3. The number of rows I am wanting to do this with
changes so I don't thing recording a macro will work.


A B C
1 2 10 Aunts
2 3 10 Moles
3 2 11 Mice
4 2 12
5 12 Aunts


A1 to C1 would be on sheet 2
A2 to C2 would be on sheet 3
A3 to C3 would be on sheet 2
A4 to C3 would be on sheet 3
A5 to C5 would copy nowhere because there is no data in A5.


Thank you for your help.

Jeremy

 

[Max]

A formulas driven play is available ..
Take away this sample construct from my archives:
http://www.savefile.com/files/430142
AutoCopy Lines to Resp Sht Non Array.xls
(Full details inside, nicely rendered. Easy to adapt ..)

Data is continuously entered in a master ("parent") sheet, with lines neatly
auto-copied to each individual ("child") sheet based on the values within a
key col.

In the sample, the key col in the master sheet is the "State" col, which may
contain eg: NY, CA, NV, SD, AZ, etc. All lines with "NY" in the key col will
be auto-copied to the sheet named: NY, and appear neatly bunched at the top,
w/o any intervening blank lines. Ditto for lines with "CA", "NV", etc which
will be copied into their respective sheets.

Propagation of the "child" sheet is as simple as making a copy of the
initial one, then renaming it accordingly as the next key col value. Eg we
first formulate one child sheet for "NY", dress it up nicely, then just make
copies of the "NY" sheet, and rename these as: CA, NV, SD, etc.

In your case, the key col would be col A with values: 2, 3, etc
and you would name the child sheets simply as: 2, 3, etc

 

[Max]

Some notes for you when you adapt it to suit ..

In WS1,
you need to list the numbers in K1 across as **text numbers**, ie enter it
with a preceding apostrophe, viz.: '2, '3

Then change the formula in K2 slightly to this:
=IF($A2&""=K$1,ROW(),"")
and copy K2 across/fill down