vlookup? other function?

[k_milner1]

I have two workbooks with data. I have always manually linked the two workbooks, however, I sometimes add columns and rows to the other workbook. Because it is a completely separate file, the link will no longer deliver me the right numbers.

I am looking for a function, probably a vlookup, that allows me to link the cells a different way. Is there a function that will search the first column of another worksheet (keep in mind it is searching for basic English words such as "Direct Costs"). Once it finds the word(s) that it is looking for I want it to return to me the data in the second column in that row. For whatever reason right now vlookup is not working for me.

Any Ideas?

Thanks,

 

[Caleeco]

You can instead try converting the dataset into an Excel Table. You can they refer to specific columns by header name in you vlookup or INDEX/MATCH eg Table1[Direct Costs]

That should alleviate the problem that is created when adding/deleting columns

Regards
Caleeco

 

[k_milner1]

The data set is so large and we are so far into the year that it cannot be changed to table format. Any other ideas?

 

[FDibbins]

The data set is so large and we are so far into the year that it cannot be changed to table format. Any other ideas?

There nothing really to change, just highlight the range, select Insert Tab/Table/OK and it's done

 

[FDibbins]

I have two workbooks with data. I have always manually linked the two workbooks, however, I sometimes add columns and rows to the other workbook. Because it is a completely separate file, the link will no longer deliver me the right numbers.

Make sure that both files are open before you do that - then make sure you save both files again, so that the changes are registered in both files

 

[Caleeco]

The data set is so large and we are so far into the year that it cannot be changed to table format. Any other ideas?

I dont see why that would be a problem? If all the data is on one page.. and continuous:
1. Click anywhere in the data set
2. Click "Insert" ribbon tab
3. Click Table
4. Click Ok

this will change the data to an Excel Table

If this is not what you want. I can code a VBA solution, but will need more information about your sheet names, workbook names, workbook locations, are they both open when the macro is run? etc etc

Regards
Caleeco