Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Names applicable to several sheets? (2000 SR-1)

    Hi.

    I've got a multi-sheet workbook, containing an undefined number of irregularly named sheets, where each sheet is identically laid out. I want to create a formula which pulls together various bits of information. Obviously I could do this using cell addresses very simply, but because of where I want to use it, I would like to have some visibility in the formulae I use, so I was going to use named ranges.

    As far as I can tell, however, named ranges only apply to specific sheets. So if I create a name for A1 on sheet1, "Fred", say, then it always refers to Sheet1!A1, even if I use it on sheet3. What I would like to do is to have a named range, such as "BoughtValue" which refers to a cell on the current sheet, whatever that happens to be. The number of sheets is variable, so I don't really want to have to have to define a new ranges and then amend all the formulae every time I create a new sheet.

    Is this possible at all?

    Regards,

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Names applicable to several sheets? (2000 SR-1)

    It should be possible without too much bother. Firstly you can have a range named BoughtValue on each sheet, provided that when defining the name you make sure that SheetName is included in the referes to box, e.g. ='Sheet2'!$A$1.

    You cann create a new worksheet template consisting of a workbook with a single sheet saved as Sheet.xlt in your XLStart directory. If you define a range name in that sheet it should be included in all new sheets you insert into your workbook.

    When you reference BoughtValue on a sheet it defaults to the range name in that sheet unless the reference includes the sheetname of another sheet.

    Hope the above is of some help.

    Andrew C

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Names applicable to several sheets? (2000 SR-1)

    Andrew,

    Not sure I follow.

    If I go into Insert | Name | Define and enter a name such as "BoughtValue" then the sheet name is inserted for me. If I then go to another sheet and enter "BoughtValue" the same way, then I end up changing the one that I've already entered. I can't enter two of the same name. Am I missing something?

    I also don't want to use a template in startup because I only want the formatting etc to apply to one particular workbook - if I did this, I'd have my ranges in every spreadsheet I write. What I really want is to be able to copy a sheet and then just rename it within this one workbook, and then have everything work.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Names applicable to several sheets? (2000 SR-1)

    Copying the sheet with the name in place is probably the best way to go. If you do copy a sheet with range names, the names should copy to the new sheet as distinct names in that sheet. Try it and see.

    Andrew

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Names applicable to several sheets? (2000 SR-1)

    When you go to Insert|Name|Define, the Sheet name is already inserted in the Refers To formula, NOT in the name. If you type the Name as Sheet1!MyCells, then you can have a variable name MyCells on each sheet.
    Legare Coleman

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Names applicable to several sheets? (2000 SR-1)

    <P ID="edit" class=small>Edited by WebGenii on 28-Aug-01 09:18.</P>edited to include link - WebGenii
    Stuart,

    I found the following article on range names most useful, you might like to have a browse,

    <A target="_blank" HREF=http://www.elementkjournals.com/ime/9806/ime9861.htm>http://www.elementkjournals.com/ime/9806/ime9861.htm</A>

    Regards,

    Peter Moran

  7. #7
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Names applicable to several sheets? (2000 SR-1)

    Aha! Now I see it! Microsofty mist clears!

    Thanks Legare & Andrew!

    Regards,

  8. #8
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Names applicable to several sheets? (2000 SR-1)

    Peter,

    Interesting article, thanks. The solution I was looking for was the one above, but this was still extremely interesting - I particularly liked the indirect named range that looked at the cell immediately above the one that called it!

    Regards,

  9. #9
    New Lounger
    Join Date
    Mar 2001
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Names applicable to several sheets? (2000 SR-1)

    Thank a million.
    Not knowing how to create duplicate named ranges through the UI has bugged me for 8 years. Whew!

    Lee Embrey

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Names applicable to several sheets? (2000 SR-1)

    I may be missing the point (I frequently do, and maybe my suggestion is in that URL), but for what you originally asked, wouldn't a 3D range be useful? For example ='Sheet2:Sheet4'!$D$10 is a valid range to name "mythreedrange", so =SUM(mythreedrange) is a valid expression.
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Names applicable to several sheets? (2000 SR-1)

    It wasn't a 3D range that I was originally looking for.

    I was setting up a workbook where I wanted to have one sheet for each of an indeterminate number of people. The sheet name would be the person's name, so there was going to be no consistency in the sheet naming convention that I could build into a formula. My "total" cells were also, depending upon a number of user-selections, going to be totalling a different number of entries, so that the total formula would be in different places on each sheet.

    So, I wanted to have a named range which pointed to a total cell, but I wanted it to be the same name for every sheet, but only pointing to that sheet. Then in my summary, I would put the person's name in column B3 (say) and a formula like: =indirect(B3 & "!" & ThisTotal) in C3, which I could then copy down. Each sheet would have its own "ThisTotal" range.

    The answer suggested earlier, of naming the ranges "FredSmith!ThisTotal", "JimBrown!ThisTotal", etc, worked perfectly, and I've happily used this technique in other workbooks since.

    Regards,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •