Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    3d reference (XP)

    Never knew you could define a name referring to a cell for inclusion in a formula on multiple sheets until this morning. Printed the instructions from the Excel help results -
    this is how it reads:

    This is also called a 3-D reference (3-D reference: A reference to a range that spans two or more worksheets in a workbook.).

    On the Insert menu, point to Name, and then click Define.
    In the Names in workbook box, type the name.
    If the Refers to box contains a reference, select the equal sign (=) and the reference and press BACKSPACE.
    In the Refers to box, type = (equal sign).
    Click the tab for the first worksheet to be referenced.
    Hold down SHIFT and click the tab for the last worksheet to be referenced.
    Select the cell or range of cells to be referenced.

    I've tried several times (I've got 3 worksheets for which I wish to name the same cell on each sheet the same name) - maybe I'm misunderstanding what the result will be - sure expected to see the name I assigned to the cell appear in the name box when I make the newly-named cell the active cell - nope.

    Help me again, please.

    AuntLinda

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 3d reference (XP)

    A 3D name refers to more than one sheet. Therefore, the name is *not* shown in the name box when you select a cell, since that cell is in only one sheet. For the same reason, you'll get an error if you select Edit | Go To and type the range name in the Reference box.

    You can use a 3D name in a formula. For example, if the name Test refers to Sheet1:Sheet3!A1, the formula

    =SUM(Test)

    is equivalent to

    =SUM(Sheet1:Sheet3!A1)

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 3d reference (XP)

    Jan Karel,

    That's an amazing trick - I've never seen it before!

    It works very well when I enter a new formula in a cell. However, if I edit an existing formula and type a ? or * in a sheet reference, Excel expands the formula the way you describe, then crashes immediately. This is in Excel 2002 SP-3 (Dutch).

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 3d reference (XP)

    This is for both of you, Hans and Jan - Cool!

    Thanks again for saving me.

    AuntLinda

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 3d reference (XP)

    Yes, I heard about some instabilities.
    Cool trick indeed. Not very useful, but cool anyway.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 3d reference (XP)

    FYI: Excel 2003 does not crash on editing an existing formula using this trick.

    I see that Excel XP behaves slightly different from 2003 when entering the formula: XP replaces the wildcard sheet ref as you're typing, Excel 2003 waits with doing that until you hit enter.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Quick way to enter 3d reference (Excel)

    Did you know this one:

    Type in a cell:

    =SUM('Sheet?'!A1)

    and hit enter

    If your workbook contains
    Sheet1
    Sheet2
    Sheet3

    This is what Excel does with the formula:

    =SUM(Sheet1:Sheet3!A1)

    Neat eh?

    You can use other wildcard combinations in there too!

    ###EDIT###

    Note: Excel XP crashes on editing existing formulas using this. Excel 2003 behaves itself.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Quick way to enter 3d reference (Excel)

    Hi Jan Karel

    I tried this in Excel 2003 and got different results in each sheet, Please see attached


    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Quick way to enter 3d reference (Excel)

    Excel excludes the sheet on which you are entering the formula from the result. So you should preferably enter the formula on a sheet whose name does not fit the pattern. For example, if you enter
    <code>
    =SUM(Data?!A1)
    </code>
    it works best if the name of the sheet on which you are entering this does not begin with Data.

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Quick way to enter 3d reference (Excel)

    Hi Hans

    Thanks for the explanation.

    Braddy
    If you are a fool at forty, you will always be a fool

  11. #11
    New Lounger
    Join Date
    Oct 2005
    Location
    West Jordan, Utah, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quick way to enter 3d reference (Excel)

    I have a question that is sort of related the original poster of this thread. I created a sheet with all of the cells I wanted to be named, named, and then held ctrl down and clicked and dragged the sheet to make a copy, all the cells that were named on the first sheet showed the same name in the name box, but if I went in and tried to name the same cell individually on each sheet, it only allowed me to do one and then it reverted back to the original. Can you or anyone you know tell me why this happens?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Quick way to enter 3d reference (Excel)

    When you name a range, the name is by default global to the workbook. You cannot define the same global name twice.
    When you copy a sheet containing named ranges, there is a conflict, since a global name can be used only once. Excel solves this by making the names in the copied sheet local to that sheet. You cannot define the same local name twice on the same sheet, but you can define the same name as a local name on different sheets.
    A global name is indicated by its name only, e.g. Profit.
    A local name is indicate by the sheet name and the range name separated by an exclamation mark, e.g. Sheet1!Profit.
    Confusing? Use Jan Karel Pieterse's free Name Manager add-in - it lets you view and manage defined names in a much more comprehensive way than the built-in dialog.

  13. #13
    New Lounger
    Join Date
    Oct 2005
    Location
    West Jordan, Utah, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quick way to enter 3d reference (Excel)

    Thanks. In waiting for a response, I found a thread done by Jan Karel Pieterse that explained it all. Thanks again for the response.

  14. #14
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 3d reference (XP)

    Thanks! Excellent article - just as you said.

    You have a great weekend and, again - I need all the help I can get!

    Aunt Linda

  15. #15
    New Lounger
    Join Date
    Oct 2005
    Location
    West Jordan, Utah, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 3d reference (XP)

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    AuntLInda,

    If you happen to ever check this again, or anyone else for that matter, I found out by pieterse's web page, http://www.jkp-ads.com/Articles/ExcelNames00.htm, on how to name the same cell on multiple sheets. Go to this website and she/he does a very good job of explaining how to do it.

Page 1 of 2 12 LastLast

Posting Permissions

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