Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect Function() - summing across sheets
Hi,
I'm struggling to unnderstand what's wrong with the following, and after googling around I saw one comment that INDIRECT() was somewhat problematic if using across worksheets. I have several sheets, the first is named "RGB1" and a later one is named "RGB9", in between are other sheets with values in B2, all of which I want to add. There are sheets after RGB9 which I don't want included. So the formula =SUM(RGB1:RGB9!$B$1) works fine. However for various reasons I want to hold the names of the two sheets in A1 & A2 since these are variables. I've tried all sorts but am unable to get an INDIRECT() to work. The obvious =SUM(INDIRECT(A1&":"&A2&"!"&$B$1)) doesn't work, nor indeed something like =SUM(INDIRECT("'" & A1 & ":" & A2 &"'!"&$B$1)) or various other combinations using quotes around the A1 & A2 references. Any ideas please? Usual TIA Rgds __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect Function() - summing across sheets
In article
k (Richard Buttrey) wrote: Hi, I'm struggling to unnderstand what's wrong with the following, and after googling around I saw one comment that INDIRECT() was somewhatproblematic if using across worksheets. I have several sheets, the first is named "RGB1" and a later one is named "RGB9", in between are other sheets with values in B2, all ofwhich I want to add. There are sheets after RGB9 which I don't want included. So the formula =SUM(RGB1:RGB9!$B$1) works fine. However for various reasons I want to hold the names of the two sheets in A1 & A2 since these are variables. I've tried all sorts but amunable to get an INDIRECT() to work. The obvious =SUM(INDIRECT(A1&":"&A2&"!"&$B$1)) doesn't work, nor indeed something like =SUM(INDIRECT("'" & A1 & ":" & A2 &"'!"&$B$1)) or various other combinations using quotes around the A1 & A2 references. Any ideas please? Usual TIA Rgds ¾**__* Richard Buttrey Grappenhall, Cheshire, UK ¾**__________________________ Sorry, Should have referred in the formula to B2 not B1. Mea culpa. *** I'm using an evaluation license of nemo since 81 days. You should really try it! http://www.malcom-mac.com/nemo |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect Function() - summing across sheets
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect Function() - summing across sheets
Let's say that in A1 thru A3 we have:
RGB5 RGB9 B2 Then first install the following UDF: Function addacross(r1 As Range, r2 As Range, r3 As Range) As Variant Application.Volatile Dim s1 As String, s2 As String, s3 As String s1 = r1.Value s2 = r2.Value s3 = r3.Value doit = False For i = 1 To Sheets.Count If Sheets(i).Name = s1 Then doit = True End If If doit Then addacross = addacross + Sheets(i).Range(s3).Value End If If Sheets(i).Name = s2 Then doit = False End If Next End Function Next, in an unused cell, enter: =addacross(A1,A2,A3) This should give the sum of the B2's in sheets RGB5 thru RGB9. UDFs are very easy to install and use: 1. ALT-F11 brings up the VBE window 2. ALT-I ALT-M opens a fresh module 3. paste the stuff in and close the VBE window If you save the workbook, the UDF will be saved with it. To use the UDF from the normal Excel window, just enter it like a normal Excel Function To remove the UDF: 1. bring up the VBE window as above 2. clear the code out 3. close the VBE window To learn more about UDFs, see: http://www.cpearson.com/excel/Writin...ionsInVBA.aspx -- Gary''s Student - gsnu2007g "Richard Buttrey" wrote: Hi, I'm struggling to unnderstand what's wrong with the following, and after googling around I saw one comment that INDIRECT() was somewhat problematic if using across worksheets. I have several sheets, the first is named "RGB1" and a later one is named "RGB9", in between are other sheets with values in B2, all of which I want to add. There are sheets after RGB9 which I don't want included. So the formula =SUM(RGB1:RGB9!$B$1) works fine. However for various reasons I want to hold the names of the two sheets in A1 & A2 since these are variables. I've tried all sorts but am unable to get an INDIRECT() to work. The obvious =SUM(INDIRECT(A1&":"&A2&"!"&$B$1)) doesn't work, nor indeed something like =SUM(INDIRECT("'" & A1 & ":" & A2 &"'!"&$B$1)) or various other combinations using quotes around the A1 & A2 references. Any ideas please? Usual TIA Rgds __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect Function() - summing across sheets
In article Ron
wrote: On Wed, 02 Apr 2008 09:33:45 GMT, (Richard Buttrey) wrote: Hi, I'm struggling to unnderstand what's wrong with the following, and after googling around I saw one comment that INDIRECT() was somewhatproblematic if using across worksheets. I have several sheets, the first is named "RGB1" and a later one is named "RGB9", in between are other sheets with values in B2, all ofwhich I want to add. There are sheets after RGB9 which I don't want included. So the formula =SUM(RGB1:RGB9!$B$1) works fine. However for various reasons I want to hold the names of the two sheets in A1 & A2 since these are variables. I've tried all sorts but amunable to get an INDIRECT() to work. The obvious =SUM(INDIRECT(A1&":"&A2&"!"&$B$1)) doesn't work, nor indeed somethinglike =SUM(INDIRECT("'" & A1 & ":" & A2 &"'!"&$B$1)) or various other combinations using quotes around the A1 & A2 references. Any ideas please? Usual TIA Rgds ¾¾**__* Richard Buttrey Grappenhall, Cheshire, UK ¾¾**__________________________ I do not believe that INDIRECT can be used to construct a 3D reference. However, with certain constraints, Laurent Longre (author of morefunc.xll, a very useful add-in) discovered that you can use INDIRECT to construct an arrayof references, which can have a similar result. For example, with a number 2 in A1, and number 9 in A2, the following will SUMthe values in Sheet2:Sheet9!B1: =SUMPRODUCT(N(INDIRECT("Sheet"&ROW(INDIRECT(A1&":" &A2))&"!"&ADDRESS(1, ¾**2¨¨¨¨ The argument for the INDIRECT function resolves into this array: {"Sheet2!$B$1";"Sheet3!$B$1";"Sheet4!$B$1";"Sheet5 !$B$1";"Sheet6!$B$1" ;"Sheet7!$B$1";"Sheet8!$B$1";"Sheet9!$B$1"} The N function is required -- INDIRECT won't pass the values without it. Don'tknow why. You could use the SUM function instead of SUMPRODUCT but, at least in Excel 2007, you would have to enter the formula as an array-formula (e.g. with<ctrl<shift<enter). In your example, assuming your RGB sheets are consecutively numbered, you couldsubstitute "RGB" for "Sheet". Obviously there are other methods of constructing the required array. If the sheet names were not related by a simple numbering scheme, you couldenter the sheet names individually into a1:an and use something like: =SUMPRODUCT(N(INDIRECT(A1:An&"!"&ADDRESS(1,2)))) But if there are any empty entries (or invalid sheetnames) in A1:An, you willprobably get a #REF! error --ron Thanks for the detailed response Ron, The first mentioned possible solution =SUMPRODUCT(N(INDIRECT("Sheet"&ROW(INDIRECT(A1&":" &A2))&"!"&ADDRESS(1, *2¨¨¨¨ Seems to work to a point, but unless I've misunderstood something, this appears to work with the VBA sheet names rather than the tab names. For instance in my test workbook, the sheets left to right have tab names of Sheet2, Sheet3, Sheet4, Sheet5 and Sheet9, however VBA (and presumably the Indirect function, knows these as Sheet2, Sheet4, Sheet9, Sheet5 and Sheet3, presumably because I've been changing the names and order whilst attempting to get to grips with this problem. i.e. the straightforward non indirect function adds up all 5 sheets because Sheet2 & Sheet5 are the first and last in the order, whereas the Indirect() function is only summing the first 4 sheets. Is there any modification I can make to have the ...Indirect() formula total the same as the non Indirect version? Thanks once more, Richard *** I'm using an evaluation license of nemo since 81 days. You should really try it! http://www.malcom-mac.com/nemo |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect Function() - summing across sheets
In article
Gary''sStudent wrote: Let's say that in A1 thru A3 we have: RGB5 RGB9 B2 Then first install the following UDF: Function addacross(r1 As Range, r2 As Range, r3 As Range) As Variant Application.Volatile Dim s1 As String, s2 As String, s3 As String s1 = r1.Value s2 = r2.Value s3 = r3.Value doit = False For i = 1 To Sheets.Count If Sheets(i).Name = s1 Then doit = True End If If doit Then addacross = addacross + Sheets(i).Range(s3).Value End If If Sheets(i).Name = s2 Then doit = False End If Next End Function Next, in an unused cell, enter: =addacross(A1,A2,A3) This should give the sum of the B2's in sheets RGB5 thru RGB9. UDFs are very easy to install and use: 1. ALT-F11 brings up the VBE window 2. ALT-I ALT-M opens a fresh module 3. paste the stuff in and close the VBE window If you save the workbook, the UDF will be saved with it. To use the UDF from the normal Excel window, just enter it like a normal Excel Function To remove the UDF: 1. bring up the VBE window as above 2. clear the code out 3. close the VBE window To learn more about UDFs, see: http://www.cpearson.com/excel/Writin...ionsInVBA.aspx Hi, Thanks for that, What's the reference to 'doit' in the VBA code? I'm using a Mac at the moment which seems to complain about this - it thinks it's an undeclared variable. However from memory I can't recall that as a keyword from my PC VBA days. I'll dig out an old PC shortly and try the UDF on that. Many thanks Richard *** I'm using an evaluation license of nemo since 82 days. You should really try it! http://www.malcom-mac.com/nemo |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect Function() - summing across sheets
On 02 Apr 2008 13:38:29 GMT, Richard wrote:
Thanks for the detailed response Ron, The first mentioned possible solution =SUMPRODUCT(N(INDIRECT("Sheet"&ROW(INDIRECT(A1&": "&A2))&"!"&ADDRESS(1, *2¨¨¨¨ Seems to work to a point, but unless I've misunderstood something, this appears to work with the VBA sheet names rather than the tab names. For instance in my test workbook, the sheets left to right have tab names of Sheet2, Sheet3, Sheet4, Sheet5 and Sheet9, however VBA (and presumably the Indirect function, knows these as Sheet2, Sheet4, Sheet9, Sheet5 and Sheet3, presumably because I've been changing the names and order whilst attempting to get to grips with this problem. i.e. the straightforward non indirect function adds up all 5 sheets because Sheet2 & Sheet5 are the first and last in the order, whereas the Indirect() function is only summing the first 4 sheets. Is there any modification I can make to have the ...Indirect() formula total the same as the non Indirect version? Thanks once more, Richard The INDIRECT function should be working on the actual names, not the VBA names. IT is also NOT constructing a 3D reference, but rather an array of individual references. The problem with your use of the first approach to your list, is that your sheets are not consecutively numbered, so when you construct your array using the ROW(INDIRECT(... function, you will wind up with some illegal references: E.G. A1: 2 A2: 9 {"Sheet2!$B$1";"Sheet3!$B$1";"Sheet4!$B$1";"Sheet5 !$B$1";"Sheet6!$B$1";"Sheet7!$B$1";"Sheet8!$B$1";" Sheet9!$B$1"} Since your actual (on the Excel Tab) names are NOT related by a simple numbering scheme, you could use my second method, where you list the sheet names individually in A1:An, and then refer to that range in the formula. EG: A1: Sheet2 A2: Sheet3 A3: Sheet4 A4: Sheet5 A5: Sheet9 Then use: =SUMPRODUCT(N(INDIRECT(A1:A5&"!"&ADDRESS(1,2)))) to sum all the B1's in those sheets. Or you may be able to develop the appropriate array differently. OR you may be able to number/name your sheets sequentially. --ron |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect Function() - summing across sheets
It is just a Boolean:
Dim doit as Boolean after the other dim statement -- Gary''s Student - gsnu2007g "Richard" wrote: In article Gary''sStudent wrote: Let's say that in A1 thru A3 we have: RGB5 RGB9 B2 Then first install the following UDF: Function addacross(r1 As Range, r2 As Range, r3 As Range) As Variant Application.Volatile Dim s1 As String, s2 As String, s3 As String s1 = r1.Value s2 = r2.Value s3 = r3.Value doit = False For i = 1 To Sheets.Count If Sheets(i).Name = s1 Then doit = True End If If doit Then addacross = addacross + Sheets(i).Range(s3).Value End If If Sheets(i).Name = s2 Then doit = False End If Next End Function Next, in an unused cell, enter: =addacross(A1,A2,A3) This should give the sum of the B2's in sheets RGB5 thru RGB9. UDFs are very easy to install and use: 1. ALT-F11 brings up the VBE window 2. ALT-I ALT-M opens a fresh module 3. paste the stuff in and close the VBE window If you save the workbook, the UDF will be saved with it. To use the UDF from the normal Excel window, just enter it like a normal Excel Function To remove the UDF: 1. bring up the VBE window as above 2. clear the code out 3. close the VBE window To learn more about UDFs, see: http://www.cpearson.com/excel/Writin...ionsInVBA.aspx Hi, Thanks for that, What's the reference to 'doit' in the VBA code? I'm using a Mac at the moment which seems to complain about this - it thinks it's an undeclared variable. However from memory I can't recall that as a keyword from my PC VBA days. I'll dig out an old PC shortly and try the UDF on that. Many thanks Richard ï*‡ï*‡ïŸ¼ I'm using an evaluation license of nemo since 82 days. You should really try it! http://www.malcom-mac.com/nemo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Indirect and Sum across Multiple Sheets | Excel Discussion (Misc queries) | |||
INDIRECT and multiple sheets | Excel Worksheet Functions | |||
indirect function within sumif to reference other sheets | Excel Worksheet Functions | |||
Summing a range using INDIRECT & ADDRESS | Excel Worksheet Functions | |||
Sum Indirect function through multiple sheets | Excel Discussion (Misc queries) |