A couple other alternatives (that I haven't used) are: 1. Write VBA UDF macros to summarize all the ranges you want, or to return a more flexible 3-D array that you can use with built-in functions or array formulae
2. Use the Morefunc addin (raved about in online forums, I haven't tried it) which among other things implements more 3-D functions to help you summarize your 3-D range, or to return a single array from a 3D range that you can then use with many built-in functions and array formulae 3. Supposedly you can use the Indirect function with an array of sheets and ranges to create a 3D array, with certain limitations and quirks. However, examples I saw online do not work in Excel 2010.. it may work in earlier versions. The author of Morefunc's site seems to be down at the moment, but you can find the addin at http://download.cnet.com/Morefunc/3000-2077_4-10423159.html. The author's site listed it as compatible through Excel 2007, but reportedly it also work with Excel 2010 but you may need an added setup step (add references to Morefunc.xll and Morefunc12.xlam - see http://www.mrexcel.com/forum/showthread.php?t=467397). Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Asa Rossoff Sent: Friday, May 11, 2012 8:18 PM To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ Defining Same Name For ranges available in multiple tabs Hi Shekhar, You can use: =SUMIF(sheet1!WBS,A1,sheet1!COST)+SUMIF(sheet2!WBS,A1,sheet2!COST)+SUMIF(she et3!WBS,A1,sheet3!COST) Or, if the ranges are identical on each sheet, you can use 3-D references with certain functions (but not with SUMIF) to refer to basically a "union" of the identical ranges on a range of sheets, in the form: sheet1:sheet3!A2:A100 You can't use sheet1:sheet3!WBS, but you can make a new name for your 3-D reference if it uses a range reference (non-named). For more on 3-D references see https://office.microsoft.com/en-us/excel-help/create-a-3-d-reference-to-the- same-cell-range-on-multiple-worksheets-HP010102346.aspx. Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Shekhar Sharma Sent: Friday, May 11, 2012 2:59 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Defining Same Name For ranges available in multiple tabs I'm wondering if it's possible to define one name for columns in multiple worksheet. Basically I'm working on an estimating spreadsheet with multiple sheets; each sheet contains hundreds of individual costs (separate lines) which are categorized into more generic numerical buckets. So each line has a column for the generic buck and the cost. I'd like to have a summary sheet with a column summarizing these numerical buckets and the total cost for each bucket which is added from each sheet. Let's say the generic bucket column in each tab is called WBS, and the cost column is called COST. In my summary tab, I'd like a formula such as =SUMIF(WBS,A1,COST)...but of course this doesn't work. Any ideas on how I can solve this? -- Shekhar Sharma "Life consists not in holding good cards but in playing those you hold well" "Before you judge another, think of your own last mistake." -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com
