1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel Forumula Help

Discussion in 'Archive: Business Applications' started by maracles, Oct 10, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. maracles

    maracles Thread Starter

    Joined:
    Apr 4, 2006
    Messages:
    298
    I have two work books. The first work book has a list of Order Numbers i.e.

    Worksheet 1

    A1 = Order00001
    A2 = Order00001

    and so forth.

    The second work book contains information which relates to a certain order, but does not yet have an order number set. This information is stored in rows i.e.

    Worksheet 2

    Row 1 = Unassigned order 1
    Row 2 = Unassigned order 2

    and so forth.

    These unassigned orders will however, at a later date, be given one of the unique order numbers from worksheet1 . I need a forumla which, when an order number is given to a particular unassigned order, copies the information from that particular unassigned orders into the columns of the same order number in worksheet 1. For example:

    If worksheet2!A1 = "[Unique Order Number]" data from worksheet2 columns A2:A5 are copied to the same columns next to the relevant order number row in worksheet1

    I understand that this may be confusing so please reply if you need clarification.
     
  2. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Well, first, I assume you mean A2 = Order00002.
    Otherwise, it should be just a matter of an Index/Match...can you post a sample file?
    Then I can write the formula(s) you need.
     
  3. maracles

    maracles Thread Starter

    Joined:
    Apr 4, 2006
    Messages:
    298
    I do mean A2 = Order00002, sorry.

    Will try and do a sample file within next 5 mins.

    Thanks for the quick response.
     
  4. maracles

    maracles Thread Starter

    Joined:
    Apr 4, 2006
    Messages:
    298
    I have posted the sample file. As you can see there are two worksheets.


    In the 'Orders' worksheet there are order numbers with no details
    In the 'Unassigned Orders' worksheet there are general enquiries without order numbers.

    I want to be able to enter an order number in the 'Order Number' column of the 'Unassigned Orders' column and then have excel copy the data from that particular row and the columns B, C and D, into the same correspoding order number and columns of worksheet1.

    I.e. If I enter 'abc001' into A2 or the 'Unassigned Orders' worksheet, then cells B2, C2 and D2 get copied into columns B2, C2 and D2 or the 'Orders' worksheet.

    Hope this is clearer!
     

    Attached Files:

  5. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Here you go - as you can see, I randomly assigned some IDs to the Unassigned sheet, just to show the match works.
     

    Attached Files:

  6. maracles

    maracles Thread Starter

    Joined:
    Apr 4, 2006
    Messages:
    298
    Thanks for that! It seems to work perfectly.

    I have to say my posting was two-fold, to receive a solution for the problem (completed) and to be able to deduce how the formula works. I'm quite a beginner at Excel is there any chance you could break down your formula so I can see how it works? This will mean that If I have to make any alterations I can learn for myself.

    Your help has been much appreciated. Thank you.
     
  7. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    It is interesting, actually. :) I have learned a lot at this site - including being able to answer your question, so I understand your curiosity!

    =IF() statements are of the format, IF(some logic test, answer if the test is true, answer if the test is false)
    The first part of this formula, the ISNA is the test that the IF is running - is the subsequent Index/Match test returning an #N/A?
    The (INDEX('Unassigned Orders'!A:D,MATCH(A4,'Unassigned Orders'!A:A,0),2)),"MISSING"
    checks to compare A4 against the following: 'Unassigned Orders'!A:A,0 - in other words, is whatever is in A4 also in column A of the other sheet. It also looks at the array of A:D. The reason for this is so, at the end, the number 2 tells what column matching a find to display, counting from the left. So in this formula it will display the first name. The 0 after the A:A (sometimes you will see FALSE instead) tells it only to show exact matches. Without that, it would show MISSING until it found a match, then show the first match until it found the next match, then show that match until it found the next and so on. So in the file I uploaded, you would have 2 MISSING, then Joe, then 11 more Joes, then Mark....

    Because the IF test is to see if the ISNA is true - and that is based on whether or not there is a match, the answer if it is is MISSING. (It could easily have been anything else, from "" to 0 to "Tough Luck!".)
    If it is not true, then the answer is to show the match - hence the repeated portion, apparently, of INDEX('Unassigned Orders'!A:D,MATCH(A4,'Unassigned Orders'!A:A,0),2))



    Please use the button at the top to mark this thread as Solved. :)
     
  8. maracles

    maracles Thread Starter

    Joined:
    Apr 4, 2006
    Messages:
    298
    Thank you for the explanation, that helped a lot, your right about this forum, it is very helpful!

    One last question, how do I extend the formula to search multiple worksheets? I have made two further worksheets with exactly the same layout as the 'Enquiries' worksheet, and I want to formula to check each of these for new order numbers and update the 'Orders' worksheet accordingly.

    I tried to do this myself but got the error 'You have used too many arguments for this function'
     
  9. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    To be honest, I am not sure. I'll look and see if a better Excel person is on here.
     
  10. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,447
    Nope, no can do. You would either need to embed that many formulas in many IF() functions, but there you're limited to 7 anyway. Or you could have a separate table with the names and in an adjacent column have what sheet/range they were associated with, or you could use a custom VBA function. Those are the only ways to do it. If you'd like one of those solutions, I'm sure we can help, but I'd recommend consolidating your data if you can.
     
  11. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Kind of simplistic, but the only way I could figure out....
     

    Attached Files:

  12. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,447
    Ugh, that's a lot of wasted space and unused formulas. I wouldn't recommend it. I'd go UDF before I went that route. I'd even go the INDIRECT() route before that! LOL! :D
     
  13. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    uh, I said it was simplistic...we can't all be Excel lords.... :p :rolleyes:
     
  14. maracles

    maracles Thread Starter

    Joined:
    Apr 4, 2006
    Messages:
    298
    Cheers guys, have only just been able to check this forum again but thanks for the advice.

    Ideally what i'm doing would be done in access but I have absolutely zero knowledge of Access so that is going to have to wait a while. I will try and rearrange the data to make it more manageable and maybe come to you guys after that if i'm still struggling.
     
  15. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Access? Now you are talking my language more...here is a simple db with the two tables and a query matching them.
     

    Attached Files:

  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/757872