Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help with lookup reference to other workbooks
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. |
#2
|
|||
|
|||
Hi
just replace the cell/range reference. Easiest way: Open both workbooks and select the source range with your mouse -- Regards Frank Kabel Frankfurt, Germany "chris w" <chris schrieb im Newsbeitrag ... 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. |
#3
|
|||
|
|||
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" wrote: Hi just replace the cell/range reference. Easiest way: Open both workbooks and select the source range with your mouse -- Regards Frank Kabel Frankfurt, Germany "chris w" <chris schrieb im Newsbeitrag ... 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. |
#5
|
|||
|
|||
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. "Frank Kabel" wrote: Hi could you provide more details how your other sheet is structured. e.g. post some example rows and describe which value to return -- Regards Frank Kabel Frankfurt, Germany "chris w" <chris schrieb im Newsbeitrag ... 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" wrote: Hi just replace the cell/range reference. Easiest way: Open both workbooks and select the source range with your mouse -- Regards Frank Kabel Frankfurt, Germany "chris w" <chris schrieb im Newsbeitrag ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
Referencing workbooks | New Users to Excel | |||
Workbooks...I'll try this again... | Excel Discussion (Misc queries) | |||
Ranges with in a LOOKUP | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |