Automating User Defined VLOOKUPS

[HangMan]

I have a master Workbook which is an amalgamation of four other area Workbooks. The four area Workbooks are updated on daily on an ad-hoc basis by different people across the country. The (actual) Workbooks have over 50 columns of data and around 3,000 rows.

Currently to update the Master Workbook I'm having to do labourious VLOOKUPS on around 20 different columns (sometimes non-adjacent columns) for each of the four area Workbooks to update my master sheet, but only on certain columns because not every column needs updating.

I'm trying to find a way to automate this process using a macro or similar. Basically what I need to be able to do on my master Workbook is:

1. Specify via a user form which column/s needs to be updated on the master sheet, by using the column letter, e.g. C, G, H, K or whichever column needs to be updated
2. Do a VLOOKUP or similar (doesn't have to be a VLOOKUP, INDEX and MATCH is fine) based only on the entries in columns A and G, i.e. column A is a unique number, column G is one of the four areas

Note: The Master Workbook contains more entries than the four area Workbooks (as in not all the entries on the Master Workbook appear on the four area Workbooks)
Note: Some of the columns which require updating contain Data Validation
Note: These are all separate Workbooks, I've just put them in as separate sheets on the attached sample for ease

Where there are adjacent columns to be updated, it would be great if when specifying the columns to be updated a range could be entered in the userform, e.g. J:M, so that each column doesn't have to be updated one by one. What would be even better would be if 'ALL' the columns to be updated could be specified in one go in the userform, e.g. B:C, E:F, Z, AB, AS:BZ and so on

I've attached a greatly simplified example... Is this even remotely possible?

Many thanks

[sourabhg98]

What do you need??
You just need to update the master sheet?
For example- In the "North" Sheet you have UID 23 in the second row, having Q1 value as 57 however in your "Master" Sheet the Q1 value for UID 23 is 567, so you need to change 567 to 57??
Right?

[HangMan]

Yes, just update the master workbook exactly as you state. The real sheets are much much bigger (as in many more columns and rows) and I need to be able to specify through the use of a userform which specific columns need to be updated using some form of lookup from each of the four area sheets, so that the master sheet is updated to reflect each of the four area sheets.

Not every column needs updating, which is why I need to be able to specify either individual columns, e.g. it may be that the North have only updated column K, so I need to be able to specify just column K on my master Workbook to be updated with the values on the North Sheet.

Or it may be that on the South Sheet, that only columns H, J, L:M have been updated, so again on my master sheet I need to be able to specify that those same columns are updated. Being able to specify which columns are to be updated on the master sheet is important.

The other important point is that because the master sheet includes all four areas, the updates from each are workbook must only affect the relevant rows on the master workbook.

There are five workbooks, one master and four area workbooks.

I hope that makes sense and helps a little...

Many thanks

[sourabhg98]

Okay done...
So as and when you would change any value in Column J to M in any of the 4 sheets the value would be updated in master sheet automatically....
Give it a try...
As you want to specify individual columns you can do that easily....by copying just one line of code..
But first try it..

[sourabhg98]

oops wrong file attached...

[HangMan]

That works as a starting point, thank you, though it ultimately needs to be more sophisticated. Firstly this needs to work between different WorkBooks, rather than WorkSheets.

It needs to use a UserForm to allow specification of which columns need to be updated because it will never be the same columns consistently, it needs to allow the user to specifiy which column/s on the master sheet are updated based on each of the four area workbooks, e.g. today it may be column AA on the North Workbook, column BA on the South Workbook, columns L:Z on the East Workbook and columns P, R, U, Z on the West Workbook and so on and then tomorrow it may be column G on the North Workbook, columns Z:AT on the South Workbook, columns P, Q, S, V on the East Workbook and column BC on the West Workbook, so it will never be consistent, hence the need to be able to specify.

It needs to go something like this with the Master Workbook

Step 1 - Run the Macro so a UserForm pops up
Step 2 - Enter the Column Letter/s to be updated, e.g. C or F or L:O or which ever columns need updating
Step 3 - Specify the data range to be updated based on column G, i.e. North, South, East or West, so that only the relevant rows on the master are updated
Step 4 - Because there are far more rows on the master than on the area Workbooks, where a lookup value is not found because it doesn't appear on the area workbook, an option to specify what value is placed on the master sheet if an error is found (IFERROR), e.g. '0' or "" or "tbc" or 'N/A'

The number of rows in the four area workbooks will increase as time goes by, so they will never be consistent either.

As it could be any one of 50 columns that needs updating, so to have to go in and edit the code each time means it would be no quicker than using an actual VLOOKUP which is the process I'm trying to automate.

I hope this helps to try and explain a little better...

Many thanks

[sourabhg98]

This should work--

[HangMan]

Wow, that is looking really good...

Because of the nature of the content in different columns, is it possible to add a fourth option to allow the user to specify what should appear for IFERROR, e.g. if the columns is made up from numbers, the IFERROR value maybe '0' r some other user defined value, if the column consists of text, the IFERROR value may be 'tbc' or may some other value or for other columns the IFERROR value may need to be "" (nothing).

Also, will this work across different WorkBooks rather than Worksheets?

Many thanks

[sourabhg98]

I don't know how will it work with different workbooks...due to my limited vba knowledge..
But for the error value part just change it to-

Please Login or Register  to view this content.

Now for the different workbooks part you need to take someone else's help..
So maybe just post your workbook with this macro with a new thread...so that someone else may help..

[HangMan]

Okay, will do... one tiny detail, is there any way to make it so that the UserForm entries are 'not' case sensitive, I'm just thinking about the end user not realising?

Many thanks for our help, this is looking really good...

[sourabhg98]

Okay use this then-

Please Login or Register  to view this content.

If that helped, you can click on Add Reputation to say thanks and mark this thread as solved from the tools above...