Excel - Read data from another sheet

October 2016


I have a small Requirement in Excel. Can you please help me out?

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.


  • 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).


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%


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

in Workbook A at the location desired enter

Notice the sheetname!cellreference is how to get data from one sheet to another in the same workbook
so adding the [pathname] just addes the workbook location.

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

Thanks to jobeard for this tip.

Related :

This document entitled « Excel - Read data from another sheet » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.