vlookup on multiple workbooks into 1 main sheet

Hi Everyone,

Sorry for the title I wasn't sure how to word it but here goes I have uploaded only one sheet which I have users in my team completing and they all have individual folders

For example

Adam has a folder and in that is the spreadsheet
Cameron has a folder and in that is the same spreadsheet but different data
and this goes on for 4 teams and about 40 agents


Now here is the question and I'm sorry for babbling but is there an easy way or macro or anything I can make I want a main sheet to be able to go out and collect all the data from all workbooks from the status tab for the 1st and 2nd etc..

Can someone please suggest how I can do this or what I can please do please and I'm kinda needing this for my teams in a few days and I have tried everything I know..

Sorry to bug everyone

Thanks

Ads

I am trying something similar. I want to look up 6 cells from each file in my directory. I would happily do it just using excel but this falls apart when you have many hundreds of cells - one cannot type all those formulae! What I hoped I could do would be to create a directory list (which is easily done) which would tell me the full path to each workbook. I would then concatenate this with the sheet and cell references I want. But that does not work because whilst I can get the right formula to show, that is the result, not the formula in the cell. I have to copy and paste it as a value in order to get it to actually reference the correct thing. Even then, excel often complains about the link and asks me to update it.

Anyhow, a bit of playing and it seems not too difficult to use VBA to do the whole thing (concatenate the string and paste as "=string"). This is not without a bit of trouble because the directory list returns the file path but the link one pastes needs square brackets! I am still hoping someone might be able to tell me an easy way - I remain shocked that it is as easy as pie to find the name and details of every single file but it becomes awfully difficult to get it to tell you the value of sheet1A1 in those same files!

ps. If you did get it working, how does it go in terms of reliability? I am a bit concerned having a large number of links in a single sheet.... i do find excel can be a tad flakey at the best of times!

Thanks for your help!

1) The path is d:\Archem\Adam mixes

2) All files (if it is easy, filtering would be good such that it would only look at files which have a "-" in the title, just in case something else gets put in there).

3) Sheet "cost summary", cell A5 which would be titled 'cost' (there are others but I would not wish to waste your time, once I see how you are doing it, I can replicate for the other cells I would want to retrieve).

4) Whether to renew or append I am unsure. I am assuming that it is probably easier to just renew rather than append.... either way would work for me. Whichever is easier I guess.

I would anticipate that the report would have column A giving the title of the workbook and then B as giving the cost. Again, once I see how it is done, I would then tweak it to retrieve other cells and place them in the right spot with an offset to column B.

Many thanks

Thanks, that seems like exactly what I am after. I have had a quick play without much luck - Nothing actually gets imported so I am assuming that something is stopping the macro from managing to find the files it should. I will play further later on.

I have had a chance to have a good mess around now and it is all working brilliantly. I have ti conditionally formatting and such to make the result lool nice but there is one thing I cannot seem to manage. How do I insert a hyperlink to the files in the list? I can insert the full path, I can even convert it within excel into a hyperlink, but no luck directly from the vba.

So I would like the hyperlink to wbdata.path (I guess) with the displayed text being wbdata.name - am I just being silly failing to do this?

Thanks!

Oh great thanks... i was on the right lines and just needed a little tweak!

I apologise to ask another one... I do have a way to do it but it has me fiddling to MSO... subfolders? Is there an easy way to extend the search to them without rewriting for MSO?

Ah nuts.... thanks, I will have to try and redo it the other way. Thanks and have a good weekend.

Unless you know what the subfolders will be called.... save me the hassle of a rewrite!

ps. I wasn't the original author so cannot change the thread status I am afraid.

Hey guys,

I have been using this for some time and it works well. However, I want to access a network source and cannot get it to work. I replace my "D:\" path with "\\192.168.1.2\". I have checked this and it works - if i put the same address into my explorer bar, it goes to the correct place. However... when I run the macro, it fails to import anything. I know the macro does run because I can make it add other headers. It is just although it fails to find any files... am i missing something obvious?

Thanks