Excel Data Reading from another Excel Shheet [Solved/Closed]

Mohinder - Nov 1, 2008 at 03:11 AM - Latest reply:  ger
- Jan 6, 2012 at 09:16 AM
Dear People,

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 recognise 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%


Thank you in Advance! Hope some day I am able to solve some of yours Issues!

Best Regards,
MOhinder
See more 

15 replies

Best answer
19
Thank you
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

Thank you, jobeard 19

Something to say? Add comment

CCM has helped 1676 users this month

Here is what worked for me:
Given: 3 Excel Files: "QuoteSummary.xls", "QuoteA.xls", "QuoteB.xls".
QuoteSummary.xls references the Option A and Option B files to compile the results. All the files are in the same directory so I didn't need the full path. In QuoteSummary.xls, there are two cells that reference the other two files. They contain:
='[QuoteA.xls]Quote Results'!$E$48
='[QuoteB.xls]Quote Results'!$E$48
The "Quote Results" is the name of my Worksheet in the OptionA and OptionB files.

You can type this all manually into the cells in the summary workbook, but I find it's much easier to have all the files open, click on the cell in the summary workbook, hit = key, then go up to the Window pull down menu, select the other file that you want to reference, select the Worksheet and Cell in that other file and hit enter. This fills in the formula for you and returns you back to the summary workbook.

(Steps done on Excel 2003)
Can you give any sample excel sheet for your answer...
Hi,

That was enormously helpful.
Thanks.

I have another question.

Given the general formula,

=[pathtoWorkboolX.xls]sheetname!cellReference

What I am doing is Hard-coding the names of external workbook and its sheetname.
To be precise, I am using: =SUM([mainBook.xls]Sheet1!$P$296:$P$336)

Is there any way such that the source workbook (WorkboolX.xls) could be selected dynamically, maybe an explorer window pops up and then I select the source workbook and it gets inserted into this formula ??

Thanks again in advance.
pls help us to find a formula to find for our marksheet which follows semester pattern and we need to find that a particular student apperaing for both the semester has passed both the semsester (ie; 6 papers in both semester) if not than we need a formulas to have a track to find that she wount be able to go for next semester plspls help.
Pls reply me on my email id as soon as possible pls.
email id Email Id removed for security
<ital> <ital> <ital>
 <ital> <ital> <code> <ital>Permalink (#12) 
</ital> </ital> </code> </ital> </ital> </ital> </ital>
0
Thank you
im not an excel wiz but I know that say you have two xls documents and you want to make a reference in the second document to the first then you can open up both of them and then choose right click on the sheet in the first doc and choose move or copy and then choose make a copy and choose the second doc as the place to put the copy.
Now you should have a new sheet in your second document that have exactly the same data as in the sheet of your first document. Notice that on this new sheet, each cell refers to the first document's sheet so you can use those codee in the cells to refer to the first document.
You will need to experiment with updating, i'm not sure how it would work. You might have to reopen the document for it to update.

Hope this helps
0
Thank you
I am trying to create a second excel spreadsheet by copying the original. When I do so, I loose my formula in the new document. Is there a way to copy an excel spreadsheet without loosiing the formula?
execl sheet in crystal reports by geeting htis atribute we get inherritanse by adding a delegate u get one polymorphism
0
Thank you
If U contact me on my mail I can convert your file as u required
my mail ID as156@rediffmail.com - This is free in my interest
since I am working in same field
Regards
A.S.Shukla
-3
Thank you
SAV,
To copy a spreadsheet to the next one open the orginal and in the same excel sheet program start a new window.
Make sure you highlight the whole screen ctrl-A, from the original.
Click on the new one and hit Ctrl-V.
I did it just to test it and it worked fine. If it is not copying perhaps you have something turned off or the version is off. I am using Excel 2007.
, respectfully,
Ryan
-3
Thank you
you can use the paste special command in that click on the paste link

or you can mail me alpeshvaghela_2006@yahoo.co.in or visit my you tube link

www.youtube.com/user/alpesh14386