Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Archive: Business Applications Archive: Business Applications
Search Search
Search for:
Tech Support Guy > > > >

Solved: Excel Forumula Help


(!)

maracles's Avatar
maracles maracles is offline
Computer Specs
Member with 298 posts.
THREAD STARTER
 
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.
slurpee55's Avatar
Computer Specs
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 View Post
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's Avatar
maracles maracles is offline
Computer Specs
Member with 298 posts.
THREAD STARTER
 
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.
maracles's Avatar
maracles maracles is offline
Computer Specs
Member with 298 posts.
THREAD STARTER
 
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
File Type: xls Sample File - Copy - Copy.xls (23.0 KB, 209 views)
slurpee55's Avatar
Computer Specs
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
File Type: xls Sample File - with formulas.xls (42.5 KB, 213 views)
maracles's Avatar
maracles maracles is offline
Computer Specs
Member with 298 posts.
THREAD STARTER
 
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.
slurpee55's Avatar
Computer Specs
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.
maracles's Avatar
maracles maracles is offline
Computer Specs
Member with 298 posts.
THREAD STARTER
 
Join Date: Apr 2006
13-Oct-2008, 06:08 AM #8
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's Avatar
Computer Specs
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.
Zack Barresse's Avatar
Computer Specs
Member with 5,434 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.
slurpee55's Avatar
Computer Specs
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
File Type: xls Sample File - with formulas.xls (71.0 KB, 209 views)
Zack Barresse's Avatar
Computer Specs
Member with 5,434 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!
slurpee55's Avatar
Computer Specs
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....
maracles's Avatar
maracles maracles is offline
Computer Specs
Member with 298 posts.
THREAD STARTER
 
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.
slurpee55's Avatar
Computer Specs
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
File Type: zip sample Access.zip (16.9 KB, 18 views)
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
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)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑