How do i use excel macro to show a value from another file?

Question

I want to make a reference to another file in the network so that I can display the value of a specific cell.

Example:
Project A has a total bill of $445
Project B has a total bill of $885
Project C has a total bill of $1234
These bills are all stored in their respective folders in the network eg. folder Project A, Project B etc.

I want to create a worksheet that collates those total bills together without me opening the file. Is that possible? Also, the total bill is displayed in a fixed cell in all of the files. Eg. all 3 files' total bills are shown in cell A5 in their repsecitve excel sheets. Please give me a detailed format as I'm not very good with macros. Thanks!

thanks but i wanna reference to another excel file... anyone else knows?

thanks clinky but i need to use macro... any macro experts?

Answer 1

If you are referencing something you really need to reference the whole filename starting with the drive to make sure that it absolutely links. It is referenced in the manner below:

='H:\Folder\[Filename.xls]Worksheet'!$A$44

...sheet'!$A$44 - continued because yahoo cut it off

In this example, the drive is H.

Make sure you put the apostrophes in the correct spot and surround the filename with brackets.

I've been writing VB macros for about ten years. But my position is if you don't have to write a macro, then avoid writing one.

Answer 2

no problem.

you need to open all 3 files to start with, as well as the one where you want to display the result.

click on the cell where you want to display result, press =,
then click on project A, and select the cell you want, click back to result program and enter.
If you want more than one added together, type "=SUM(" then select all the cells you want, by going to each of the projects at a time, with a comma seperating each cell, you will need to close parentheses" )" and press enter. Example below:


=SUM([rparam43.xls]data!$F$209,'[op10_51 casting.xls]all data'!$F$22)


the ONE PROBLEM is that those projects need to be on the same computer, otherwise it will give error message as the projects are not available.

Answer 3

I've referenced another worksheet before, but not another file.

Answer 4

hi in case you extremely in basic terms wished the comparable codecs on the two sheets pick the "total" sheet and replica - then pick "all" the different sheet - pick paste particular then codecs - this would replica the codecs purely so which you values are untouched. it ought to help