Results 1 to 8 of 8

Thread: copy Past with matching ceriteria in other sheet

  1. #1

    copy Past with matching ceriteria in other sheet



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have attached a work book containing 3 sheets

    Reciept
    Issuance
    Report

    Data is maintain on daily basis in Receipt/Issuance sheet and summary comes on report.

    I want when i change date on report some information copy from Receipt and issuance and past on Report.

    further more is it possible when I write any criteria at the end of row and row is locked for editing.

    File is attached.


    Kindly resolve this issue

    Regards

    NH
    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    So... you just want to look up the values from other tables where the date matches? We can do that with a VLOOKUP() funciton. What do you want to do if there are no matches with the date provided?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Quote Originally Posted by Ken Puls View Post
    So... you just want to look up the values from other tables where the date matches? We can do that with a VLOOKUP() funciton. What do you want to do if there are no matches with the date provided?

    if no value found then cell remain blank.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    I'm not seeing that...

    With your initial table, I can see that the Item Code, Item Description and UOM are all in the Receipt table. I'm not sure where your SR#, Opening Balance or other fields pull from.

    In the second table I can locate all information in the Receipt table.

    In the third table though... you have this filled in using the records for June 1, where all other records are for June 10. Did you want the Issuance information table left blank then, or are there different rules to populate it?

    PS: I've moved your thread to a more appropriate subforum as well.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Quote Originally Posted by Ken Puls View Post
    I'm not seeing that...

    With your initial table, I can see that the Item Code, Item Description and UOM are all in the Receipt table. I'm not sure where your SR#, Opening Balance or other fields pull from.

    In the second table I can locate all information in the Receipt table.

    In the third table though... you have this filled in using the records for June 1, where all other records are for June 10. Did you want the Issuance information table left blank then, or are there different rules to populate it?

    PS: I've moved your thread to a more appropriate subforum as well.
    Let me explain you.

    First sheet is report sheet with daily receipt & issuance summery as well as detail as mentioned below 2 table like receipt record & issuance record.

    We need data from Receipt sheet and issuance sheet.

    When date change on report sheet reciept and issuance record changes as per posted in two sheet (Rec/Issuance)

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    I got that part, but you really didn't answer my questions.

    Here, I have formatted both of your tables as Excel Tables, and plumbed in the VLOOKUP(MATCH()) combinations where I can.

    The yellow cells do not have formulas, as I cannot figure out where those values come from.

    The third table has formulas, but they are returning blank because there are no records for June 10 in the tlbIssuance (red) table.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    Quote Originally Posted by Ken Puls View Post
    I got that part, but you really didn't answer my questions.

    Here, I have formatted both of your tables as Excel Tables, and plumbed in the VLOOKUP(MATCH()) combinations where I can.

    The yellow cells do not have formulas, as I cannot figure out where those values come from.

    The third table has formulas, but they are returning blank because there are no records for June 10 in the tlbIssuance (red) table.


    if we want to extract data of June 02 on report sheet. You can see on report on issuance table it shows only one record {73523-buss} Actually there is two record in red table (Issuance)

    kindly see and advise

  8. #8
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Alright, so will there only ever be a maximum of 2 records to be pulled back, or could there be more?

    If only 2, we can do this with a formula. If the number of records will vary, I'd recommend that you go with a couple of PivotTables, and tie them together using a macro. The second is a lot more work than the first.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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