Can Excel copy text from cell in one worksheet to cell in another?

May 15, 2007 9:17 AM

MS Excel help, please. I'm creating a multi-sheet workbook, and cells on one sheet will contain the same text as cells on another sheet. Is there a way I can avoid having to enter the text on each sheet? Is there a function that will automatically copy text from a cell on one sheet to a cell on another sheet?
posted by msacheson to technology (7 comments total)

Hold down the control key and select all of the worksheets that are going to contain the duplicate text (the tabs on the bottom will all turn white). Type your text in once... it will appear on all of the worksheets.

Once your ready to work on each individual worksheet, just select that one worksheet and customize it (the worksheet that you are working on should be the only one that is white).
posted by jerryg99 at 9:34 AM on May 15, 2007 [1 favorite]

jerryg99 beat me to it. Keep in mind that if you enter the data in cell B29 in sheet 1, it will be in cell B29 in sheet 2 -- if it needs to be in a different cell, this won't work.
posted by Rock Steady at 9:36 AM on May 15, 2007

If the data is going to be in different places on the two worksheets, you can set up a formula in the second sheet (i.e., =Sheet1!A1) and copy the formula into the other cells. This should work as long as it the data is contiguous.
posted by trox at 9:40 AM on May 15, 2007

Thanks, everyone. trox, your answer helps a lot!
posted by msacheson at 10:05 AM on May 15, 2007

I would propose an addition to trox's advice. His advice works very well IF you will never add a column to a worksheet above the cell that you referenced. If you may (which causes the link to point to something you don't necessarily want it to), then you'll want to try this, instead.

Select the cell or cells that you want to link to. To the right of the function/value bar, you'll see the identity of the cell or cells you just selected. Change that A1, etc, to a name that specifically identifies the data. This will allow you to link to that value, and any added columns or rows in your spreadsheet will not impact the link.
posted by odi.et.amo at 10:17 AM on May 15, 2007

Use a named range....I would. then you can reference the named range whenever you need to, and so long as the values in the named range remain in place, you won't have any update issues.

I always prefer to name my ranges and cells.
posted by TomMelee at 11:02 AM on May 15, 2007

Thanks SO much for the named range thing; it sparked something for me and I automated a spreadsheet today because of it.
posted by chuckdarwin at 6:17 AM on July 2, 2007