|
There are a reason I used the range name - that is
because
they make your spreadsheet almost totally
bulletproof. If you
use the formula as you state below then it is very
easy to
corrupt your spreadsheet. That is why I asked
you who will be
using this spreadsheet. If anyone else uses
it then it is almost
guaranteed that within a few months you will have
errors.
For example - change the formula below (in row 15
of the
Totals sheet) to
=INDIRECT(A15 & "!F4")
Now copy that formula down the column on the totals
sheet to
all required rows. Next go to any used
cell in column A and
press CTRL+X and paste it to somewhere else.
Now notice
that the number from there has gone from column A
so re-enter
it into column A - but use a different
number.
Repeat this with the formula which includes the
reference to the
range name "Sheet" and you will see that making a
small mistake
like moving data instead of copying it does not
corrupt your data.
This happens far more than you would believe and
can make any
decisions based on the spreadsheet invalid as the
data is not
correct.
Hope that explanation helps you understand Excel a
bit better.
Also, if you want to avoid range names then
this will work the same
as what you have below:
=INDIRECT(A:A,"!F4")
Chrissy.
----- Original Message -----
Sent: Wednesday, July 16, 2003 7:39
AM
Subject: RE: [DUG-OFFTOPIC]: Excel
With
many thanks to Chrissy, I stripped the formula she provided me, and now, using
the Sheet # column (first at left), I use the formula:
=INDIRECT(Sheet & "!F4")
It's
simple and works just the way I want it.
Thanks a lot all.
Cheers,
John.
Hi John,
In fact, something like my example would
give you exactly what you want.
Let's say your summary sheet has Amount, Name,
Details in columns B, C, D and they are in the same columns on the other
sheets, all on row 4. Let's say your summary grid starts in row
3. Then in the first cell in the summary grid put the
following formula, then copy it to all other cells in the summary
grid:
=INDIRECT("'" & $A3 & "'!" &
ADDRESS(4, COLUMN()))
Note the double and single quotes. You
need the Address function instead of a simple cell reference because the
whole thing is inside the Indirect function and needs to be a string, not a
reference. If the Amount etc are in different columns to the
summary sheet, add the offset to the Column function as I had
to.
Add a new sheet? Copy the last row and
change the cell in column A, and hey presto it's there!
Unfortunately you can't use a named range in
this formula, because a named range will refer to a range on a specific
sheet or sheets and this needs to refer to any generic sheet. In
other circumstances, particularly referring to specific totals, intersecting
named ranges can be really handy, as Chrissy mentioned.
Cheers
Pedrocelli
----- Original Message -----
Sent: Tuesday, July 15, 2003 9:01
AM
Subject: RE: [DUG-OFFTOPIC]:
Excel
Hi Pedrocelli,
Thanks for your email but I don't think that is
quite what I want.
I want to report in a Totals Sheet the
following:
Sheet #, Amount,
Name,
Details
===============================================
1 $123 Telecom
Softw Development
2 $56 Vodafone Installation
Thus the text in
column Amount, Name and Details derive from sheet 1 and
sheet 2.
To
achieve this, you would "normally" use the formula ='2'!$F$4 where
'2'!
refers to sheet named '2'. Note that this formula would be in all
cells for
Amount, Name and Details but with a different offset
($F$4).
When I create a new sheet I'd like to copy (in the Totals
Sheet) the last
row and paste it under the last one and then change the
first column's value
from 2 to 3 and that should update the information
Amount, Name and Details
on that row from the given new created
sheet.
Thus the formula ='2'!$F$4 should be changed to
something where the string
value 2 (sheet number) is read from the first
column.
I hope this is a bit more clear what I want to
do.
Thanks a lot for any help.
John.
>
-----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Behalf Of
Pedrocelli
> Sent: Monday, 14 July 2003 23:19
> To: Multiple
recipients of list offtopic
> Subject: Re: [DUG-OFFTOPIC]:
Excel
>
>
> Hi John,
> If you just want the totals,
then Chrissy's nailed it. If you
> want to list them
> all
on the summary page, then total or do other things with the
> list -
like
> graph it - then Yes you can do it, and more ...
>
>
Here's a formula I have taken from a s/s I have developed for tracking
and
> totalling my tax accounts:
>
> =INDIRECT("'" &
LEFT($A5,3) & " C'!" & ADDRESS(Totals.C,
COLUMN()+28))
>
> I have three sheets for each month, named "Apr
A", "Apr B" and
> "Apr C", etc.
> Each sheet has totals on
potentially different rows (as they
> typically held
> quite
different numbers of rows of data), so the total row number
> is coded
in
> the cell named Total.C and its value is 35. This formula
resides
> in cell B5 on a
> totalling sheet, and the value of
cell A5 is "April". The columns being
> totalled are in columns
AD onwards - 28 columns over from here.
> The INDIRECT
>
function allows you to build a text string representing the full
>
reference to
> the cell you want, and the ADDRESS function maps a
column and row
> to the string
> representation of the cell
address, so this formula is equivalent to
> ='Apr C'!AD35
> and
by copying this formula through the whole 12-month, n-column
> grid on
my
> total page I can list the totals of all 12 month sheets and do
some grand
> totalling.
>
> Probably a bit of overkill in
hindsight, but I developed it
> before I had ever
> learnt
anything about Delphi! At the time it was a big
> improvement on
writing
> it all up on a paper cashbook, and totalling everything
across and down,
> transferring month totals to month sheets and
totalling across
> and down again.
> Then try adding a
transaction you've forgotten. Then another!
>
> You could
probably do it much more effectively with pivot tables
> as well but
I
> can't tell you much about them, I'm sorry.
>
> Anyway,
you can do quite a lot in Excel and I recommend you make
> good use of
the
> help file.
>
> Cheers
>
Pedrocelli
>
> ----- Original Message -----
> From:
"Chrissy" <[EMAIL PROTECTED]>
>
> I do not follow
exactly what you are saying (too little sleep etc)
> but I think what
you are looking for is this.
>
> If you have four sheets called
Summary, Sheeet2, Sheet3, Sheet4
> (not 2, 3, 4) then
------
>
> In cell A1 of the summary sheet enter this
formula
>
>
=SUM(Sheet2:Sheet4!A1)
>
>
>
> That will sum the all
the values from cell A1 from the three detail
> sheets and show the
result in cell A1 of the summery sheet.
>
>
> There are
some things you can do if you want to make sure that
> this always
works and does not require the person using it to
> know what not to
do. If you want more help let me know.
>
>
Chrissy.
>
>
>
> ----- Original Message
-----
> From: "John" <[EMAIL PROTECTED]>
> > Hi all,
>
>
> >
> > Does anybody know how to solve this reference
problem in Excel?
> >
> > I have multiple sheets. The
first sheet is a summary of all
> other sheets.
> >
>
> The Sum-sheet has two columns with the following cell contents:
>
>
> > Sheet # Value
> > ---------------------
>
> 2 ='2'!$C$4 value from sheet 2 of cell C4
> > 3 ='3'!$C$4
value from sheet 3 of cell C4
> > 4 ='4'!$C$4 value from sheet 4 of
cell C4
> > etc.
> >
> > Is there any way of
using the sheet value (column 1) in the
> second column's
> >
formula?
> >
> > Something like this: =SHEET($A$1)!$C$4
where $A$1 is the first column.
> >
> >
> >
Thanks for any help.
> >
> >
John.
|
