Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
named range in sum formula (indirect, offset, worksheet name)
Hi,
I'm facing the following situation: - I've got named ranges using an offset function f.ex. Range1_Sheet1=offset(...), Range1_Sheet2=offset(...),etc pointing to a range of numbers. (The size and position of these range depend on the content of values in another column). - My worksheet names are part of the names of the ranges f.ex. Sheet1, Sheet2,... - I'm using a sum function to count the total value of the numbers in each of these ranges. So far no problem. But... - The point is that when I copy a sheet and change it's name, I would like the function to still work i.e sum the values of the range (I still have to define this separately) on the new sheet. So looking into this forum I found the mid(cell(...)) trick to get the worksheet name. This combined with sum(indirect()) gives: =SUM(INDIRECT("Range1_"&MID(CELL("filename",A1),FI ND("]",CELL("filename",A1))+1,255))) But this does not work. I think it is linked to the offset function in the range names. In debugging I discovered that it works when defining an range name without using offset. Is this a limitation of excel or am I overlooking something? Thanks, Hans |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
named range in sum formula (indirect, offset, worksheet name)
Why not use local worksheet names?
In each sheet create the same name Range1, and define it as you do but precede it with the sheet name, Sheet1!formula, Sheet2!formula etc, then it is just =SUM(Range1) on each sheet. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Hans" wrote in message ... Hi, I'm facing the following situation: - I've got named ranges using an offset function f.ex. Range1_Sheet1=offset(...), Range1_Sheet2=offset(...),etc pointing to a range of numbers. (The size and position of these range depend on the content of values in another column). - My worksheet names are part of the names of the ranges f.ex. Sheet1, Sheet2,... - I'm using a sum function to count the total value of the numbers in each of these ranges. So far no problem. But... - The point is that when I copy a sheet and change it's name, I would like the function to still work i.e sum the values of the range (I still have to define this separately) on the new sheet. So looking into this forum I found the mid(cell(...)) trick to get the worksheet name. This combined with sum(indirect()) gives: =SUM(INDIRECT("Range1_"&MID(CELL("filename",A1),FI ND("]",CELL("filename",A1))+1,255))) But this does not work. I think it is linked to the offset function in the range names. In debugging I discovered that it works when defining an range name without using offset. Is this a limitation of excel or am I overlooking something? Thanks, Hans |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
named range in sum formula (indirect, offset, worksheet name)
Hi Bob, I'm not following you:
- how do I create local worksheet names? The only way I know to define names is for the entire workbook (inser\name\define) - do you mean the input for the range should be in the form sheet1!offset(...)? This does not seem to work. I added some more details about the situation: Sheet Test1 column A column D 1 02:30 1 03:30 1 04:30 1 05:30 Cl 06:30 Cl 07:30 Cl 08:30 VP 09:30 VP 10:30 VP 11:30 VP 12:30 VP 13:30 VP 14:30 Sheet Test2 column A column D 1 02:30 1 05:30 Cl 06:30 Cl 07:30 Cl 08:30 Cl 09:30 Cl 10:30 VP 11:30 VP 12:30 VP 13:30 VP 14:30 I use the following formula's to define the ranges: Range1_Test1=OFFSET(Test1!$A$1,MATCH("1*",Test1!$B $1:$B$500,0)-1,4,(MATCH("CL*",Test1!$B$1:$B$500,0)-MATCH("1*",Test1!$B$1:$B$500,0)),1) Range1_Test2=OFFSET(Test2!$A$1,MATCH("1*",Test2!$B $1:$B$500,0)-1,4,(MATCH("CL*",Test2!$B$1:$B$500,0)-MATCH("1*",Test2!$B$1:$B$500,0)),1) And for the sumation in each sheet I would like to use something like this: SUM(INDIRECT("Range1_"&MID(CELL("filename",A1),FIN D("]",CELL("filename",A1))+1,255)&"1")) Thanks for any help! Hans "Bob Phillips" wrote: Why not use local worksheet names? In each sheet create the same name Range1, and define it as you do but precede it with the sheet name, Sheet1!formula, Sheet2!formula etc, then it is just =SUM(Range1) on each sheet. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Hans" wrote in message ... Hi, I'm facing the following situation: - I've got named ranges using an offset function f.ex. Range1_Sheet1=offset(...), Range1_Sheet2=offset(...),etc pointing to a range of numbers. (The size and position of these range depend on the content of values in another column). - My worksheet names are part of the names of the ranges f.ex. Sheet1, Sheet2,... - I'm using a sum function to count the total value of the numbers in each of these ranges. So far no problem. But... - The point is that when I copy a sheet and change it's name, I would like the function to still work i.e sum the values of the range (I still have to define this separately) on the new sheet. So looking into this forum I found the mid(cell(...)) trick to get the worksheet name. This combined with sum(indirect()) gives: =SUM(INDIRECT("Range1_"&MID(CELL("filename",A1),FI ND("]",CELL("filename",A1))+1,255))) But this does not work. I think it is linked to the offset function in the range names. In debugging I discovered that it works when defining an range name without using offset. Is this a limitation of excel or am I overlooking something? Thanks, Hans |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
named range in sum formula (indirect, offset, worksheet name)
Select the appropriate worksheet
Go to menu InsertNameDefine... In the Names box insert this_sheet_name!Range1 and in the RefersTo box insert =OFFSET(... Then onto the next sheet and repaet the exercise, and so on. Then on each sheet you just use =SUM(Range1) which you can copy from sheet to sheet. A bit better than INDIRECTs. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Hans" wrote in message ... Hi Bob, I'm not following you: - how do I create local worksheet names? The only way I know to define names is for the entire workbook (inser\name\define) - do you mean the input for the range should be in the form sheet1!offset(...)? This does not seem to work. I added some more details about the situation: Sheet Test1 column A column D 1 02:30 1 03:30 1 04:30 1 05:30 Cl 06:30 Cl 07:30 Cl 08:30 VP 09:30 VP 10:30 VP 11:30 VP 12:30 VP 13:30 VP 14:30 Sheet Test2 column A column D 1 02:30 1 05:30 Cl 06:30 Cl 07:30 Cl 08:30 Cl 09:30 Cl 10:30 VP 11:30 VP 12:30 VP 13:30 VP 14:30 I use the following formula's to define the ranges: Range1_Test1=OFFSET(Test1!$A$1,MATCH("1*",Test1!$B $1:$B$500,0)-1,4,(MATCH("CL*",Test1!$B$1:$B$500,0)-MATCH("1*",Test1!$B$1:$B$500,0)),1) Range1_Test2=OFFSET(Test2!$A$1,MATCH("1*",Test2!$B $1:$B$500,0)-1,4,(MATCH("CL*",Test2!$B$1:$B$500,0)-MATCH("1*",Test2!$B$1:$B$500,0)),1) And for the sumation in each sheet I would like to use something like this: SUM(INDIRECT("Range1_"&MID(CELL("filename",A1),FIN D("]",CELL("filename",A1))+1,255)&"1")) Thanks for any help! Hans "Bob Phillips" wrote: Why not use local worksheet names? In each sheet create the same name Range1, and define it as you do but precede it with the sheet name, Sheet1!formula, Sheet2!formula etc, then it is just =SUM(Range1) on each sheet. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Hans" wrote in message ... Hi, I'm facing the following situation: - I've got named ranges using an offset function f.ex. Range1_Sheet1=offset(...), Range1_Sheet2=offset(...),etc pointing to a range of numbers. (The size and position of these range depend on the content of values in another column). - My worksheet names are part of the names of the ranges f.ex. Sheet1, Sheet2,... - I'm using a sum function to count the total value of the numbers in each of these ranges. So far no problem. But... - The point is that when I copy a sheet and change it's name, I would like the function to still work i.e sum the values of the range (I still have to define this separately) on the new sheet. So looking into this forum I found the mid(cell(...)) trick to get the worksheet name. This combined with sum(indirect()) gives: =SUM(INDIRECT("Range1_"&MID(CELL("filename",A1),FI ND("]",CELL("filename",A1))+1,255))) But this does not work. I think it is linked to the offset function in the range names. In debugging I discovered that it works when defining an range name without using offset. Is this a limitation of excel or am I overlooking something? Thanks, Hans |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
named range in sum formula (indirect, offset, worksheet name)
sense and simplicity! thx for the help.
Hans "Bob Phillips" wrote: Select the appropriate worksheet Go to menu InsertNameDefine... In the Names box insert this_sheet_name!Range1 and in the RefersTo box insert =OFFSET(... Then onto the next sheet and repaet the exercise, and so on. Then on each sheet you just use =SUM(Range1) which you can copy from sheet to sheet. A bit better than INDIRECTs. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Hans" wrote in message ... Hi Bob, I'm not following you: - how do I create local worksheet names? The only way I know to define names is for the entire workbook (inser\name\define) - do you mean the input for the range should be in the form sheet1!offset(...)? This does not seem to work. I added some more details about the situation: Sheet Test1 column A column D 1 02:30 1 03:30 1 04:30 1 05:30 Cl 06:30 Cl 07:30 Cl 08:30 VP 09:30 VP 10:30 VP 11:30 VP 12:30 VP 13:30 VP 14:30 Sheet Test2 column A column D 1 02:30 1 05:30 Cl 06:30 Cl 07:30 Cl 08:30 Cl 09:30 Cl 10:30 VP 11:30 VP 12:30 VP 13:30 VP 14:30 I use the following formula's to define the ranges: Range1_Test1=OFFSET(Test1!$A$1,MATCH("1*",Test1!$B $1:$B$500,0)-1,4,(MATCH("CL*",Test1!$B$1:$B$500,0)-MATCH("1*",Test1!$B$1:$B$500,0)),1) Range1_Test2=OFFSET(Test2!$A$1,MATCH("1*",Test2!$B $1:$B$500,0)-1,4,(MATCH("CL*",Test2!$B$1:$B$500,0)-MATCH("1*",Test2!$B$1:$B$500,0)),1) And for the sumation in each sheet I would like to use something like this: SUM(INDIRECT("Range1_"&MID(CELL("filename",A1),FIN D("]",CELL("filename",A1))+1,255)&"1")) Thanks for any help! Hans "Bob Phillips" wrote: Why not use local worksheet names? In each sheet create the same name Range1, and define it as you do but precede it with the sheet name, Sheet1!formula, Sheet2!formula etc, then it is just =SUM(Range1) on each sheet. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Hans" wrote in message ... Hi, I'm facing the following situation: - I've got named ranges using an offset function f.ex. Range1_Sheet1=offset(...), Range1_Sheet2=offset(...),etc pointing to a range of numbers. (The size and position of these range depend on the content of values in another column). - My worksheet names are part of the names of the ranges f.ex. Sheet1, Sheet2,... - I'm using a sum function to count the total value of the numbers in each of these ranges. So far no problem. But... - The point is that when I copy a sheet and change it's name, I would like the function to still work i.e sum the values of the range (I still have to define this separately) on the new sheet. So looking into this forum I found the mid(cell(...)) trick to get the worksheet name. This combined with sum(indirect()) gives: =SUM(INDIRECT("Range1_"&MID(CELL("filename",A1),FI ND("]",CELL("filename",A1))+1,255))) But this does not work. I think it is linked to the offset function in the range names. In debugging I discovered that it works when defining an range name without using offset. Is this a limitation of excel or am I overlooking something? Thanks, Hans |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct Indirect Named Dynamic Range using Offset | Excel Worksheet Functions | |||
Indirect to Named range | Excel Worksheet Functions | |||
named range row offset | Excel Worksheet Functions | |||
Dynamic named range & Offset | Excel Discussion (Misc queries) | |||
named range, offset self-reference | Excel Discussion (Misc queries) |