[maracles]

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.

[slurpee55]

Originally Posted by maracles

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.

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.

[maracles]

I do mean A2 = Order00002, sorry.

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

Thanks for the quick response.

[maracles]

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!

[slurpee55]

Here you go - as you can see, I randomly assigned some IDs to the Unassigned sheet, just to show the match works.

[maracles]

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.

[slurpee55]

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,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. 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,MATCH(A4,'Unassigned Orders'!A:A,0),2))



Please use the button at the top to mark this thread as Solved.

[maracles]

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'

[slurpee55]

To be honest, I am not sure. I'll look and see if a better Excel person is on here.

[Zack Barresse]

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.

[slurpee55]

Kind of simplistic, but the only way I could figure out....

[Zack Barresse]

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!

[slurpee55]

uh, I said it was simplistic...we can't all be Excel lords....

[maracles]

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.

[slurpee55]

Access? Now you are talking my language more...here is a simple db with the two tables and a query matching them.