RE: [DUG-OFFTOPIC]: Excel

[John]

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. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Pedrocelli Sent: Wednesday, 16 July 2003 00:06 To: Multiple recipients of list offtopic Subject: Re: [DUG-OFFTOPIC]: Excel 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 ----- From: "John" <[EMAIL PROTECTED]> To: "Multiple recipients of list offtopic" <[EMAIL PROTECTED]> 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.