Locating data from other Excel sheets.

[helpme71]

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

[6StringJazzer]

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.

[helpme71]

Thanks for that. I do have access to all 30 worksheets, basically the 5 tabs equate to monday to friday, so all the sheets are the same.
Difficult to upload due to the nature of the documents, but for exmaple I get order number 124654 for x customer, I input this on my master tracker sheet. Staff member 15 picks this order up out of the intray and inputs it on his worksheet that he or she is working on it. I need to be able to go into my tracker sheet (which I cant give all my team access to) and find out who's picked up this order and the date they picked it up.
Does this help you any more?
I have never used VBA Macro's but if it there is simple to follow instructions I am sure I can figure it out.

[helpme71]

Hi, I have uploaded a sample sheet.

[6StringJazzer]

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".

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.

[helpme71]

Hi

The name, surname on main is entered manually. The only data I need pulling from Sheet A and subsequent ones is the customer number which is on Column C on Sheet A and the date they picked it up. Everything else is generated by direct input. The main sheet is a weekly sheet hence the tab date. However the date picked up will be a daily date, linked hopefully to the date on Sheet A at K3 (this is the same location on all 5 tabs). I hope this clears up your query. One more this, all 30 sheets are password protected. I do have all the passwords, as I enter this to pull other date from the sheets for productivity. Will this be a problem??
thanks for your help so far.

[6StringJazzer]

...the date picked up will be a daily date, linked hopefully to the date on Sheet A at K3 (this is the same location on all 5 tabs).

In the weekly workbooks, if there is one date for "date picked up" for the whole sheet in K3, why is there also a column for "date picked up" in column D repeated for each row?

One more this, all 30 sheets are password protected. I do have all the passwords, as I enter this to pull other date from the sheets for productivity. Will this be a problem??

What is the level of protection? If you need a password even to open the file then it will have to be included in the programming. If the password is required only to make updates, then it doesn't matter. Is it the same password for all 30, or 30 different passwords? Do the passwords ever change?

[helpme71]

In the weekly workbooks, if there is one date for "date picked up" for the whole sheet in K3, why is there also a column for "date picked up" in column D repeated for each row?

That row is no longer going to be used, and as such will be deleted, unless it is easier to use that row for the dates??

What is the level of protection? If you need a password even to open the file then it will have to be included in the programming. If the password is required only to make updates, then it doesn't matter. Is it the same password for all 30, or 30 different passwords? Do the passwords ever change?

I have all the passwords, and their all different. Only needed for updates, and they wont be changing.

Hope this helps, thank you

[6StringJazzer]

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.

[helpme71]

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

[helpme71]

Hi

thanks for this, it is almost perfect. Only thing is, the customer number will already be on the sheet ( I will be putting these details in manually as the orders come in) so I need it to match up and pull through just the date it was picked up and the person who did it (all this pulls through fine on the test sheet). Is there a way this can be done?

One thing, I run it once, saved it, went back to run it again and it came up with errors. I resaved it into the folder and it ran again no problem and same error came up when I went back into it.

Thank you for your work it is very much appreciated.

[6StringJazzer]

OK, I see. I'll revisit. It's certainly possible. The reason it works on the sheet you have is that you are explicitly linking to a file, and you already know which file to link to. The code will have to search all 30 files to figure it out so a little more work needs to be done.

As for the errors, hard to diagnose without the same files you have but I didn't try to run it twice. I'll see if I can reproduce. Can you describe exactly what errors you got?

[helpme71]

Thank you.

Yea, I got a runtime error '1004' saying that file could not be found and check spelling of file name etc.

[6StringJazzer]

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.

[helpme71]

This is great, thank you so very much!