Range question in '97
Can you have sheet specific named ranges?
ie sheet1!game, sheet2!game, sheet3!game so they can be referenced in a lookup, pulling values from the correct sheet. thanks |
Range question in '97
Yes. Select sheet1,
InsertNameDefine... then in the 'Names in workbook' put Sheet1!game, then add the range in the Refersto box. Repeat for sheet2, etc. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message ... Can you have sheet specific named ranges? ie sheet1!game, sheet2!game, sheet3!game so they can be referenced in a lookup, pulling values from the correct sheet. thanks |
Range question in '97
a further clarification, can you name a range of cells say A1:K22 game, and
then specify sheet1!game, sheet2!game without haveing to name then on sheet1 and sthhet2 and sheet3...? "Bob Phillips" wrote in message ... Yes. Select sheet1, InsertNameDefine... then in the 'Names in workbook' put Sheet1!game, then add the range in the Refersto box. Repeat for sheet2, etc. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message ... Can you have sheet specific named ranges? ie sheet1!game, sheet2!game, sheet3!game so they can be referenced in a lookup, pulling values from the correct sheet. thanks |
Range question in '97
Adam
Try this.......... On sheet1 InsertNameDefine. Type in "globalgame"(no quotes) In refers to box type in =!$A$1:$K$22 and OK your way out. Go to any sheet and F5. In the reference: dialog Type in "globalgame"(no quotes) and OK. Gord Dibben Excel MVP On Sat, 10 Dec 2005 14:16:21 -0600, "Adam Kroger" wrote: a further clarification, can you name a range of cells say A1:K22 game, and then specify sheet1!game, sheet2!game without haveing to name then on sheet1 and sthhet2 and sheet3...? "Bob Phillips" wrote in message .. . Yes. Select sheet1, InsertNameDefine... then in the 'Names in workbook' put Sheet1!game, then add the range in the Refersto box. Repeat for sheet2, etc. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message ... Can you have sheet specific named ranges? ie sheet1!game, sheet2!game, sheet3!game so they can be referenced in a lookup, pulling values from the correct sheet. thanks |
Range question in '97
You can do that if you plan ahead, while you're creating your WB.
Select a range in Sheet1. Click in the name box and type: sheet1!rng1 Then <Enter When you click in the name box again, all you see is: rng1 Now, hold down <Ctrl, and click in the tab of Sheet1, and drag right until the small arrow is on the right side of the tab, and then release the mouse first, and you have copied your sheet. This new sheet has the same sheet specific range (rng1) automatically configured to it. Rename this sheet. So, create your sheet specific ranges on your first sheet, and any sheet copied from it will contain the same ranges, each specific to the individual newly copied sheets. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message ... a further clarification, can you name a range of cells say A1:K22 game, and then specify sheet1!game, sheet2!game without haveing to name then on sheet1 and sthhet2 and sheet3...? "Bob Phillips" wrote in message ... Yes. Select sheet1, InsertNameDefine... then in the 'Names in workbook' put Sheet1!game, then add the range in the Refersto box. Repeat for sheet2, etc. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message ... Can you have sheet specific named ranges? ie sheet1!game, sheet2!game, sheet3!game so they can be referenced in a lookup, pulling values from the correct sheet. thanks |
Range question in '97
Works perfect
Thanks "Gord Dibben" <gorddibbATshawDOTca@ wrote in message ... Adam Try this.......... On sheet1 InsertNameDefine. Type in "globalgame"(no quotes) In refers to box type in =!$A$1:$K$22 and OK your way out. Go to any sheet and F5. In the reference: dialog Type in "globalgame"(no quotes) and OK. Gord Dibben Excel MVP On Sat, 10 Dec 2005 14:16:21 -0600, "Adam Kroger" wrote: a further clarification, can you name a range of cells say A1:K22 game, and then specify sheet1!game, sheet2!game without haveing to name then on sheet1 and sthhet2 and sheet3...? "Bob Phillips" wrote in message . .. Yes. Select sheet1, InsertNameDefine... then in the 'Names in workbook' put Sheet1!game, then add the range in the Refersto box. Repeat for sheet2, etc. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message ... Can you have sheet specific named ranges? ie sheet1!game, sheet2!game, sheet3!game so they can be referenced in a lookup, pulling values from the correct sheet. thanks |
Range question in '97
Works well within the active sheet, but I can not figure out how to
reference it from another sheet. EXAMPLE active sheet is pilot2 rn1hp refers to !$C$5 I want to refer to r1hp on sheet1 =sheet1!rn1hp returns #NAME? and brings up a file dialog box & =INDIRECT(sheet1!rn1hp) returns #REF! and changes the formula to ='TESTBED - Combat Tracker v3.0.4.xls'!rn1hp thanks "Gord Dibben" <gorddibbATshawDOTca@ wrote in message ... Adam Try this.......... On sheet1 InsertNameDefine. Type in "globalgame"(no quotes) In refers to box type in =!$A$1:$K$22 and OK your way out. Go to any sheet and F5. In the reference: dialog Type in "globalgame"(no quotes) and OK. Gord Dibben Excel MVP On Sat, 10 Dec 2005 14:16:21 -0600, "Adam Kroger" wrote: a further clarification, can you name a range of cells say A1:K22 game, and then specify sheet1!game, sheet2!game without haveing to name then on sheet1 and sthhet2 and sheet3...? "Bob Phillips" wrote in message . .. Yes. Select sheet1, InsertNameDefine... then in the 'Names in workbook' put Sheet1!game, then add the range in the Refersto box. Repeat for sheet2, etc. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message ... Can you have sheet specific named ranges? ie sheet1!game, sheet2!game, sheet3!game so they can be referenced in a lookup, pulling values from the correct sheet. thanks |
Range question in '97
Adam
Apologies. Misunderstood I thought you wanted rn1hp to refer to same cell $C$5 on each sheet. To reference Sheet1!rn1hp from another sheet, you have to make the name sheet-specific when you create the name. You're back to adding the refers to: =sheet1!$C$5 as you create the name. Gord On Sun, 11 Dec 2005 04:03:02 -0600, "Adam Kroger" wrote: Works well within the active sheet, but I can not figure out how to reference it from another sheet. EXAMPLE active sheet is pilot2 rn1hp refers to !$C$5 I want to refer to r1hp on sheet1 =sheet1!rn1hp returns #NAME? and brings up a file dialog box & =INDIRECT(sheet1!rn1hp) returns #REF! and changes the formula to ='TESTBED - Combat Tracker v3.0.4.xls'!rn1hp thanks "Gord Dibben" <gorddibbATshawDOTca@ wrote in message .. . Adam Try this.......... On sheet1 InsertNameDefine. Type in "globalgame"(no quotes) In refers to box type in =!$A$1:$K$22 and OK your way out. Go to any sheet and F5. In the reference: dialog Type in "globalgame"(no quotes) and OK. Gord Dibben Excel MVP On Sat, 10 Dec 2005 14:16:21 -0600, "Adam Kroger" wrote: a further clarification, can you name a range of cells say A1:K22 game, and then specify sheet1!game, sheet2!game without haveing to name then on sheet1 and sthhet2 and sheet3...? "Bob Phillips" wrote in message .. . Yes. Select sheet1, InsertNameDefine... then in the 'Names in workbook' put Sheet1!game, then add the range in the Refersto box. Repeat for sheet2, etc. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message ... Can you have sheet specific named ranges? ie sheet1!game, sheet2!game, sheet3!game so they can be referenced in a lookup, pulling values from the correct sheet. thanks |
Range question in '97
I want both, I'm greedy :)
I have 12 sheets in my workbook that are identicle but track a different individual, each sheet has a repeated section. Best analogy I can think of, unless you are familiar with wargames, is tracking salesmen. 12 salesman each have their own sheet, and each sheet tracks the information for a salesman across 12 months, broken down by weeks. There are identical calculations for each week refering to cells that are in the same relative position on each sheet, and section. A 13th sheet is set up with sumary information for the entire year, broken down by week, a cover sheet with anual summary information, and "probably" a pivot table sheet (once I figure out how to do that; there will probably be several questions posted when I come to it.) Basically, I am working on a spreadsheet that is beyond my abilities, but that is the only way to learn :) "Gord Dibben" <gorddibbATshawDOTca@ wrote in message ... Adam Apologies. Misunderstood I thought you wanted rn1hp to refer to same cell $C$5 on each sheet. To reference Sheet1!rn1hp from another sheet, you have to make the name sheet-specific when you create the name. You're back to adding the refers to: =sheet1!$C$5 as you create the name. Gord On Sun, 11 Dec 2005 04:03:02 -0600, "Adam Kroger" wrote: Works well within the active sheet, but I can not figure out how to reference it from another sheet. EXAMPLE active sheet is pilot2 rn1hp refers to !$C$5 I want to refer to r1hp on sheet1 =sheet1!rn1hp returns #NAME? and brings up a file dialog box & =INDIRECT(sheet1!rn1hp) returns #REF! and changes the formula to ='TESTBED - Combat Tracker v3.0.4.xls'!rn1hp thanks "Gord Dibben" <gorddibbATshawDOTca@ wrote in message . .. Adam Try this.......... On sheet1 InsertNameDefine. Type in "globalgame"(no quotes) In refers to box type in =!$A$1:$K$22 and OK your way out. Go to any sheet and F5. In the reference: dialog Type in "globalgame"(no quotes) and OK. Gord Dibben Excel MVP On Sat, 10 Dec 2005 14:16:21 -0600, "Adam Kroger" wrote: a further clarification, can you name a range of cells say A1:K22 game, and then specify sheet1!game, sheet2!game without haveing to name then on sheet1 and sthhet2 and sheet3...? "Bob Phillips" wrote in message . .. Yes. Select sheet1, InsertNameDefine... then in the 'Names in workbook' put Sheet1!game, then add the range in the Refersto box. Repeat for sheet2, etc. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message ... Can you have sheet specific named ranges? ie sheet1!game, sheet2!game, sheet3!game so they can be referenced in a lookup, pulling values from the correct sheet. thanks |
Range question in '97
I figured it out. :)
if I name them (the ranges) like this =$A$1 instead of =!$A$1 then I can reference them by '<sheet'!<range_name I guess excel does a literal substitution and as a result the two !'s were confusing it. bubba assigned to !$A$1 'sheet 1'!bubba gets translated to 'sheet 1'!!$A$1 so excel starts looking for a spreadsheet named "sheet 1" I guess. It seems to make some sort of logical sense; to me.... This opens up so many possibilities for me. I should be able to eliminate several hundred VLOOKUP() formulas from my workbook. THANK YOU for pointing me in the right direction. "Gord Dibben" <gorddibbATshawDOTca@ wrote in message ... Adam Apologies. Misunderstood I thought you wanted rn1hp to refer to same cell $C$5 on each sheet. To reference Sheet1!rn1hp from another sheet, you have to make the name sheet-specific when you create the name. You're back to adding the refers to: =sheet1!$C$5 as you create the name. Gord On Sun, 11 Dec 2005 04:03:02 -0600, "Adam Kroger" wrote: Works well within the active sheet, but I can not figure out how to reference it from another sheet. EXAMPLE active sheet is pilot2 rn1hp refers to !$C$5 I want to refer to r1hp on sheet1 =sheet1!rn1hp returns #NAME? and brings up a file dialog box & =INDIRECT(sheet1!rn1hp) returns #REF! and changes the formula to ='TESTBED - Combat Tracker v3.0.4.xls'!rn1hp thanks "Gord Dibben" <gorddibbATshawDOTca@ wrote in message . .. Adam Try this.......... On sheet1 InsertNameDefine. Type in "globalgame"(no quotes) In refers to box type in =!$A$1:$K$22 and OK your way out. Go to any sheet and F5. In the reference: dialog Type in "globalgame"(no quotes) and OK. Gord Dibben Excel MVP On Sat, 10 Dec 2005 14:16:21 -0600, "Adam Kroger" wrote: a further clarification, can you name a range of cells say A1:K22 game, and then specify sheet1!game, sheet2!game without haveing to name then on sheet1 and sthhet2 and sheet3...? "Bob Phillips" wrote in message . .. Yes. Select sheet1, InsertNameDefine... then in the 'Names in workbook' put Sheet1!game, then add the range in the Refersto box. Repeat for sheet2, etc. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message ... Can you have sheet specific named ranges? ie sheet1!game, sheet2!game, sheet3!game so they can be referenced in a lookup, pulling values from the correct sheet. thanks |
Range question in '97
OOPS!
spoke too soon. I thought it was working, but it is not... apparently when I take the ! out of the address when I name it, XL appends the sheet address to it..... I am back to square 1. !HELP! "Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message ... I figured it out. :) if I name them (the ranges) like this =$A$1 instead of =!$A$1 then I can reference them by '<sheet'!<range_name I guess excel does a literal substitution and as a result the two !'s were confusing it. bubba assigned to !$A$1 'sheet 1'!bubba gets translated to 'sheet 1'!!$A$1 so excel starts looking for a spreadsheet named "sheet 1" I guess. It seems to make some sort of logical sense; to me.... This opens up so many possibilities for me. I should be able to eliminate several hundred VLOOKUP() formulas from my workbook. THANK YOU for pointing me in the right direction. "Gord Dibben" <gorddibbATshawDOTca@ wrote in message ... Adam Apologies. Misunderstood I thought you wanted rn1hp to refer to same cell $C$5 on each sheet. To reference Sheet1!rn1hp from another sheet, you have to make the name sheet-specific when you create the name. You're back to adding the refers to: =sheet1!$C$5 as you create the name. Gord On Sun, 11 Dec 2005 04:03:02 -0600, "Adam Kroger" wrote: Works well within the active sheet, but I can not figure out how to reference it from another sheet. EXAMPLE active sheet is pilot2 rn1hp refers to !$C$5 I want to refer to r1hp on sheet1 =sheet1!rn1hp returns #NAME? and brings up a file dialog box & =INDIRECT(sheet1!rn1hp) returns #REF! and changes the formula to ='TESTBED - Combat Tracker v3.0.4.xls'!rn1hp thanks "Gord Dibben" <gorddibbATshawDOTca@ wrote in message ... Adam Try this.......... On sheet1 InsertNameDefine. Type in "globalgame"(no quotes) In refers to box type in =!$A$1:$K$22 and OK your way out. Go to any sheet and F5. In the reference: dialog Type in "globalgame"(no quotes) and OK. Gord Dibben Excel MVP On Sat, 10 Dec 2005 14:16:21 -0600, "Adam Kroger" wrote: a further clarification, can you name a range of cells say A1:K22 game, and then specify sheet1!game, sheet2!game without haveing to name then on sheet1 and sthhet2 and sheet3...? "Bob Phillips" wrote in message ... Yes. Select sheet1, InsertNameDefine... then in the 'Names in workbook' put Sheet1!game, then add the range in the Refersto box. Repeat for sheet2, etc. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message ... Can you have sheet specific named ranges? ie sheet1!game, sheet2!game, sheet3!game so they can be referenced in a lookup, pulling values from the correct sheet. thanks |
All times are GMT +1. The time now is 06:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com