Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Using Name as Worksheet Reference
I have a defined name that holds the names of each relevant worksheet I want to calculate. Lets Say I want to sum every A1 in each worksheet in the defined name. How would I go about doing that? -- malik641 ------------------------------------------------------------------------ malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190 View this thread: http://www.excelforum.com/showthread...hreadid=401807 |
#2
|
|||
|
|||
"malik641" wrote in
message ... I have a defined name that holds the names of each relevant worksheet I want to calculate. Do you mean a Collection of worksheets you want to calculate? ------------------------------------- Dim i, SumOfA1Cells as Currency For Each i In NameOfSheets SumOfA1Cells = SumOfA1Cells + Val(Sheets(i).[a1]) Next ------------------------------------- Lets Say I want to sum every A1 in each worksheet in the defined name. How would I go about doing that? Or, for any worksheet in active workbook: -------------------------------------------- Dim i, SumOfA1Cells As Currency For Each i In ThisWorkbook.Worksheets SumOfA1Cells = SumOfA1Cells + Sheets(i.Name).[a1] Next -------------------------------------------- Bruno |
#3
|
|||
|
|||
"Bruno Campanini" wrote in message
... Ooops! That's better: For Each i In NameOfSheets SumOfA1Cells = SumOfA1Cells + Val(Sheets(i.Name).[a1]) Next Bruno |
#4
|
|||
|
|||
Hi!
Try this: J1 = Sheet1 J2 = Sheet3 J3 = Sheet10 J1:J3 is given the defined name SheetNames =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1"))) This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1 Biff "malik641" wrote in message ... I have a defined name that holds the names of each relevant worksheet I want to calculate. Lets Say I want to sum every A1 in each worksheet in the defined name. How would I go about doing that? -- malik641 ------------------------------------------------------------------------ malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190 View this thread: http://www.excelforum.com/showthread...hreadid=401807 |
#5
|
|||
|
|||
Biff Wrote: Hi! Try this: J1 = Sheet1 J2 = Sheet3 J3 = Sheet10 J1:J3 is given the defined name SheetNames =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1"))) This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1 Biff I'm getting a #Name? error with this. I checked it out and the error comes from this: ""&SheetNames&" I have NO clue how this worksheet function is supposed to work. Especially the N before the INDIRECT function. Can you explain that a little bit?? Thanks -- malik641 ------------------------------------------------------------------------ malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190 View this thread: http://www.excelforum.com/showthread...hreadid=401807 |
#6
|
|||
|
|||
You need to give the range a name (insertnamedefine) as per your
instructions "J1:J3 is given the defined name SheetNames" or use the range as in =SUMPRODUCT(N(INDIRECT("'"&J1:J3&"'!A1"))) -- Regards, Peo Sjoblom (No private emails please) "malik641" wrote in message ... Biff Wrote: Hi! Try this: J1 = Sheet1 J2 = Sheet3 J3 = Sheet10 J1:J3 is given the defined name SheetNames =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1"))) This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1 Biff I'm getting a #Name? error with this. I checked it out and the error comes from this: ""&SheetNames&" I have NO clue how this worksheet function is supposed to work. Especially the N before the INDIRECT function. Can you explain that a little bit?? Thanks -- malik641 ------------------------------------------------------------------------ malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190 View this thread: http://www.excelforum.com/showthread...hreadid=401807 |
#7
|
|||
|
|||
Hi!
Peo explained the #NAME? problem. As far as N goes....... I don't know the exact reason it's needed. It's one of those quirky type things you just run into. It usually comes into play when you're trying to deal with 3D references. For some reason if you just used: =SUMPRODUCT(INDIRECT("'"&SheetNames&"'!A1")) The arguments to INDIRECT will return #VALUE! errors. Using N (in this case) causes the arguments to actually return their true values. The N() function is used for numeric values and the T() function is used for text values. If you only wanted to sum the same cell on 3 different sheets I would just use: =SUM(Sheet1!A1,Sheet3!A1,Sheet10!A1) The SUMPRODUCT method is good if you have many sheets to sum. Biff "malik641" wrote in message ... Biff Wrote: Hi! Try this: J1 = Sheet1 J2 = Sheet3 J3 = Sheet10 J1:J3 is given the defined name SheetNames =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1"))) This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1 Biff I'm getting a #Name? error with this. I checked it out and the error comes from this: ""&SheetNames&" I have NO clue how this worksheet function is supposed to work. Especially the N before the INDIRECT function. Can you explain that a little bit?? Thanks -- malik641 ------------------------------------------------------------------------ malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190 View this thread: http://www.excelforum.com/showthread...hreadid=401807 |
#8
|
|||
|
|||
"Biff" wrote...
.... As far as N goes....... I don't know the exact reason it's needed. It's one of those quirky type things you just run into. It usually comes into play when you're trying to deal with 3D references. .... It's not 3D referencing. Calling INDIRECT or OFFSET with array arguments results in those functions returning something that behaves like an array of range references. There are very few other functions that can cope with such beasts, certainly not SUMPRODUCT. N and T functions convert these into arrays of numbers or strings, which SUMPRODUCT can handle. |
#9
|
|||
|
|||
Awesome, got it! Now I need to figure out how to use this with certain criteria...hmmmm But anyway thanks again Biff and Peo. Bruno, thanks for the VBA but I kinda wanted to avoid using a VBA Function. Guess I should have specified. Sorry -- malik641 ------------------------------------------------------------------------ malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190 View this thread: http://www.excelforum.com/showthread...hreadid=401807 |
#10
|
|||
|
|||
Hi!
Now I need to figure out how to use this with certain criteria Depends on what you want to do. You might be able to use something like: =SUMPRODUCT(SUMIF(................................ .. Biff "malik641" wrote in message ... Awesome, got it! Now I need to figure out how to use this with certain criteria...hmmmm But anyway thanks again Biff and Peo. Bruno, thanks for the VBA but I kinda wanted to avoid using a VBA Function. Guess I should have specified. Sorry -- malik641 ------------------------------------------------------------------------ malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190 View this thread: http://www.excelforum.com/showthread...hreadid=401807 |
#11
|
|||
|
|||
Biff Wrote: Hi! Now I need to figure out how to use this with certain criteria Depends on what you want to do. You might be able to use something like: =SUMPRODUCT(SUMIF(................................ .. Biff "malik641" wrote in message ... Awesome, got it! Now I need to figure out how to use this with certain criteria...hmmmm But anyway thanks again Biff and Peo. Bruno, thanks for the VBA but I kinda wanted to avoid using a VBA Function. Guess I should have specified. Sorry -- malik641 ------------------------------------------------------------------------ malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190 View this thread: http://www.excelforum.com/showthread...hreadid=401807 Two things: 1st Off, what the hell's wrong with this thread? Why is it repeating itself a rediculous amount of times????? 2nd, The SUMIF function is not working for me. I'm looking for something along the lines of: Sheet1 C1:2 D1:Yes Sheet2 C1:2 D1:Yes Sheet3 C1:2 D1:No Here is the formula that I have to add every C1 in worksheets from Range "Employees" (Defined Name): =SUMPRODUCT(N(INDIRECT("'"&Employees&"'!C1"))) How can I sum the worksheets from Range "Employees"C1 where D1=Yes??? -- malik641 ------------------------------------------------------------------------ malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190 View this thread: http://www.excelforum.com/showthread...hreadid=401807 |
#12
|
|||
|
|||
Okay, I figured that part out. =SUMPRODUCT((T(INDIRECT("'"&Employees&"'!D1"))="Ye s")*(N(INDIRECT("'"&Employees&"'!C1")))) NOW what my REAL criteria is: B2=1-Jan A4=Hitachi 717 Formula is in B4. What I'm looking for from this is to sum the values in each "Employees" sheet in the column that equals 1-Jan (from B2) and in the row that equals "Hitachi 717". Here's what I came up with, but it's not working. =SUMPRODUCT((N(INDIRECT("'"&Employees&"'!$1:$1))=B $2)*(T(INDIRECT("'"&Employees&"'!$A:$A"))=$A4)) In every "Employees" sheet in row 1:1 is the Date value. And in Column A:A in every "Employees" sheet is where "Hitachi 717" would be found. Where these two will intersect is what I want to sum. Any ideas?? -- malik641 ------------------------------------------------------------------------ malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190 View this thread: http://www.excelforum.com/showthread...hreadid=401807 |
#13
|
|||
|
|||
malik641 wrote...
.... NOW what my REAL criteria is: B2=1-Jan A4=Hitachi 717 Formula is in B4. What I'm looking for from this is to sum the values in each "Employees" sheet in the column that equals 1-Jan (from B2) and in the row that equals "Hitachi 717". Here's what I came up with, but it's not working. =SUMPRODUCT((N(INDIRECT("'"&Employees&"'!$1:$1))= B$2)*(T(INDIRECT("'"&Employees&"'!$A:$A"))=$A4)) In every "Employees" sheet in row 1:1 is the Date value. And in Column A:A in every "Employees" sheet is where "Hitachi 717" would be found. Where these two will intersect is what I want to sum. Any ideas?? You can't do this if your A4 value could be anywhere in col A in the other worksheets *AND* your B2 value could be anywhere in row 1 in the other worksheets. If that were the case, you'd need a 3D array or some means of isolating the appropriate column in each worksheet separately. Neither are possible. However, if all the employee worksheets would have the same row 1, so 1-Jan would be in the same column in every employee worksheet, you could use =SUMPRODUCT(SUMIF(INDIRECT("'"&Employees&"'!A:A"), $A4, INDIRECT("'"&Employees&"'!C"& MATCH(B$2,INDIRECT("'"&INDEX(Employees,1)&"'!1:1") ,0),0))) |
#14
|
|||
|
|||
I think I'm just going to do a macro function. I'll be better off this way. -- malik641 ------------------------------------------------------------------------ malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190 View this thread: http://www.excelforum.com/showthread...hreadid=401807 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet reference behaving funny | Excel Discussion (Misc queries) | |||
Cell Reference Math | Excel Worksheet Functions | |||
Worksheet reference (i.e placing worksheet name in a cell) | Excel Worksheet Functions | |||
Indirect reference from one worksheet to another | Excel Worksheet Functions | |||
Reference a cell to get worksheet name | Excel Worksheet Functions |