Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy Data from Worksheet1 to Worksheet2 IF Worksheet1 Column D = Y
Hi Experts:
What I would like to do is copy the data from Worksheet1 titled "JanObs" and paste it to Worksheet2 titled "HOA Forms" IF Column D on "JanObs" = Y How can I do this either with formula or Macro? Any help would be appreciated. -- jeannie v |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy Data from Worksheet1 to Worksheet2 IF Worksheet1 Column D = Y
Formula entered in A1 of HOA Forms sheet.
=IF(JanObs!D1="Y",JanObs!D1,"") Drag/copy down as far as you wish. Gord Dibben MS Excel MVP On Thu, 24 Jan 2008 15:14:29 -0800, jeannie v wrote: Hi Experts: What I would like to do is copy the data from Worksheet1 titled "JanObs" and paste it to Worksheet2 titled "HOA Forms" IF Column D on "JanObs" = Y How can I do this either with formula or Macro? Any help would be appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy Data from Worksheet1 to Worksheet2 IF Worksheet1 Column D = Y
One way ..
Assume data starts in row2 down in JanObs, In HOA Forms, Put in A2: =IF(JanObs!D2="Y",ROW(),"") Leave A1 blank Put in B2: =IF(ROWS($1:1)COUNT($A:$A),"",INDEX(JanObs!A:A,SM ALL($A:$A,ROWS($1:1)))) Copy B2 across by as many cols as there is data in JanObs to bring over, eg across to K2. Then select A2:K2, copy down to cover the max extent that data is expected in JanObs. Minimize/hide away col A. Cols B to K will return the required results with all lines neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "jeannie v" wrote: Hi Experts: What I would like to do is copy the data from Worksheet1 titled "JanObs" and paste it to Worksheet2 titled "HOA Forms" IF Column D on "JanObs" = Y How can I do this either with formula or Macro? Any help would be appreciated. -- jeannie v |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy Data from Worksheet1 to Worksheet2 IF Worksheet1 Column D
Hi Max:
I should have told you that the Columns are not in the same sequence on both Worksheets headings that cannot be changed....How would I get it to pop the correct data to Worksheet2 for all records on Worksheet1 Column D= Y. So This is Worksheet1 (JanObs) Columns are headed as: COLUMN HEADER A # B Name C Score Deleted D Deleted HS E Rep F Site G Date Deleted H Date Scored I Reason J Deleted by K Per Worksheet2 (HOA Forms) A Name B Blank C Rep D Site E Date Deleted F Date Scored G Reason H Deleted by I would appreciate any help you can provide......I can use the formulas that you provided for another document though, so thank you for that. -- jeannie v "Max" wrote: One way .. Assume data starts in row2 down in JanObs, In HOA Forms, Put in A2: =IF(JanObs!D2="Y",ROW(),"") Leave A1 blank Put in B2: =IF(ROWS($1:1)COUNT($A:$A),"",INDEX(JanObs!A:A,SM ALL($A:$A,ROWS($1:1)))) Copy B2 across by as many cols as there is data in JanObs to bring over, eg across to K2. Then select A2:K2, copy down to cover the max extent that data is expected in JanObs. Minimize/hide away col A. Cols B to K will return the required results with all lines neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "jeannie v" wrote: Hi Experts: What I would like to do is copy the data from Worksheet1 titled "JanObs" and paste it to Worksheet2 titled "HOA Forms" IF Column D on "JanObs" = Y How can I do this either with formula or Macro? Any help would be appreciated. -- jeannie v |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy Data from Worksheet1 to Worksheet2 IF Worksheet1 Column D
Hi Gord:
I left out some important information! Would you please take a look at my response to Max's solution and see if you can help me further? Thank you for your expertise. -- jeannie v "Gord Dibben" wrote: Formula entered in A1 of HOA Forms sheet. =IF(JanObs!D1="Y",JanObs!D1,"") Drag/copy down as far as you wish. Gord Dibben MS Excel MVP On Thu, 24 Jan 2008 15:14:29 -0800, jeannie v wrote: Hi Experts: What I would like to do is copy the data from Worksheet1 titled "JanObs" and paste it to Worksheet2 titled "HOA Forms" IF Column D on "JanObs" = Y How can I do this either with formula or Macro? Any help would be appreciated. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy Data from Worksheet1 to Worksheet2 IF Worksheet1 Column D
.. Columns are not in the same sequence
Quite ok here, think you just need to tweak it a little so that the returned cols will sync in the destination sheet, as shown in this sample: http://www.freefilehosting.net/download/3b1i9 Conditionally extract lines to another sht.xls In HOA Forms, In A2: =IF(JanObs!D2="Y",ROW(),"") Leave A1 blank In B2: =IF(ROWS($1:1)COUNT($A:$A),"",INDEX(JanObs!B:B,SM ALL($A:$A,ROWS($1:1)))) Leave C2 blank, since you indicate the col header in C1 is "Blank" In D2: =IF(ROWS($1:1)COUNT($A:$A),"",INDEX(JanObs!E:E,SM ALL($A:$A,ROWS($1:1)))) Copy D2 to I2. Then select A2:I2, copy down to cover the max expected extent of data in JanObs, say down to I500. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "jeannie v" wrote: Hi Max: I should have told you that the Columns are not in the same sequence on both Worksheets headings that cannot be changed....How would I get it to pop the correct data to Worksheet2 for all records on Worksheet1 Column D= Y. So This is Worksheet1 (JanObs) Columns are headed as: COLUMN HEADER A # B Name C Score Deleted D Deleted HS E Rep F Site G Date Deleted H Date Scored I Reason J Deleted by K Per Worksheet2 (HOA Forms) A Name B Blank C Rep D Site E Date Deleted F Date Scored G Reason H Deleted by I would appreciate any help you can provide......I can use the formulas that you provided for another document though, so thank you for that. -- jeannie v |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy Data from Worksheet1 to Worksheet2 IF Worksheet1 Column D
Good Morning, Max:
Thank you so much....This works....Once I began to think it out, it makes perfect sense and is so easy. I can use this method over and over again. I appreciate your expertise! -- jeannie v "Max" wrote: .. Columns are not in the same sequence Quite ok here, think you just need to tweak it a little so that the returned cols will sync in the destination sheet, as shown in this sample: http://www.freefilehosting.net/download/3b1i9 Conditionally extract lines to another sht.xls In HOA Forms, In A2: =IF(JanObs!D2="Y",ROW(),"") Leave A1 blank In B2: =IF(ROWS($1:1)COUNT($A:$A),"",INDEX(JanObs!B:B,SM ALL($A:$A,ROWS($1:1)))) Leave C2 blank, since you indicate the col header in C1 is "Blank" In D2: =IF(ROWS($1:1)COUNT($A:$A),"",INDEX(JanObs!E:E,SM ALL($A:$A,ROWS($1:1)))) Copy D2 to I2. Then select A2:I2, copy down to cover the max expected extent of data in JanObs, say down to I500. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "jeannie v" wrote: Hi Max: I should have told you that the Columns are not in the same sequence on both Worksheets headings that cannot be changed....How would I get it to pop the correct data to Worksheet2 for all records on Worksheet1 Column D= Y. So This is Worksheet1 (JanObs) Columns are headed as: COLUMN HEADER A # B Name C Score Deleted D Deleted HS E Rep F Site G Date Deleted H Date Scored I Reason J Deleted by K Per Worksheet2 (HOA Forms) A Name B Blank C Rep D Site E Date Deleted F Date Scored G Reason H Deleted by I would appreciate any help you can provide......I can use the formulas that you provided for another document though, so thank you for that. -- jeannie v |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy Data from Worksheet1 to Worksheet2 IF Worksheet1 Column D
Welcome, Jeannie
Glad to hear that -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "jeannie v" wrote in message ... Good Morning, Max: Thank you so much....This works....Once I began to think it out, it makes perfect sense and is so easy. I can use this method over and over again. I appreciate your expertise! -- jeannie v |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to add column from worksheet1.xls to column in worksheet2.xls | Excel Discussion (Misc queries) | |||
CmdButton on worksheet1 missing | Excel Discussion (Misc queries) | |||
columns on worksheet2 don't sort with worksheet1 | Excel Worksheet Functions | |||
linking worksheet1 to worksheet1 | New Users to Excel | |||
IF WorkSheet1 A1=1 then put value of WS1 B2 in WS2 C3 else donothi | Excel Worksheet Functions |