Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Mixed sheet refs (2000+ ?)

    It won't be the first time I was asleep at the wheel. I'm almost too embarrassed to ask.

    I just noticed that if I create a formula, say on Sheet1, that refers to cells on, say, Sheet2 and Sheet1 (by clicking the sheet tabs and the cells of interest) that the formula fills in all sheet names. For example:
    =Sheet2!C1+Sheet1!B2

    But if this formula was all within Sheet1, then it would simply be
    =C1+B2

    I could swear that in some version of Excel that a formula with offsheet refs would NOT fill in the Sheet names for onsheet cells. I see for 2000 and XP that I'm wrong. Did this get changed somewhere along the line and I just missed it?

    Fred

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

    Re: Mixed sheet refs (2000+ ?)

    I don't remember how it was in Excel 97 or before, but in Excel 2002 (XP), it depends on the order of the cells. Say that you are on Sheet1. If you start the formula with a cell on Sheet1, the result will be

    =B1+Sheet2!C1

    but if you start with a cell on Sheet2, the result will be as in your post:

    =Sheet2!C1+Sheet1!B1

    If you think about it, it is not unreasonable that Excel handles it this way. You can edit the formula to look either way, both forms are valid.

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Mixed sheet refs (2000+ ?)

    Hans,

    Maybe I just always started with the onsheet ref. Editing to remove the onsheet sheet name is probably easy using Replace; I don't think it would be as easy to add in the names.

    I also ask in case there's a max number of characters that one can have in a formula.

    Fred

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

    Re: Mixed sheet refs (2000+ ?)

    I think the maximum length of the text of a formula is still 1,024 characters.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Mixed sheet refs (2000+ ?)

    Just to confirm, this is how it is in XL97. Once you start pointing to a different sheet in a formula, XL will continue even on the same sheet.

    Steve

Posting Permissions

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