PDA

View Full Version : Excel Sreadsheets



rny
26-06-2006, 05:23 PM
Hi folks, is there a way or function to share data between two or more worksheets in a Excel Spreadsheet? Data would be via a formula, so would need to be able to share the values only. Sorry for my apparent lack of knowledge as I am still getting the hang of using Excel.
rny.

FoxyMX
26-06-2006, 05:37 PM
Yes there is but a few more details would help.

rny
26-06-2006, 05:52 PM
Ta for the reply, here is what I am attempting to do as best as I can explain. Results from a sporting event involving a number of people from different clubs, need to be sorted into an order of highest to lowest, over a series of matches, and then a formula applied to apportion prizemoney depending on number of entries and entry fees, (already have that), for each of the matches, then sort into various clubs so the final print out contains the infomation for each club. Am achieving the desired result at the moment by doing a lot of copy, paste special between worksheets. Am just looking for an easier and less time consuming way. Hope this helps.
rny.

FoxyMX
26-06-2006, 06:22 PM
We have Excel gurus here that ought to find that a piece of cake so sit tight and hopefully they won't be too far away. :)

rob_on_guitar
26-06-2006, 06:35 PM
Crickey. Sounds confusing. :waughh:

bunta
26-06-2006, 06:53 PM
Sounds like the easiest thing for you to do would be to put the information from each of those spreadsheets into the one spreadsheet on to different "sheets". Then you can reference the data between each sheet a lot easier and can create formulas between them quite simply.

Otherwise you can still access data from different spreadsheets by referencing the name of the spreadsheet like so:

='[spreadsheet_name.xls]Sheet1'!$A$5

or something similar....

EDIT: an easy way to do this is to open up the spreadsheets to be referenced in Excel. Hit = or start typing your formula and then you can switch to the other spreadsheet and click on the desired cell and hit enter and it will insert the reference to that cell automatically.

rny
26-06-2006, 09:15 PM
Thanks bunta, looks like an easy fix, will give it a try.
rny

Parry
27-06-2006, 08:15 AM
Hi, if you are receiving a number of workbooks which need to be collated (eg 10 people giving you a workbook and you need to take the results from each and populate to one workbook) then if the workbooks have the same format it may be easier with code depending upon whether the process gets repeated and how many people involved. For example, you can place the books into a folder then have code loop through every file in the folder and bring in the info.

If this is the case then you can email me at parryishere@yahoo.com.au with the destination book and a book that has already had data inserted with the specific data highlighted in the destination one so I can see where you place the info.

regards,
Graham

rny
27-06-2006, 01:07 PM
Thanks for the reply Graham, with the info supplied so far, I have been able to achieve what I set out to do. Learning more about the capabilities of Excel all the time. Thanks again.
rny

MMNZ
27-06-2006, 04:24 PM
There are a couple of more possibilities than just link one cell.
You could work with named areas and / or use the VLOOKUP formula to get data from one sheet to another one.
If you collect workshhets and want to cumulate them, you can have different layouts, if you use Named areas.