Excel - Read data from another sheet

May 2017


Issue


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.

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
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
=c:\xxx\yyy\zzz\workbook



Thanks to jobeard for this tip.

Related


Published by aakai1056. Latest update on November 8, 2012 at 12:33 PM by Jeff.
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).