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]>
To: "Multiple recipients of list offtopic" <[EMAIL PROTECTED]>
Sent: Monday, July 14, 2003 7:58 AM
Subject: Re: [DUG-OFFTOPIC]: Excel
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]>
To: "Multiple recipients of list offtopic" <[EMAIL PROTECTED]>
Sent: Friday, July 11, 2003 12:19 PM
Subject: [DUG-OFFTOPIC]: Excel
> 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.
>
> ---------------------------------------------------------------------------
> New Zealand Delphi Users group - Offtopic List - [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
> To UnSub, send email to: [EMAIL PROTECTED]
> with body of "unsubscribe offtopic"
> Web Archive at: http://www.mail-archive.com/offtopic%40delphi.org.nz/
>
---------------------------------------------------------------------------
New Zealand Delphi Users group - Offtopic List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
To UnSub, send email to: [EMAIL PROTECTED]
with body of "unsubscribe offtopic"
Web Archive at: http://www.mail-archive.com/offtopic%40delphi.org.nz/
---------------------------------------------------------------------------
New Zealand Delphi Users group - Offtopic List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
To UnSub, send email to: [EMAIL PROTECTED]
with body of "unsubscribe offtopic"
Web Archive at: http://www.mail-archive.com/offtopic%40delphi.org.nz/
