Liz has attended:
SharePoint course
Linking cells throughout a workbook....
Hello
We use excel workbooks for document issue sheets.
Each workbook has numerous worksheets, each with a different set of documents. Below the document list is a distribution list area. The distribution list needs to be consistent throughout the workbook. We use the cells to the right of the recipients names to record details of issues in each date column.
We have tried using one worksheet for the distribution list and linking this to all other worksheets using paste special.
This tends to fail due to the need to insert new recipients during the lifetime of the project. The details entered for each recipient on each document worksheet must be retained.
I remember there was a way of copying selected cells through a range of (or through all) worksheets in a workbook. I can't remember how this is done, please remind me. Also can you suggest a way that we can cater for inserting new recipients and if necessary resorting the recipients list into say alphabetical order - throughout the whole workbook - but without disrupting the existing issue date for issues made to date.
RE: linking cells throughout a workbook....
Hi Liz
Thank you for your question. May I suggest you forward an example to info@stl-training.co.uk and mark it for the attention of Andrew. I can take a look to see if this is something we can help you with.
In the meantime one way to apply a function to a group of cells across multiple sheets is to use what is referred to as a 3D formula.
This works where the data sheets are all structured similarly and you wish to summarise values on a cover sheet.
You also need to make sure that your data sheets are in a consecutive group in your workbook.
In the cell on the sheet that will contain the summary begin typing the formula as follows =SUM(
With the forumla still being edited move away from the cell and click on the first tab of the group of datasheets.
Click on the tab for the first datasheet that will be summaried and select the cell that contains the data to be summed.
Press and hold down the SHIFT key then click the last tab of the group of sheets.
Let go of shift and the mouse.
Type the close bracket character and press enter.
On your coversheet you will now have a formula that sums a value across multiple sheets.
Is this the kind of approach you were looking for?
I look forward to seeing the example file.
Kind regards,
Andrew
RE: linking cells throughout a workbook....
Hi Liz
thank you for sending through the example. It looks to me that Excel is working as a database (to track entries) and that we are looking for a query that will pull out records into one table - such as the sheet at the front of the Workbook. A query would also be able to handle new issues added at a later date.
I will check further but my initial impression is that to achieve this we would either need to incorporate some VBA programming or perhaps use Access (database) to compile the multiple sheet information into one place.
Kind regards,
Andrew
