Formual link to external workbook

[JBW]

How can I reference an external workbook with look-up tables in it from
another, yet keep the look-up table workbook closed

I have formual for calculation in one workbook, looking up values in second.
Works fine when second open but not when it's shut.

Whole point of excersise is to keep constants listed in look-up tables
hidden as they are commercially sensitive.

 

[~L]

It seems you are using a volatile function (this includes Indirect, offset,
rand, now, today, info, cell and some user-defined functions).

Try replacing these with non-volatile functions.

Excel is not secure and should not be expected to protect any sensitive
data, even when the built-in measures are used properly.

Copy the data, open a new worksheet and paste special values to avoid
revealing formulas which can be used to calculate your hidden lookup values
even if they're in a separate document.

If you're going to do that, there's no need to keep the results page
separate from the lookup page and you can use volatile functions to make your
life easier.

 

[Dave Peterson]

You can write your =vlookup() (are you using =vlookup()'s???) formulas so that
they point to the range in the other workbook--and that "sending" workbook
doesn't have to be open.

But if you use the workbook, sheet and range in the formula, then anyone can see
the formula and just open that other workbook--or use other formulas to retrieve
the values.

You can lock the cells, hide the formulas (for those locked cells) and protect
the sheet, but that won't stop anyone who's really interested in your data.

My suggestion is to not use excel for this kind of thing--or don't share it with
people who can't be trusted.