Colors, which are just one aspect of 'properties' of cells work kind of
backwards. You go to the source cell FIRST and click it and copy it (Ctrl+C,
or Edit | Copy or click the copy icon in the toolbar) then you go to the cell
you wish to have the same appearance and paste it (ctrl+V, Edit | Paste, or
click the paste icon). You can then use your newly formatted cell as the
source for others.
Have to watch out when copying - if the cell has formulas with, those get
copied also and the cell references may change when you paste it. Sometimes
this is a good thing, even a desirable thing, sometimes not what you wanted
at all.
There is also a very handy tool for applying the format of a cell that
already looks the way you want - it's an icon that looks like a paint brush
and it is called the 'Format Painter': click a cell that has the appearance
you want, then click a cell where you want that appearance (no formulas or
values get copied). If you click-hold-and-drag, then the appearance is
applied to all cells you drag over.
Another trick with the Format Painter: choose cell with desired appearance
and double-click the Format Painter, now it becomes 'locked' until you click
it again. Now you can click one or more cells in separated locations and
have that original format applied to them, and the click-and-drag process
also works.
Sounds like you could probably use a good basic tutorial/reference for
Excel. Microsoft publishes a "Step-by-Step" series that is good, and for
Excel, Greg Harvey has published two that are good, depending on version of
Excel you're using. If you're using 2007, then
http://www.amazon.com/Excel-2007-Ref...102227-4267936
should do well, and if you're using 2003 or earlier then
http://www.amazon.com/Excel-2003-All...102227-4267936
I usually recommend those because they cover the same ground that the same
author's plain Excel 200# for Dummies does plus more, for only a few dollars
more, making them a better value as I see it. There's also a fairly
comprehensive list of books about Excel that are available he
http://www.contextures.com/xlbooks.html
"Man" wrote:
It worked perfectly thank you so much, I was wondering if I can also do the
same with colours?
"JLatham" wrote:
Should work for you.
In essence you can treat other worksheets and other workbooks just as if
they were part of the same worksheet, in a fashion - at least while doing it
manually like this and with all needed books open. It's just a matter of a
few more clicks, with Excel making formula and address adjustments for you
along the way.
Think of it like this: you have a value in A1 on a worksheet, and you want
to echo that value in cell X44 of that same sheet. Then in X44 of that sheet
you put =A1
But if you wanted to echo A1 from a different sheet in the workbook, you'd
start by typing = then click the other sheet and click cell A1 there and hit
[enter] and Excel would build something like
='Other Sheet'!$A$1
All we're doing here is taking that one step further and echoing from
another workbook, which means we have the extra 'click' of activating that
other workbook after typing the = symbol.
Just so you'll know, you can use these same methods inside of functions and
formulas, anywhere you'd use any cell reference. There are some worksheet
functions that will not work unless both workbooks are open, but for the most
part, they work even when the source workbook is closed.
"Man" wrote:
thank you so much I really appreciate it. I will give it a go and will let
you know how it went.
Thanks again.
"JLatham" wrote:
To do it manually, without any code, start out like this:
Open both workbooks. In the workbook that you want to 'echo' the
information in, choose the cell where you want it to appear and type in an =
symbol to start a formula. Choose the other workbook (the one you'll just be
typing in to), then choose the sheet where the information is or will be, and
choose the cell where it is or will be. Press the [Enter] key. Now there is
a link in the first (echo) book to the primary copy. At this time it will
look something like
=[sourceWorkbook.xls]'Sheet name'!$A$1
if you close the main workbook, the path to it will be placed into that cell
automatically by Excel.
If you need a few more cells, you can repeat the process for each. If you
would like to echo several cells, as on a single row, edit that first formula
to remove the $ symbols from the cell address. Then you can fill (look in
Excel Help for Fill Data) the formula across or down the sheet and it will
create multiple links to nearby cells automatically also.
Once you've set it up, you can close the source workbook, it does not have
to be open to see the data in the linked book. You can even link several
different pages from several different workbooks into the one book in this
fashion. Any time you open the workbook, the current information in the
source workbook will be available. You may have to allow it to "update
links", but there's even a setting you can make that will bypass that prompt.
The methods Tim offered are good alternatives under certain circumstances.
"Man" wrote:
I was wondering if there is any formula so that I can type information in one
workbook and have that information also appear in another workbook? (So as
that I don't have to type it in twice) and if so do I have to have both
workbooks open to do this?? Thank you