Help with lookup reference to other workbooks

[Guest]

I have made a quote sheet where I have a cell that represents a price of raw
materials. I would like to have that cell lookup a value to a workbook that
may be on another computer in the network. How can I do this? And how can I
have the prices be updated on the individual quote sheets.

 

[Frank Kabel]

Hi
just replace the cell/range reference. Easiest way: Open both workbooks
and select the source range with your mouse

 

[Guest]

Thanks,

Next I created macro to update the link to change the prices to the saved
prices in the other workbook. But now I would like to have a cell return a
date of when the price links in the quote workbook gets updated. How would I
go about this?

 

[Frank Kabel]

Hi
could you provide more details how your other sheet is structured. e.g.
post some example rows and describe which value to return

 

[Guest]

Frank, thanks for the help.

here is my scenario. The material we build is primarily custom for each
customer, each customer has one quote sheet per item. The raw materials for
the item have fluctuations that need to be checked against what has been
quoted. So, 1 document can have all the comodity prices and each quote can
be manually updated to check prices.

The index lookup feature looks like this:

=INDEX('\\Cp300\my documents\[lumber$now.xls]Lumber'!$A$1:$B$8,
MATCH(K14,'\\Cp300\my documents\[lumber$now.xls]Lumber'!$A$1:$A$8,),
MATCH('\\Cp300\my documents\[lumber$now.xls]Lumber'!D2,'\\Cp300\my
documents\[lumber$now.xls]Lumber'!$A$1:$B$1,))

where Cp300 is another computer and lumber$now is the source document with
the prices. There are multiple instances on the sheet that access the same
price list for different components.

I want the quote sheet upon opening to have the "old" prices showing, but be
able to click the button I created to run the following macro:

Sub Update()
'
' Update Macro
' Macro recorded 12/3/2004 by Chris Whiting
'

'
ActiveWorkbook.UpdateLink Name:="\\Cp300\my documents\lumber$now.xls",
Type _
:=xlExcelLinks
End Sub

Then I would like a cell in the sheet to date stamp when the last update was
run.