Extracting data - vlookups with closed Sheets

Hi,

See below ... since topic is quite frequent ...

http://www.excelforum.com/2257101-post4.html

HTH

Thank you for that but I cannot find the link from your post.

Libby

Hi,

Below is the latest link ...
http://xcell05.free.fr/english/index.html

HTH

Hi Libby

You may want to try the attached files, but firstly you will need to change the path of the MAT and CAP files within the code.

Also for this to work you need to make sure that the headings in the two files are identical. I have made a note in the IF file regarding the column headers I had to change in the MAT and CAP files.

Also the previous macro was based on there being information in the first column of IF Sheet1, but I have now changed this to be dependent on the CAP/Client No as I was unable to populate the Project Manager name.

Please let me know how you get on.

Morning,

Thanks you very much.

It is stopping at the bit in code that says

Please Login or Register  to view this content.

Also how do I add other columns to copy.

I will need quite a few more but I presume I can simple add these.

You are an absolute star by the way.

Thanks
Libby

Sorry my fault, wrong path name.

Its not retrieving anything now. I think its me. I have changed the path and it is not debugging but it is not retrieving.

Have I done something.

Please Login or Register  to view this content.

Libby

Morning Libby,

You are welcome.

It is probably stopping at that code because you need to change the following code

Please Login or Register  to view this content.

or copy the sample files to the C:\temp directory.

Currently the code expects the CAP file to be in the C:\temp directory with filename CAP Sample.xls and the MAP file to be in the same directory with the sample filename.

I don't know if you intend for the user to go and locate the CAP and MAT files themselves. If that is the case then you would probably want the code above to be

Please Login or Register  to view this content.

For the additional columns as long as the two files have the same column headings, you should be able to add as many as you need. At the moment, I think the only limitation is that the code relies on the IF file having more columns (with data) than the other two files. If it is likely that this would not always be the case, please let me know.

Regards

Thats better if we can locate the file but its not retrieving anything.

It did once then not again. I'm sure it is me.

Libby

Let me see what I am doing as I think it because I have selected a file with wrong column headers.

I'm so chuffed. Would have taken me ages as only new to VBA. Trying to learn.

Thanks
Libby

Sorry, I presume this will return things no matter how many rows there are?

Its me! I know it. It is asking me which file etc then it is opening but not retrieving. I checked the headings were the same but still not retrieving. Could it be because I am on Excel 2003?
Libby

Is it based on some data already being in the CAP column?

That's odd, as it works with the sample files I have here. Also I'm working with Excel 2003, so that shouldn't be the problem.

Are you still using the sample files or are you using your actual files at the moment, because if that is th case, you may need to amend the worksheets it refers to for the IF files.

Thats what it was. I deleted the CAP. Worked it out eventually.

You are an absolute star.

Thanks you so much.

Libby

Yes it is based on there being data in the CAP column.

Hi there,

Me again. Is there anyway it can be speeded up. I have 40, 000 rows and its crashing. i think to be fair its going to be that way because of the level of data but just thought I would check.

Libby

Hi Libby

I have made an amendment to the code, as I've done something similiar in other post.

Hopefully the change should result in a better run time.

Please try it and let me know how you get on.

Regards

You star. Thank you very much.

I am trying to learn but I don't seem to be doing very well.

All your help is very much appreciated.

Thanks agian

Hi there,

Sorry for the delay. I have had a lot of problems. I have pasted the code into the word doc. I added 2 columns in with SLFF start period and SLFF end period in next to the project end date too.

Hopefully you may be able to see what I have done.

Thank you.

Libby

Hi Libby

One thing I did find was that you are using an older version of the code, so the data was being determined first then the columns were populated, as a result you had gone back to the situation we had earlier where you had to run the code three times before it had all the results you wanted.

I have change the code and attached it to a separate file on its own with a command bar button. This means you now have to locate the IF file, like you do for the MATs and CAPs file.

Please let me know if you have any problems.

Regards

Hi Libby

Please find attached the revised file with the errant link to the 'xla' file removed.

Regards

Here's the screena shot for the error
Thanks again
Libby

Hi Libby

I don't think I will be messing with add-ins for a while.

I have now attached a newly created file, which reassigns the macro to the button on open. The new button is now just called Update.

This should work now.

Regards

Hi again.

I'm so sorry I seem to keep messing up your code.

I've simply added this to the blank sheet that I would use really it is only to show you the additions to the code.

If I can get these additions in and it works that will be fab. It seems to be corrupting and not doing anything now.

It did work though.
Sorry

Hi Libby

I think I have resolved all the anomalies. I have attached the file (IF master file) and the code file, these two work with the last CAP and MAT files.

Please let me know how you get on.

Hi there,

Its still not bringing across project renewal and the compliancy now.

I had a version that worked perfectly and my computer corrupted it so maybe its me.

Thank you so much

Hi Libby

The code was working on the number of columns populated in the IF file which previously had more columns that either the MAT or CAP file, but this is no longer the case. So I've made an adjustment to pick up 20 more columns than the number of columns in the IF file.

And the reason why the 'AW' column has no values is that is being determined based on values in column 'O' (Code closed for time) which has no values in it. I have amended this to look at column 'P' (commission) as I think that is most probable column which should be evaluated.

I have amended the code for the columns which are not being picked up, but you would probably need to close down excel, so that you are sure you are using the current version.

Please let me know if it works.

Kind Regards

Thank you. I have got it to work. I think I had so many versions of this that you are right I think I had the wrong versions open.

Thanks so much for all your help.