Locating data from other Excel sheets.

Hi

I have a master tracker sheet on which I keep all my customers orders. These are sorted by order numbers. I have 30 members of staff who work on these orders and input the order number on their daily excel worksheet (there are 5 tabs).

At the end of each day I currently get them to print off these sheets and have to manually enter the name of the member of staff against the order number as well as the date they picked it up.

does anyone know how I can do this automatically? Ideally I would need to search all 30 excel sheets, and all 5 tabs to find the order number, link it to my tracker sheet and pull through the name of the member of staff and date they started working on it.

I have a reasonable knowledge of excel sheets, but this has got me stumped so any help would be gladly received.

thank you

There are two broad strategies for this, but the details will depend on what data you have and how it's organized. (Not clear how the 5 tabs you mentioned are used, for example.)

In either case, the 30 workbooks have to be accessible to you, either over a netwrok or by getting copies of them on your machine.

One strategy is to write VBA macros to scan the 30 workbooks and update your master with the relevant data. This is not especially hard but would be challenging for someone not already familiar with VBA programming.

Another is to use formulas that reference the other workbooks to look up data, although the fact that there are 30 of them could make things a bit complex.

Is it possible for you to post your master file plus one of the 30 as an example? Do not post private data.

Hi, I have uploaded a sample sheet.

The data looks pretty straightforward but I can't see how the data in the individual Sheet A maps to the data in Main. I would need to know for each column in Main where the data is supposed to come from. For example, Main has a surname and first name, and Sheet A just has Name. Main has a Customer Number, but Sheet A does not. Main has a Date Picked Up; Sheet A has four dates but none are Picked Up. What is the naming convention you use for the tabs in Main? Most of them look like "wc d-m-yy" but one is "d.m.yy".

Originally Posted by helpme71

I have never used VBA Macro's but if it there is simple to follow instructions I am sure I can figure it out.

If you have never used VBA before and have no experience in programming then you won't find this simple, but I think I can get you most of the way there if I can understand the technical requirements a bit better.

Hi

I have redone the excel docs to try and show exactly what I need, I have linked the Sheet 1 to the name and date to indicate how I need it to work, I have also tried to link all the data onto one worksheet (within the same workbook as the main doc) and use Lookup, this seems to be ok when I first start putting data in but once its looking up over 200 it seems to be coming up with all kinds of different errors. For example, wrong name is pulled through, or wrong date, or nothing at all.

I could do with having this up and running by the end of the month. So any help would be appreciated before I throw my monitor out of the window in frustration! LOL.

thanks

Here, give this a test run. I tried it on very limited test data.

I created a Template worksheet, which you'll have to leave in place.

You will have to manually rename the new worksheet, which will get the default name of Template (2) and be the rightmost tab.

Create a directory. Put this file in it, and the 30 data files, and nothing else. Open this file, open the Run worksheet, and click the button.

Edit: Attachments removed at the request of helpme71. Contact me if you need a similar solution.

This should solve the problems, but again, you'll need to test.

Because you are matching existing data you have to indicate which worksheet has the data you want to fill out. I've added a dialog box to prompt for that.

If it finds an order number in of the 30 order books that it cannot find in your Main sheet, you get an error message. Because I expect this to be rare I did not try to log the messages, you just get a message box telling you.

Edit: Attachments removed at the request of helpme71. Contact me if you need a similar solution.