Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy data from one sheet to another using a formula
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy data from one sheet to another using a formula
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 Singapore http://savefile.com/projects/236895 xdemechanik --- "Jeremy" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy data from one sheet to another using a formula
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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy data of two cells from Sheet 2 into one cell in Sheet 1 | Excel Worksheet Functions | |||
How can i copy data from a tabbed working sheet to a summary sheet | Excel Discussion (Misc queries) | |||
Formula Help - Copy data from one sheet to another based on criter | Excel Worksheet Functions | |||
how to copy a cell with formula from sheet 1 (data is all vertical) into sheet 2 | Excel Worksheet Functions | |||
With a formula copy data from one sheet to another | New Users to Excel |