copying and pasting

  • Thread starter Thread starter stephen clarke
  • Start date Start date
S

stephen clarke

I wonder if you can help me. I want to create a
spreadsheet which has multiple pages (the tabs at the
bottom of the screen), which are linked to each other in
various ways:

-Is there a formula whereby when a certain word is typed
in one cell, or a box checked, all the data in one row (or
column, etc) is transferred, or copied to another part of
the spreadsheet, on another page?

-Can I type a formula in a cell on one page which refers
to data in cells on another page (For example, a formula
on a cell on page 1 which adds all values in a column on
page 2) or even another spreadsheet?

-Can I copy the contents of one cell, x, and paste it into
another, y, in such a way that whenever x changes, y will
automatically update itself to whatever is currently in y?

Thank you

Stephen Clarke
 
Stephen,

Answers to your questions:

1. Yes, check the lookup functions (e.g. vlookup, hlookup,
lookup, index, match, offset...) If you want help with a
more specifc issue, describe it in more detail.

2,3. Yes, you have many options:
a) create a link to the cell by: typing = in the
destination cell and then click the source cell (wich can
be in another sheet or file)
b) type in the destination cell:
=[Sourcefilename]Sourcesheetname!cellrefernce, an example
would be:
=[Book1]Sheet1!A11
c) Copy the source cell (click ctrl+C), select the cell
where you want to create the link, Select Paste Special
from the edit menu, and check the paste link option.

Regards,
Felipe
 
Regarding question 2:
Yes, any excel formula can be ferenced to another
sheet/page or spreadsheet.

Type the formula as you would normaly do and when
selecting the cells change the sheet by using the tabs or
change the file by clicking ctrl+tab or the window menu
and select the cell or cell range.

Regards,
Felipe
 
For linking, just say that you want the cell to equal the
other cell, or the sum of the other columns. For example,
for cell a1 on the tab Summary to display a sum of
information in the A column on the Details tab, the
formula in A1 would read =SUM(Details!A:A). It's easy to
just type = and then hit the tabs at the bottom to go to
the tab for which the formula is applicable.

Linking is better than copying because it will update
automatically. If it is necessary to copy for some
reason, you can write a macro to do so. Play around with
the tools...macros...record macro function. You don't
have to know any code; you just start recording, then do
what it is you want done, then stop recording. The code
writes itself. You can then create a button on the
worksheet itself or on a toolbar and assign the macro to
it.

Jane
 
Back
Top