I have a small Requirement in Excel. Can you please help me out?
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
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)
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.
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