PDA

View Full Version : Vlookup issue - How to change the source file without affecting vlook



musicgold
06-17-2013, 06:13 AM
Hi,

I have an excel file – central database - that holds a lot of raw data organized in tables. There are more than 15 files (dependent files) that access this data using VLOOKUP formulas.

I now need to add a few columns to the central database and these columns can’t be after the columns already existing, i.e. they have to be on the left of some of the existing columns. As you may appreciate, inserting those columns is going to mess up all the vlookup formulas in the dependent files.

Is there a way change the central database file without affecting the vlookup formulas in the dependent files?

Thanks.

Cross posted here: http://www.mrexcel.com/forum/excel-questions/706982-vlookup-issue-how-change-source-file-table_array-without-affecting-vlookup-formulas.html#post3498083

SamT
06-17-2013, 07:59 AM
The simplest way I know is to open all the files at the same time, Central and dependent, then add the new columns. Excel will adjust the Lookup columns from you. (I see that you've tried that)

If the new columns fall inside a Lookup range the Lookup result column will be wrong. If that is the case, the answer to your question, "Is there a way..." is no.

You have run into the classic problem of "Feature Creep" endemic to Organic programs. An Organic program is one that is developed to handle the situation today without consideration of Feature Creep tomorrow. They typically grow by adding more constraints, (AKA features,) until they are totally unmanageable.

There are at least three possible ways to go from here.:
Put the Central book's data into a Database Server, with all that entails.
Name the column ranges in the Central workbook and rewrite the formulas in the dependent books to use Excel's DB functions.
Turn the Central Book into a Data Server. This means that each dependent book needs a Reference to the Data Server Project. All the Lookup formulas need to be converted to VBA Get Procedures in the Data Server, and all the Lookup Formulas in the dependent books need to be rewritten to use the Data Server's Properties.#3
Assume that in a dependent book you have some column with a list of employee names and some column that wants those employee salaries, and that you have named the Central book's Project "DataServer. "The Salary formula might look like this.

DataServer.EmpSalary($C2) 'Where col "C" is the Employee name column When you're writing the formula and get the first "(" typed, excel will prompt your for EmployeeName, and you just click in the appropriate Cell.

The corresponding Property Get Procedure in the Data Server might look like.

Public Property Get EmpSalary(EmployeeName As String) As Currency
Dim Rw As Long
Rw = Range(cEmpName).Find(EmployeeName).Row
EmpSalary = Range(cEmpSalary).Cells(Rw-1).Value 'Where the data starts in Row 2

End Property Of course, that can be shortened a single line.

EmpSalary = Range(cEmpSalary).Cells(Range(cEmpName).Find(EmployeeName).Row-1).Value