+ Reply to Thread
Results 1 to 54 of 54

how to Copy Data From One Sheet To Another With Condition

  1. #1
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Chennai
    MS-Off Ver
    Excel 2003
    Posts
    119

    how to Copy Data From One Sheet To Another With Condition

    Hi all,

    I'm trying to copy certain data from one sheet to another sheet meeting a condition. sample sheet attached.

    Leave data is list the list of leave taken by the employees. Meeting the Employee No as condition, when entered in 'Leave card' sheet, i want to copy the data automatically in the highlighted cell. this will help me to get the complete leave details of an employee in a prescribed format. i not used with VBA or macros, so want to get it done using formulas.

    Kindly help.
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,408

    Re: how to Copy Data From One Sheet To Another With Condition

    I see that you have responded on another thread to which I contributed, and here's another one you can look at from yesterday:

    http://www.excelforum.com/excel-form...xed-value.html

    Set up a drop-down in cell D12 of your leave card sheet so that you can choose the employee number, and then use a helper column (H) in both sheets similar to the example I've just given.

    Hope this helps.

    Pete

  3. #3
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Chennai
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: how to Copy Data From One Sheet To Another With Condition

    Hi pete,

    This is not what i'm exactly looking for. kindly check the sample sheet which i attached in the thread. help me the workings that could suit to my requirement.

    Divya...

  4. #4
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: how to Copy Data From One Sheet To Another With Condition

    Another way

    ARRAY formula in A20 and copy down. Change the INDEX reference for the others.


    =INDEX('Leave data'!D$2:$D22,SMALL(IF('Leave data'!$B$2:$B$22=$D$12,ROW('Leave data'!D$2:D$22)-1),ROW('Leave data'!D1)))
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  5. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: how to Copy Data From One Sheet To Another With Condition

    Please confirm what is your exact excel version is it 2003? or may be 2007?


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  6. #6
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Chennai
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: how to Copy Data From One Sheet To Another With Condition

    its version 2003

  7. #7
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: how to Copy Data From One Sheet To Another With Condition

    Please refer the attached file for details... The formula is something same like Fotis1991 suggestion... just added some few more workouts.

  8. #8
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Chennai
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: how to Copy Data From One Sheet To Another With Condition

    Fotis, could you explain me abt the index fuction function used here. it works perfectly bt i need to understand the function to change its reference. this would help to understand better

  9. #9
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Chennai
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: how to Copy Data From One Sheet To Another With Condition

    @ sixthsense.

    this really works well and that is exactly what I want do with. Appreciating your help. But if could explain me the workings, that wud be even helpful for my understanding, as i'm a beginner in using excel fucntions

  10. #10
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: how to Copy Data From One Sheet To Another With Condition

    =INDEX('Leave data'!D$2:$D22,SMALL(IF('Leave data'!$B$2:$B$22=$D$12,ROW('Leave data'!D$2:D$22)-1),ROW('Leave data'!D1)))

    The small if part of the formula is the part that "tells" to the formula which is the condition. So the condition is in
    'Leave data'!$B$2:$B$22, looks for ids that are equal to D12.

    The INDEX part, the row Part give the column that formula must search to find the expected results. The -1 part of the formula shows in which row formula must start searching. It's -1 bexause your data start in row 2. If your data start in row 10..this part should be -9 ! Finally the D1 part, which when you copy down becomes D2 ,D3 etc...gives the first instance of maching data the second the third etc...

    Hope that be clear

  11. #11
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: how to Copy Data From One Sheet To Another With Condition

    Glad it helps you and thanks for the feedback

    Please refer Post #6 of the below thread for details explanation

    http://www.excelforum.com/excel-form...-workbook.html

    Edit: Fotis1991 already posted the detailed explanation in Post #10

  12. #12
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Chennai
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: how to Copy Data From One Sheet To Another With Condition

    Guys, thanks a ton !

  13. #13
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: how to Copy Data From One Sheet To Another With Condition

    ................

  14. #14
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Chennai
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: how to Copy Data From One Sheet To Another With Condition

    Guys, to add up with this. Could you help to get the same for specific period.

    Say i have the leave data of the employees from the day the joined. now how to change the same workings so that i could the result for specific period like leave availed report for the period 1st May to 31st May

  15. #15
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: how to Copy Data From One Sheet To Another With Condition

    Please keep two cells (i.e. for Start Date and End Date) and confirm us which will be that cells?

  16. #16
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Chennai
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: how to Copy Data From One Sheet To Another With Condition

    D9 would be the Start date and F9 would be the end date

  17. #17
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: how to Copy Data From One Sheet To Another With Condition

    =INDEX('Leave data'!D$2:$D22,SMALL(IF(('Leave data'!$B$2:$B$22=$D$12)*('Leave data'!$D$2:$D$22>=$d$9)*('Leave data'!$D$2:$D$22<=$f$9),ROW('Leave data'!D$2:D$22)-1),ROW('Leave data'!D1)))

  18. #18
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Chennai
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: how to Copy Data From One Sheet To Another With Condition

    is that rite?

    IF(ISERR(SMALL(IF('Leave data'!$B$2:$B$22=$D$12,ROW('Leave data'!$B$2:$B$22)),ROW($A1))),"",INDEX('Leave data'!$D$2:$D$22,SMALL(IF(('Leave data'!$B$2:$B$22=$D$12)*('Leave data'!$D$2:$D$22>=$D$9)*('Leave data'!$D$2:$D$22<=$F$9),ROW('Leave data'!$B$2:$B$22)-ROW('Leave data'!$B$2)+1),ROW($A1))))

  19. #19
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: how to Copy Data From One Sheet To Another With Condition

    No....This part should come inside the Iserr(Highlited Text) function

    =INDEX('Leave data'!D$2:$D22,SMALL(IF(('Leave data'!$B$2:$B$22=$D$12)*('Leave data'!$D$2:$D$22>=$d$9)*('Leave data'!$D$2:$D$22<=$f$9),ROW('Leave data'!D$2:D$22)-1),ROW('Leave data'!D1)))

  20. #20
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Chennai
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: how to Copy Data From One Sheet To Another With Condition

    the workings which i got from sixthsense suits me better, check the attachment in his previous reply. Help to add up the date criteria into that

  21. #21
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: how to Copy Data From One Sheet To Another With Condition

    Fotis1991 suggestion will work fine too... you just made a mistake when you try to add the error handler. Please refer Post #19 to know how to add error handler in Fotis1991 suggested formula

  22. #22
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: how to Copy Data From One Sheet To Another With Condition

    =IF(ISERROR(INDEX('Leave data'!D$2:$D22;SMALL(IF(('Leave data'!$B$2:$B$22=$D$12)*('Leave data'!$D$2:$D$22>=$D$9)*('Leave data'!$D$2:$D$22<=$F$9);ROW('Leave data'!D$2:D$22)-1);ROW('Leave data'!D1))));"";INDEX('Leave data'!D$2:$D22;SMALL(IF(('Leave data'!$B$2:$B$22=$D$12)*('Leave data'!$D$2:$D$22>=$D$9)*('Leave data'!$D$2:$D$22<=$F$9);ROW('Leave data'!D$2:D$22)-1);ROW('Leave data'!D1))))
    Attached Files Attached Files

  23. #23
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Chennai
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: how to Copy Data From One Sheet To Another With Condition

    IF(ISERR(SMALL(IF(('Leave data'!$B$2:$B$23=$D$12)*('Leave data'!$D$2:$D$23>=$D$9)*('Leave data'!$D$2:$D$23<=$F$9),ROW('Leave data'!$B$2:$B$23)),ROW($A1))),"",INDEX('Leave data'!$D$2:$D$23,SMALL(IF(('Leave data'!$B$2:$B$23=$D$12)*('Leave data'!$D$2:$D$23>=$D$9)*('Leave data'!$D$2:$D$23<=$F$9),ROW('Leave data'!$B$2:$B$23)-ROW('Leave data'!$B$2)+1),ROW($A1))))


    is that correct now????

  24. #24
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: how to Copy Data From One Sheet To Another With Condition

    Quote Originally Posted by divi123 View Post
    is that correct now????
    Seems to be perfect now....

  25. #25
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Chennai
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: how to Copy Data From One Sheet To Another With Condition

    Guys, i got this done... thanks...

    just asking whethr its possible to hide this formula or to make this smaller.. bcoz the master sheet which i'm working seems to be cozy

  26. #26
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: how to Copy Data From One Sheet To Another With Condition

    Suggestions:

    1) Helper columns using regular formulas(not ARRAY)(See Pete's suggestion-Post#2)

    2)VBA

  27. #27
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: how to Copy Data From One Sheet To Another With Condition

    And

    If possible upgrading to higher (latest) version after 2003 will help you to use

    Iferror(YourFormulaHere,"TextToBeShownInCaseOfError")

    will reduce the formula length considerably

  28. #28
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Chennai
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: how to Copy Data From One Sheet To Another With Condition

    As i'm a beginner i was not able to understand the workings suggested by Pete and in turn i'm not used with VBA too... the working which you suggested is much better, only thing is to lock the all cells that has the formulas except D12 in which the value is to be entered. so that the formulas will not be changed or disturbed

  29. #29
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: how to Copy Data From One Sheet To Another With Condition

    One way.

    Choose D12. >>right click>>format cell>>protection>>uncheck the lock option.

    Then protect the whole sheet..

  30. #30
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Chennai
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: how to Copy Data From One Sheet To Another With Condition

    Thanks guys........will catch u soon with my next query.........

  31. #31
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Chennai
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: how to Copy Data From One Sheet To Another With Condition

    Hi , again too add up more with this...

    now I have leave credit data similar like leave data.. as we have done with the leave data, i want the leave credit details to be added in the Leave card. attached sheet for your reference, kindly help to generate Leave card when employee number is entered
    Attached Files Attached Files

  32. #32
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: how to Copy Data From One Sheet To Another With Condition

    Please refer the attached file for solution...

    I am not sure what is your expected output in Leave Applied Area
    Attached Files Attached Files

  33. #33
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Chennai
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: how to Copy Data From One Sheet To Another With Condition

    it is as simple as that, i want to get the datas from both the sheets i.e) from the sheets 'Leave data' and 'Leave credit' into 'Leave card' when the employee number is entered

  34. #34
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Chennai
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: how to Copy Data From One Sheet To Another With Condition

    Am i making it clear for U?

  35. #35
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: how to Copy Data From One Sheet To Another With Condition

    No it is not the right method, I believe. Because you are retrieving the dates from Leave Credit and showing the Leave Availed which is arrived from Leave Data will be treated as these leaves are availed on the dates which is showing in Column-A.

    I hope you understand the situation...

  36. #36
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Chennai
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: how to Copy Data From One Sheet To Another With Condition

    it is not that way.. pls look into the attached file for the format

    firstly meeting the condition of employee no. i want to to pull up data from Leave data and leave credit sheets. in the prescribed format.
    Attached Files Attached Files

  37. #37
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: how to Copy Data From One Sheet To Another With Condition

    Thanks for the explanation, please refer the attached file for exact solution

    PS: But it is seems to be a hectic method
    Attached Files Attached Files

  38. #38
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Chennai
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: how to Copy Data From One Sheet To Another With Condition

    realy hectic yar...

    is there any other solution?

    Just want to have the entire leave availed and leave credit report of employee, when his employee number is entered.. and meeting the condition of given date

  39. #39
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Chennai
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: how to Copy Data From One Sheet To Another With Condition

    think i'm bothering you so much... but highly in need of your help

  40. #40
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: how to Copy Data From One Sheet To Another With Condition

    From my point of view Pivot Table is the best way based on your data....

    But at present I am having some urgent job needs to be finished if you don't mind please wait so that I can show how it can be done easily. But I need atleast 4 to 5 hours to finish my currently assigned job

  41. #41
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Chennai
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: how to Copy Data From One Sheet To Another With Condition

    hmmmm will wait..... and will come back after 5hrs..........

  42. #42
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: how to Copy Data From One Sheet To Another With Condition

    This is my level best code to mask the formula with UDF...

    Please Login or Register  to view this content.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Refer the B5 cell for details and the formula cells for details...

    Refer the attached file for details.

    I got this evaluation method with the help of the below thread
    http://www.excelforum.com/excel-prog...la-in-vba.html
    Attached Files Attached Files

  43. #43
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Chennai
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: how to Copy Data From One Sheet To Another With Condition

    Heart felt thanks for the interest and efforts you have taken to solve my query. This formula really seems better and easy to understand but when i write up in the actual sheet i'm getting #Name? error.. pls help..

  44. #44
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: how to Copy Data From One Sheet To Another With Condition

    Thanks for your feedback and request you to attach a sample file without any confidential data for better understanding and testing purpose.

  45. #45
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: how to Copy Data From One Sheet To Another With Condition

    Have you copied the given code in a standard module of your workbook?

  46. #46
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Chennai
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: how to Copy Data From One Sheet To Another With Condition

    pls find the attachment
    Attached Files Attached Files

  47. #47
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Chennai
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: how to Copy Data From One Sheet To Another With Condition

    whats that mean by standard module?

  48. #48
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: how to Copy Data From One Sheet To Another With Condition

    My Post #45 assumption is right... you have not copied the provided code in your file

    Refer the below link to know how to add code in regular module
    http://www.contextures.com/xlvba01.html

    Refer the attached file in which I have included the UDF code.
    Attached Files Attached Files

  49. #49
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Chennai
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: how to Copy Data From One Sheet To Another With Condition

    thanks its done now.. also help me how to get the report displayed by ascending the dates irrespective of the data. report format attached
    Attached Files Attached Files

  50. #50
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: how to Copy Data From One Sheet To Another With Condition

    Don't know what needs to be shown in Ascending Order

  51. #51
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Chennai
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: how to Copy Data From One Sheet To Another With Condition

    think i'm confusing... after getting the data from both sheets into leave card, i want the data (date) to be sorted in ascending order automatically

  52. #52
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Chennai
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: how to Copy Data From One Sheet To Another With Condition

    did i made it clear?

  53. #53
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: how to Copy Data From One Sheet To Another With Condition

    I believe the data shown in the same way in post # 49 of the screenshot

  54. #54
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Chennai
    MS-Off Ver
    Excel 2003
    Posts
    119

    Re: how to Copy Data From One Sheet To Another With Condition

    the screen shot posted in post #49, is the sample and its done manualy...i want things to be sorted in that way whereas my results are

    15-Apr-13 Leave Credit - 1st Month
    15-May13 Leave Credit - 2nd Month

    29-Apr-13 CL
    30-Apr-13 Loss of Pay
    13-May-13 Loss of Pay

    want the final report to be sorted by date

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1