Search Search for: Archive: Business ApplicationsAll Forums

Solved: Excel Forumula Help

Member with 298 posts.

Join Date: Apr 2006
10-Oct-2008, 12:16 PM #1
Solved: Excel Forumula Help
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.
Member with 7,837 posts.

Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
10-Oct-2008, 12:28 PM #2
Quote:
 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.
Member with 298 posts.

Join Date: Apr 2006
10-Oct-2008, 12:30 PM #3
I do mean A2 = Order00002, sorry.

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

Thanks for the quick response.
Member with 298 posts.

Join Date: Apr 2006
10-Oct-2008, 12:43 PM #4
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
 Sample File - Copy - Copy.xls (23.0 KB, 192 views)
Member with 7,837 posts.

Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
10-Oct-2008, 02:24 PM #5
Here you go - as you can see, I randomly assigned some IDs to the Unassigned sheet, just to show the match works.
Attached Files
 Sample File - with formulas.xls (42.5 KB, 192 views)
Member with 298 posts.

Join Date: Apr 2006
11-Oct-2008, 08:38 AM #6
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.
Member with 7,837 posts.

Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
11-Oct-2008, 04:33 PM #7
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.
__________________
Iowa? I could have sworn this was heaven.
Well, I think I can answer this question most successfully in mime.
My theme song... | Affero - rate me!
Member with 298 posts.

Join Date: Apr 2006
13-Oct-2008, 06:08 AM #8

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'
Member with 7,837 posts.

Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
13-Oct-2008, 09:19 AM #9
To be honest, I am not sure. I'll look and see if a better Excel person is on here.
Member with 5,164 posts.

Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
13-Oct-2008, 02:30 PM #10
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.
__________________
Regards, Zack (If you would like comments in any code, please say so.) Wanna rate me?
Excel & Access blog :|: OfficeArticles.com :|: Extreme Excel Tutorial
What is a Microsoft MVP?
Member with 7,837 posts.

Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
13-Oct-2008, 03:27 PM #11
Kind of simplistic, but the only way I could figure out....
Attached Files
 Sample File - with formulas.xls (71.0 KB, 195 views)
Member with 5,164 posts.

Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
13-Oct-2008, 09:39 PM #12
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!
Member with 7,837 posts.

Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
14-Oct-2008, 05:44 AM #13
uh, I said it was simplistic...we can't all be Excel lords....
Member with 298 posts.

Join Date: Apr 2006
15-Oct-2008, 12:22 PM #14
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.
Member with 7,837 posts.

Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
15-Oct-2008, 03:38 PM #15
Access? Now you are talking my language more...here is a simple db with the two tables and a query matching them.
Attached Files
 sample Access.zip (16.9 KB, 11 views)
 techguy.org/757872
As Seen On

WELCOME TO TECH SUPPORT GUY!

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

Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your computer problem!

 Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)