0
Thanks

A few words of thanks would be greatly appreciated.

Excel - Read data from another sheet


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.
0
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
Jean-François Pillou

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.

Learn more about the CCM team

0 Comments