Calculating cells between separate MS Excel files?

[MickG]

My excel abilities are self taught and learnt as I go, plus have picked up lots from asking q's on AULRO, but just stumbled across an anomoly which, if possible, will open up a whole new world of efficiency.

Q. Can formulas calculate between separate spreadsheets/files?

I am using Office 2007 and I simply copied a bunch of cells into another file with a view to reformatting them for my new spreadsheet and changing the formulas to suit, but noticed that the copied cells were still calculating back to the file I had copied from:eek:
So, if it is possible to calculate between spreadsheets, would I simply have the files open I want to calculate from and set formulas that way or is there an easier/safer way?

All comments appreciated.

I really should go and do an excel course:eek:

Aye, Mick

[weeds]

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

[WhiteD3]

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.

[MickG]

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?:D
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

[WhiteD3]

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?:D

Yes.


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.

[Lotz-A-Landies]

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

[stikman]

:D

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

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 :cool:

[WhiteD3]

Just call your excel helpddesk if you need any help :cool:


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

[MickG]

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

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

Aye, Mick

[FenianEel]

Mick,

In a previous life, I also taught Excel among other things. Pretty sure I still have the discs and workbooks, (basic though to advanced), If I can find them I'll get them to you. Still used to get updated course material sent to me, I think the last ones I received were only about 18 mths ago. ;)
Now to find them :eek::D

[sschmez]

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

Aye, Mick

You haven't discovered Find/Replace then ????? ......

[MickG]

Mick,

In a previous life, I also taught Excel among other things. Pretty sure I still have the discs and workbooks, (basic though to advanced), If I can find them I'll get them to you. Still used to get updated course material sent to me, I think the last ones I received were only about 18 mths ago. ;)
Now to find them :eek::D

Mate, you're a scholar and a gentleman:D Been meaning to call you as well as I haven't heard from you in a while...will try you now:D

Cheers for the offer and there's a Guinness or two in it for you if you find them;):D

[MickG]

You haven't discovered Find/Replace then ????? ...... Would appear not......do explain though:D

[sschmez]

Would appear not......do explain though:D

Excellent little feature to be found in the "Edit" menu dropdown.

Backup your file first - don't ask how I know:(
Then tell it to find "07-08" or whatever you need changed and then tell it to replace that with "08-09" or whatever you want it to be. Use it to change formulae only or every instance of "07-08" (eg. incl headings).

When Ms Smith from the sales team becomes Mrs Jones change over very quickly and easily (and again when she gets back from her honeymoon and she's actually Mrs Johnes):D

When spreadsheets are linked as described earlier and changes are made to the source (Column added) so that now the data you need is in cell G7 instead of cell F7, find/replace (not necessary if both files are open when changes are made - excel adjusts it automatically)

Stevo

[Basil135]

Just to add a bit more confusion to this, you can also "paste special" which will give you another set of headaches, err sorry, that should be options.;)

Say you want to take all of the data from one spreadsheet, and paste it into another without all of the formulas, then use "values". This will give you the data, but will not update. Useful when sending files to senior management that don't really know what they are doing.:mad:

Should you wish to use the same formulas in another spreadsheet, then use "formulas". Then save the new file under a different name.