Mobile menu

Excel: How to link between different sheets?
Thread poster: Erik Hansson

Erik Hansson  Identity Verified
Germany
Member (2002)
Swedish
+ ...
Dec 19, 2008

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


Direct link Reply with quote
 

Tomás Cano Binder, CT  Identity Verified
Spain
Local time: 22:29
Member (2005)
English to Spanish
+ ...
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!


Direct link Reply with quote
 
PiotrA
Local time: 22:29
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!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


Direct link Reply with quote
 

Erik Hansson  Identity Verified
Germany
Member (2002)
Swedish
+ ...
TOPIC STARTER
Thank you! Dec 29, 2008

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


Direct link Reply with quote
 


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?

Advanced search






CafeTran Espresso
You've never met a CAT tool this clever!

Translate faster & easier, using a sophisticated CAT tool built by a translator / developer. Accept jobs from clients who use SDL Trados, MemoQ, Wordfast & major CAT tools. Download and start using CafeTran Espresso -- for free

More info »
Across v6.3
Translation Toolkit and Sales Potential under One Roof

Apart from features that enable you to translate more efficiently, the new Across Translator Edition v6.3 comprises your crossMarket membership. The new online network for Across users assists you in exploring new sales potential and generating revenue.

More info »



All of ProZ.com
  • All of ProZ.com
  • Term search
  • Jobs