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
