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]>
> 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/
