Is it possible to use master lists in Excell ?

[Guest]

I want to be able to use masterlists and then copy from them to other
spreadsheets. Currently I have just linked them and that obviously doesn't
work. What would be the best way of creating a continuos link between a
masterlist and another spreadsheet - the problem is that the datarange may
change, not by much but still by say 5%.
Would vlookup be the best option ?

 

[Guest]

I want to be able to use masterlists and then copy from them to other
spreadsheets. Currently I have just linked them and that obviously doesn't
work. What would be the best way of creating a continuos link between a
masterlist and another spreadsheet - the problem is that the datarange may
change, not by much but still by say 5%.
Would vlookup be the best option ?

Try Debra's nice coverage on VLOOKUP or INDEX/MATCH at her:
http://www.contextures.com/xlFunctions02.html
VLOOKUP

http://www.contextures.com/xlFunctions03.html
INDEX/MATCH

There's also some sample workbooks available for d/l & study

INDEX/MATCH would usually be a more versatile option compared with VLOOKUP
which requires that the lookup column be the leftmost col, albeit this may
not be a problem if the key col in the mastersheet is always the first col
(say). We can also directly index the col to be returned using INDEX/MATCH
[eg INDEX(H:H, ...)] whilst with VLOOKUP, we need to figure out ("count") the
relevant col_index_num. But VLOOKUP is simpler to understand, and usually
shorter in construct. It's good to know and try out both options.

---