Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default 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..

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,355
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Inputting data to one worksheet for it effect another daedalus1 Excel Discussion (Misc queries) 1 June 25th 06 04:39 PM
Look up Data from Worksheet within same Workbook Max New Users to Excel 0 May 10th 06 06:50 AM
Excel 2002; copy formulas to one workbook to another diff data SinnetBS Excel Worksheet Functions 1 November 25th 05 09:31 PM
Inserting Filtered RC cell information into other worksheets Dennis Excel Discussion (Misc queries) 10 July 30th 05 01:54 AM
Weekly Transaction Processing Ralph Howarth Excel Worksheet Functions 4 January 19th 05 06:37 AM


All times are GMT +1. The time now is 03:47 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"