A few words of thanks would be greatly appreciated.

Excel - Read data from another sheet


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.

A few words of thanks would be greatly appreciated.

Ask a question
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.
This document, titled « Excel - Read data from another sheet », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).