Excel: How to link between different sheets? Thread poster: Erik Hansson
|
|---|
Dear ProZ colleagues,
Does anyone has a good suggestion to solve this problem:
We would like to set up an availability calendar for our freelance translators. As one Excel sheet does not contain 365 tables (or 366 to be exact, one table for names and remaining for the days), we need to split up the year on several sheets. We would like to insert the names only in the first sheet, and then make one sheet for each month, having January on sheet 2, February on sheet 3 and ... See more Dear ProZ colleagues,
Does anyone has a good suggestion to solve this problem:
We would like to set up an availability calendar for our freelance translators. As one Excel sheet does not contain 365 tables (or 366 to be exact, one table for names and remaining for the days), we need to split up the year on several sheets. We would like to insert the names only in the first sheet, and then make one sheet for each month, having January on sheet 2, February on sheet 3 and so on.
However we would like to have a link from sheet 2, sheet 3 etc. back to sheet 1 which contains all the names, so the names would appear automatically on sheet 2 and onwards. Thus we would only have to make a change in sheet 1 if a new translator needs to be inserted.
We have looked into the help function in Excel 2003, also found some interesting hints, but it didn't work. Thanks a lot for your help!
Best regards
Erik ▲ Collapse
| | | | | Create a formula | Dec 19, 2008 |
You create a formula in the destination cell:
Type the equal sign = in the target cell and then, without pressing Enter, use the mouse to go to the source cell (even if it is in a different file or sheet), click the source cell (this adds the location of the cell to the formula you are creating), then press Enter. That should do it!
| | | | PiotrA
Local time: 20:34
English to Polish
+ ...
| Another option | Dec 19, 2008 |
You can follow the hint from Tomas. However, you can also automatize the process a bit. Proceed as follows:
Suppose, you have the names listed in Sheet1 in column "A", starting from cell A1 downwards.
Click on the tab marking Sheet2 (or "January"). Press "shift". Click on the tab of Sheet13 (or "December") while keeping "shift" depressed.
All tabs from January to December should be highlighted now. Now, without changing the worksheet, select cell A1 and write: "=Sheet1... See more You can follow the hint from Tomas. However, you can also automatize the process a bit. Proceed as follows:
Suppose, you have the names listed in Sheet1 in column "A", starting from cell A1 downwards.
Click on the tab marking Sheet2 (or "January"). Press "shift". Click on the tab of Sheet13 (or "December") while keeping "shift" depressed.
All tabs from January to December should be highlighted now. Now, without changing the worksheet, select cell A1 and write: "=Sheet1!a1" (this is an assumption, in reality an actual name of the sheet must be entered) . Press "enter". Select cell A1 again. Click the "copy" icon (or "edit" -> "copy" in the menu). Select at least as many cells in column A, as many names you have listed, starting from cell A2 downwards. Press "enter" in order to paste the formula to selected cells. Click on the Sheet1 tab in order to deselect the other sheets. From now on, any change you make in column A of Sheet1 will be reflected in all sheets from 2 to 13.
HTH
Regards
Piotr ▲ Collapse
| | | |
Thanks a lot Tomás and Piotr for your splendid support!
I tried both methods and decided for Piotr's suggestion as it seems that the lists on sheet 2 and onwards are updated automatically when anything has been changed on sheet 1.
Best regards and all the best for 2009!
Erik
| | | | To report site rules violations or get help, contact a site moderator: You can also contact site staff by submitting a support request » Excel: How to link between different sheets? | TM-Town | Manage your TMs and Terms ... and boost your translation business
Are you ready for something fresh in the industry? TM-Town is a unique new site for you -- the freelance translator -- to store, manage and share translation memories (TMs) and glossaries...and potentially meet new clients on the basis of your prior work.
More info » |
| | Protemos translation business management system | Create your account in minutes, and start working! 3-month trial for agencies, and free for freelancers!
The system lets you keep client/vendor database, with contacts and rates, manage projects and assign jobs to vendors, issue invoices, track payments, store and manage project files, generate business reports on turnover profit per client/manager etc.
More info » |
|
| | | | X Sign in to your ProZ.com account... | | | | | |
Login to reply/comment 
