My boss wants me to separate a workbook into two separate workbooks an
link them. The prices are to be put in one workbook and the work area
and code are to go into another. Users will have a copy of code
workbook and will link to the price file over a network. I can liv
with the message about updating links, but what I would like to do i
put a variable in the cells that reference the pricing workbook so tha
I don't have to change the name of the linked workbook every month w
get new prices. Keeping the same name and location hardcoded in th
cells is not practical for testing and the monthly updates.

My question. Is there a way to substitute a variable inside the cel
for -'C:\Documents and Settings\My Documents\MyExcel\[Pricin
Sheets.xls]Sample'-

=(VLOOKUP(B8,'C:\Documents and Settings\My Documents\MyExcel\[Pricin
Sheets.xls]Sample'!$A$2:$F$4,2,TRUE)*B8)

As a bonus, how can I get rid of the message that comes up asking t
link to the unopen workbook

--
Bruce00
-----------------------------------------------------------------------
Bruce001's Profile: http://www.excelforum.com/member.php...fo&userid=2663
View this thread: http://www.excelforum.com/showthread.php?threadid=49082

First, the easy answer.

The choice to see that message is the user's--not the developer's.

But you could tell each user to:
tools|options|edit tab
uncheck the "ask to update automatic links"

The links will be updated without a prompt.

This is a setting that affects all workbooks opened by that user, so they may
not want this to happen.

One alternative is to password protect the workbook you share with the
others--and don't share that password.

Then give the user a helper workbook that opens the real workbook with links
updated the way you want.

(You'll want to protect that helper workbook's project so that the curious don't
find the password and skip your helper workbook.)

The code in the helper workbook would look something like:

Option Explicit
Sub auto_open()
Workbooks.Open Filename:="\\path\path\path\book2.xls", _
UpdateLinks:=1, Password:="hithere"
ThisWorkbook.Close savechanges:=False
End Sub

or

Option Explicit
Sub auto_open()
Workbooks.Open Filename:=thisworkbook.path & "\book2.xls", _
UpdateLinks:=1, Password:="hithere"
ThisWorkbook.Close savechanges:=False
End Sub

Put a giant note on the only worksheet in this helper workbook. Tell them that
if they can read this message, then they should close this workbook and reopen
it with macros enabled.

=========
Second, I'd keep the name of the price file the same for the user.

But for your updates/testing, you can point to any test price workbook you want.

Just open the real workbook and do:
Edit|links|Change source

Then when you're happy with the testing, change the links back to the real price
file. And don't forget to update the real price file at the same time (or close
to the same time).

I think the more you do as a developer (and the less you ask of the user) will
make your life simpler in the long run.

=======
The ideal solution would be to use =indirect() to point at a cell that contains
the workbook name/worksheet name/range address and use that in your formula.

The bad news is that =indirect() won't work with closed workbooks.

========
And just a curiosity question, you're using TRUE in your =vlookup() formula.
This means that you may not find an exact match. So if your matching on part
numbers (or something that requires an exact match), you'll want to use FALSE as
that last parm in =vlookup().



Bruce001 wrote:

My boss wants me to separate a workbook into two separate workbooks and
link them. The prices are to be put in one workbook and the work areas
and code are to go into another. Users will have a copy of coded
workbook and will link to the price file over a network. I can live
with the message about updating links, but what I would like to do is
put a variable in the cells that reference the pricing workbook so that
I don't have to change the name of the linked workbook every month we
get new prices. Keeping the same name and location hardcoded in the
cells is not practical for testing and the monthly updates.

My question. Is there a way to substitute a variable inside the cell
for -'C:\Documents and Settings\My Documents\MyExcel\[Pricing
Sheets.xls]Sample'-

=(VLOOKUP(B8,'C:\Documents and Settings\My Documents\MyExcel\[Pricing
Sheets.xls]Sample'!$A$2:$F$4,2,TRUE)*B8)

As a bonus, how can I get rid of the message that comes up asking to
link to the unopen workbook?

--
Bruce001
------------------------------------------------------------------------
Bruce001's Profile: http://www.excelforum.com/member.php...o&userid=26630
View this thread: http://www.excelforum.com/showthread...hreadid=490829

--

Dave Peterson

Thanks Dave. I was already thinking about a helper file to open the tw
workbooks. But I'm working with an end of year deadline and have
bunch of other stuff to do. I think I'll just go with the Edit Link
like you suggested and pray I don't forget to turn it around when
distribute the executable. I'll just have to make it a rule that th
pricing file never changes it's name.

On the vlookup, it is for a pricing table and the user can enter an
whole number and it looks for a value in between and returns th
numbers I require.

It seems that the more I learn about Excel just shows me how little
really know

--
Bruce00
-----------------------------------------------------------------------
Bruce001's Profile: http://www.excelforum.com/member.php...fo&userid=2663
View this thread: http://www.excelforum.com/showthread.php?threadid=49082

I agree with this statement!

Good luck,

Bruce001 wrote:

It seems that the more I learn about Excel just shows me how little I
really know.

--
Bruce001
------------------------------------------------------------------------
Bruce001's Profile: http://www.excelforum.com/member.php...o&userid=26630
View this thread: http://www.excelforum.com/showthread...hreadid=490829

--

Dave Peterson