Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copy data from 1 worksheet to another based on a condition
Hi I need this information urgently...
Sheet 1 has some data. I put a "IF" condition which when satisfied needs to input data into Sheet 2. But the problem is that if the condition is not satisfied it is copying with blanks.. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copy data from 1 worksheet to another based on a condition
what do you want to do..?? Wrote: Hi I need this information urgently... Sheet 1 has some data. I put a "IF" condition which when satisfied needs to input data into Sheet 2. But the problem is that if the condition is not satisfied it is copying with blanks.. -- starguy ------------------------------------------------------------------------ starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434 View this thread: http://www.excelforum.com/showthread...hreadid=568272 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copy data from 1 worksheet to another based on a condition
I'm thinking you may need to do this with VBA. You may want to ask your
question in the PROGRAMMING group. " wrote: Hi I need this information urgently... Sheet 1 has some data. I put a "IF" condition which when satisfied needs to input data into Sheet 2. But the problem is that if the condition is not satisfied it is copying with blanks.. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copy data from 1 worksheet to another based on a condition
Could do with a bit more detail, but your IF statement should be of
this form: =IF(your_condition, action_if_true, action_if_false) the "action_if_false" may be set to "" (i.e. return blanks), so you will need to amend this part if you do not want blanks when the condition is not met. Hope this helps. Pete wrote: Hi I need this information urgently... Sheet 1 has some data. I put a "IF" condition which when satisfied needs to input data into Sheet 2. But the problem is that if the condition is not satisfied it is copying with blanks.. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copy data from 1 worksheet to another based on a condition
Hi this is exactly what i am looking for..
I have A-sheet which has columns as below - Dept group offer abc aa reject xyz bb accept So I need all the the data from dept and group on B-sheet if the offer is accept. If I put a plain 'if' formula then the data is getting copied to the same row as in A-sheet leaving blank rows in between.. Pete_UK wrote: Could do with a bit more detail, but your IF statement should be of this form: =IF(your_condition, action_if_true, action_if_false) the "action_if_false" may be set to "" (i.e. return blanks), so you will need to amend this part if you do not want blanks when the condition is not met. Hope this helps. Pete wrote: Hi I need this information urgently... Sheet 1 has some data. I put a "IF" condition which when satisfied needs to input data into Sheet 2. But the problem is that if the condition is not satisfied it is copying with blanks.. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copy data from 1 worksheet to another based on a condition
One way to achieve this using non-array formulas.
Source data assumed in sheet: A, cols A to C, data from row2 down. Col C = key col with the values either: offer or accept In sheet: B, Create a DV in A1 to select the options: offer or accept (Data Validation, Allow: List, Source: accept, reject) Paste the same col labels into B1:D1 In A2: =IF($A$1="","",IF(A!C2="","",IF(A!C2=$A$1,ROW(),"" ))) In B2: =IF(ROW(A1)COUNT($A:$A),"",INDEX(A!A:A,MATCH(SMAL L($A:$A,ROW(A1)),$A:$A,0))) Copy B2 to D2 Then just select A2:D2 and fill down to cover the max expected extent of data in A. The required results will appear in cols B to D, all neatly bunched at the top, ie only lines corresponding to the selection made in the DV in A1. If "accept" is selected, then only "accept" lines will appear. Ditto for "reject". -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- " wrote: Hi this is exactly what i am looking for.. I have A-sheet which has columns as below - Dept group offer abc aa reject xyz bb accept So I need all the the data from dept and group on B-sheet if the offer is accept. If I put a plain 'if' formula then the data is getting copied to the same row as in A-sheet leaving blank rows in between.. Pete_UK wrote: Could do with a bit more detail, but your IF statement should be of this form: =IF(your_condition, action_if_true, action_if_false) the "action_if_false" may be set to "" (i.e. return blanks), so you will need to amend this part if you do not want blanks when the condition is not met. Hope this helps. Pete wrote: Hi I need this information urgently... Sheet 1 has some data. I put a "IF" condition which when satisfied needs to input data into Sheet 2. But the problem is that if the condition is not satisfied it is copying with blanks.. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copy data from 1 worksheet to another based on a condition
Here's a sample construct for the foregoing:
http://cjoint.com/?ixpVOLwU05 Autocopy cases by key col value to new sht.xls (Link is good for 14 days) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copy data from 1 worksheet to another based on a condition
Here's a sample construct for the foregoing:
http://cjoint.com/?ixpVOLwU05 Autocopy cases by key col value to new sht.xls (Link is good for 14 days) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Max" wrote: One way to achieve this using non-array formulas. Source data assumed in sheet: A, cols A to C, data from row2 down. Col C = key col with the values either: offer or accept In sheet: B, Create a DV in A1 to select the options: offer or accept (Data Validation, Allow: List, Source: accept, reject) Paste the same col labels into B1:D1 In A2: =IF($A$1="","",IF(A!C2="","",IF(A!C2=$A$1,ROW(),"" ))) In B2: =IF(ROW(A1)COUNT($A:$A),"",INDEX(A!A:A,MATCH(SMAL L($A:$A,ROW(A1)),$A:$A,0))) Copy B2 to D2 Then just select A2:D2 and fill down to cover the max expected extent of data in A. The required results will appear in cols B to D, all neatly bunched at the top, ie only lines corresponding to the selection made in the DV in A1. If "accept" is selected, then only "accept" lines will appear. Ditto for "reject". -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- " wrote: Hi this is exactly what i am looking for.. I have A-sheet which has columns as below - Dept group offer abc aa reject xyz bb accept So I need all the the data from dept and group on B-sheet if the offer is accept. If I put a plain 'if' formula then the data is getting copied to the same row as in A-sheet leaving blank rows in between.. Pete_UK wrote: Could do with a bit more detail, but your IF statement should be of this form: =IF(your_condition, action_if_true, action_if_false) the "action_if_false" may be set to "" (i.e. return blanks), so you will need to amend this part if you do not want blanks when the condition is not met. Hope this helps. Pete wrote: Hi I need this information urgently... Sheet 1 has some data. I put a "IF" condition which when satisfied needs to input data into Sheet 2. But the problem is that if the condition is not satisfied it is copying with blanks.. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copy data from 1 worksheet to another based on a condition
Thanks a ton...
Max wrote: Here's a sample construct for the foregoing: http://cjoint.com/?ixpVOLwU05 Autocopy cases by key col value to new sht.xls (Link is good for 14 days) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Max" wrote: One way to achieve this using non-array formulas. Source data assumed in sheet: A, cols A to C, data from row2 down. Col C = key col with the values either: offer or accept In sheet: B, Create a DV in A1 to select the options: offer or accept (Data Validation, Allow: List, Source: accept, reject) Paste the same col labels into B1:D1 In A2: =IF($A$1="","",IF(A!C2="","",IF(A!C2=$A$1,ROW(),"" ))) In B2: =IF(ROW(A1)COUNT($A:$A),"",INDEX(A!A:A,MATCH(SMAL L($A:$A,ROW(A1)),$A:$A,0))) Copy B2 to D2 Then just select A2:D2 and fill down to cover the max expected extent of data in A. The required results will appear in cols B to D, all neatly bunched at the top, ie only lines corresponding to the selection made in the DV in A1. If "accept" is selected, then only "accept" lines will appear. Ditto for "reject". -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- " wrote: Hi this is exactly what i am looking for.. I have A-sheet which has columns as below - Dept group offer abc aa reject xyz bb accept So I need all the the data from dept and group on B-sheet if the offer is accept. If I put a plain 'if' formula then the data is getting copied to the same row as in A-sheet leaving blank rows in between.. Pete_UK wrote: Could do with a bit more detail, but your IF statement should be of this form: =IF(your_condition, action_if_true, action_if_false) the "action_if_false" may be set to "" (i.e. return blanks), so you will need to amend this part if you do not want blanks when the condition is not met. Hope this helps. Pete wrote: Hi I need this information urgently... Sheet 1 has some data. I put a "IF" condition which when satisfied needs to input data into Sheet 2. But the problem is that if the condition is not satisfied it is copying with blanks.. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copy data from 1 worksheet to another based on a condition
You're welcome !
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- " wrote: Thanks a ton... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
Look up Data from Worksheet within same Workbook | New Users to Excel | |||
Excel 2002; copy formulas to one workbook to another diff data | Excel Worksheet Functions | |||
Inserting Filtered RC cell information into other worksheets | Excel Discussion (Misc queries) | |||
Weekly Transaction Processing | Excel Worksheet Functions |