Lookups ref to other workbooks in locked cells

[mthatt]

Hi

Ived scanned this site and still hav'nt found a solution to my problem. It
seems like it is impossible.

Here it goes:

I have several user workbooks (calculations) with lookup formulas, that
lookup prices in a specific version of the pricelist. The cells that include
these lookups are locked and sheet protected.

Now my problem is that I would like to, by one simple input, to define which
pricelist the lookups should be made in without unlocking, find and rename.

The formula i need is something like this:

=VLOOKUP(A$3,"'c:\myfolder\pricelists\[ver"&$A$1"&".xls]Sheet1'!$A$1:$B$999,2,FALSE)

where the version of the pricelist is inputed in A1

Clearly this does'nt work, but i think it describes what I need.

The indirect function needs the priceliste too be open and I'vd tried the
pull function by Harlan Grove found otherwhere here, and it works fine in
other situations, but in this case it slows down unacceptable.

Does anyone have a workaround to this problem?

Michael

 

[Dave Peterson]

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may be faster. (I've never used it.)

Hi

Ived scanned this site and still hav'nt found a solution to my problem. It
seems like it is impossible.

Here it goes:

I have several user workbooks (calculations) with lookup formulas, that
lookup prices in a specific version of the pricelist. The cells that include
these lookups are locked and sheet protected.

Now my problem is that I would like to, by one simple input, to define which
pricelist the lookups should be made in without unlocking, find and rename.

The formula i need is something like this:

=VLOOKUP(A$3,"'c:\myfolder\pricelists\[ver"&$A$1"&".xls]Sheet1'!$A$1:$B$999,2,FALSE)

where the version of the pricelist is inputed in A1

Clearly this does'nt work, but i think it describes what I need.

The indirect function needs the priceliste too be open and I'vd tried the
pull function by Harlan Grove found otherwhere here, and it works fine in
other situations, but in this case it slows down unacceptable.

Does anyone have a workaround to this problem?

Michael

 

[mthatt]

Thanks Dave

I will have a look at it later tonight, but when the function is included in
an add-in, you will have to insure that users have that add-in installed.

I see a little problem for me in my organisation, because we have several
external or/and temporarily users, and we have no control of their user
software.

Can you attach an add-in to at specific workbook, so that it is only in
function in this workbook?

Michael
"Dave Peterson" skrev:

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may be faster. (I've never used it.)

Hi

Ived scanned this site and still hav'nt found a solution to my problem. It
seems like it is impossible.

Here it goes:

I have several user workbooks (calculations) with lookup formulas, that
lookup prices in a specific version of the pricelist. The cells that include
these lookups are locked and sheet protected.

Now my problem is that I would like to, by one simple input, to define which
pricelist the lookups should be made in without unlocking, find and rename.

The formula i need is something like this:

=VLOOKUP(A$3,"'c:\myfolder\pricelists\[ver"&$A$1"&".xls]Sheet1'!$A$1:$B$999,2,FALSE)

where the version of the pricelist is inputed in A1

Clearly this does'nt work, but i think it describes what I need.

The indirect function needs the priceliste too be open and I'vd tried the
pull function by Harlan Grove found otherwhere here, and it works fine in
other situations, but in this case it slows down unacceptable.

Does anyone have a workaround to this problem?

Michael

 

[Dave Peterson]

Authors of some addins have included a feature that would allow you to "merge"
the function with the workbook--and avoid having to install the addin.

You'll want to check to see if Laurent's addin supports this--I don't know one
way or the other.

Thanks Dave

I will have a look at it later tonight, but when the function is included in
an add-in, you will have to insure that users have that add-in installed.

I see a little problem for me in my organisation, because we have several
external or/and temporarily users, and we have no control of their user
software.

Can you attach an add-in to at specific workbook, so that it is only in
function in this workbook?

Michael
"Dave Peterson" skrev:

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may be faster. (I've never used it.)

Hi

Ived scanned this site and still hav'nt found a solution to my problem. It
seems like it is impossible.

Here it goes:

I have several user workbooks (calculations) with lookup formulas, that
lookup prices in a specific version of the pricelist. The cells that include
these lookups are locked and sheet protected.

Now my problem is that I would like to, by one simple input, to define which
pricelist the lookups should be made in without unlocking, find and rename.

The formula i need is something like this:

=VLOOKUP(A$3,"'c:\myfolder\pricelists\[ver"&$A$1"&".xls]Sheet1'!$A$1:$B$999,2,FALSE)

where the version of the pricelist is inputed in A1

Clearly this does'nt work, but i think it describes what I need.

The indirect function needs the priceliste too be open and I'vd tried the
pull function by Harlan Grove found otherwhere here, and it works fine in
other situations, but in this case it slows down unacceptable.

Does anyone have a workaround to this problem?

Michael