Hi Chrissy,
Hey, I never thought of putting total rows on the top, though I do have some
total columns on the left. I can see why users would like that. That's worth
remembering.
I know I could do more to that spreadsheet - I could tweak it 'till the cows
come home! But seriously if I decide to improve on it, I will replace it with
a simple d/b and Delphi program ;-) , now that I can. I've stopped using it
anyway but it worked well and I had little if anything more functional to do to
it so I left it at that.
Cheers
Pedrocelli
----- Original Message -----
From: "Chrissy" <[EMAIL PROTECTED]>
To: "Multiple recipients of list offtopic" <[EMAIL PROTECTED]>
Sent: Tuesday, July 15, 2003 8:56 AM
Subject: Re: [DUG-OFFTOPIC]: Excel
John - e-mail me privately if you like and I will do your
spreadsheet for you - I am sure that it will only take a
few mins to do.
Pedrocelli - if you are still using that spreadsheet if can
be simplified substantially. One thing to consider is to
put totals in the top row and in the left column. This
may seem a little strange at first for users but it takes them
only about a day to get used to it and from then on they
usually ask to have all existing spreadsheets altered to this
format.
The next thing to do is to use range names a bit more efficiently.
Things like intersecting ranges make for greatly simplified formulas
which can be more easily verified.
Chrissy.
BTW - I know Excel rather well. ;-)
----- Original Message -----
From: "Pedrocelli" <[EMAIL PROTECTED]>
To: "Multiple recipients of list offtopic" <[EMAIL PROTECTED]>
Sent: Monday, July 14, 2003 11:19 PM
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]>
> 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/
>
---------------------------------------------------------------------------
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/
