Excel - Read data from another sheet

Issue

Problem Description

Content of Raw Material Prices are maintained in a Xl sheet. These Material Prices are then used for Calculating the total Price of a Component in another Xl sheet using a Formula.

Requirements

• 1. The Component Cost Xl File should be able to recognize the Raw Material Price File.
• 2. The Cost of the Material should be then read from 1st Excel sheet.
• 3. Calculation in the Component sheet should update depending on the Material Prices.
• 4. The Update should be carried out in a simplified mean, either by Macro or some other easier Method.
• 5. Integration steps should be easy and should take care of the Problems regarding different Paths.
• 6. The Extension of the Components as well as Material should be automatically taken care of (automation).

Sample

Sheet 1: MaterialPrice.xls, Path: d:\\xyz

Raw Material Price
Aluminium 5,34
Steel      3,35


ComponentCal.xls , Path : d:\\abc\\def\\

Component Weight(Kg) RawMaterial% Total Price

Gear 46 Aluminium 50% 46*0,5*Aluminium + 46*0,5*Steel (from 1st Sheet)
Steel 50%

Solution

Given two (or more) workbooks where A needs data from some other workbookB.cell,

in Workbook A at the location desired enter
=[pathtoWorkboolX.xls]sheetname!cellReference

Notice the sheetname!cellreference is how to get data from one sheet to another in the same workbook

If you need to navigate on the directories, just start to enter =workbook and tab to get the file browser.
This will then create an absolute path the the file, eg
=c:\xxx\yyy\zzz\workbook

Thanks to jobeard for this tip.

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.