Thread: Calculating cells between separate MS Excel files?

not sure that you can do it across differant files.....i guessing you know you can do it across the differant sheets/tabs at the bottom of the page

Originally Posted by MickG

Q. Can formulas calculate between separate spreadsheets/files?

Yes. When you reference a cell in another spreadsheet or another tab within the same spreadsheet, it's called a link.

It is easy to accidentally create these links when copying from one sheet to another.

You can edit, update and delete links as required.

If you do reference another sheet in a calc, you will be asked to "update" the data when opening the spreadsheet.

Deleting, renaming or moving the linked sheet will cause errors in your calcs.

Originally Posted by WhiteD3

Yes. When you reference a cell in another spreadsheet or another tab within the same spreadsheet, it's called a link.

It is easy to accidentally create these links when copying from one sheet to another.

You can edit, update and delete links as required.

If you do reference another sheet in a calc, you will be asked to "update" the data when opening the spreadsheet.

Deleting, renaming or moving the linked sheet will cause errors in your calcs.

Bingo, so if my files are left in their loaction, unchanged and it is simply cell vlaues that changes, the calculations between files once set up will remain?
Do I simply open the files involved, and set the formulas up this way?

Weeds, yes formulas between sheets/tabs are no probs, thanks.

Aye, Mick

Originally Posted by MickG

Bingo, so if my files are left in their loaction, unchanged and it is simply cell vlaues that changes, the calculations between files once set up will remain?

Yes.

Originally Posted by MickG

Do I simply open the files involved, and set the formulas up this way?

Say your sheet with the calcs in it is the Master and the sheets with the values in them are Sources.

You can change values in the Source sheets whenever you want to.

When you open the Master you'll be asked to "Update links" or some such. Click Yes and Excel will retrieve the current info from the Source sheets.

Linking sheets works well, you just have to be anal about file names and locations.

What WhiteD3 said and you can even copy the spreadsheets to disk or memory stick so long as you keep the filenames and path on the media the same as on the machine (or you correct the link in the spreadsheet).

Diana

I think we need to have an Excel course over a few beers young man

With both files open you can reference back and forward between them the same way you do with sheets. I.e. click = select required file/sheet/cell and hit enter. Calculations/Lookups work in the same way. While both files are open the formula will refer to the file name, if the second file is closed the formula will refer to the file name and location i.e. C:/micks stuff/test.xls

Example:
='A:\Daily Manager Reports\WE 28.06.08\[Rep Performance Summary Report (28 06 08).xls]Daily State Summary'!$K$7

File is located on the A Drive under folders Daily Manager Reports -> WE 28.06.08, File name is Rep Performance Summary Report (28 06 08).xls and the data is in cell K7 of the Daily State Summary tab/worksheet.

Just call your excel helpddesk if you need any help

Originally Posted by stikman

Just call your excel helpddesk if you need any help

Tried that once but my Punjab-e was a bit rusty.

I need to start paying you guys a consultancy fee Yes, a training session would be good Philippe.....I will eventually do a proper one, (not that your teachings would not be proper) but next question is, do I do the novice or intermadiate course

Thanks for the help guys....now to spend the next 2 weeks modifying all my spreadsheets for the next fin year

Aye, Mick