Results 1 to 8 of 8
  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

    New Window with freeze pane/pain (Excel 2003)

    Hi All,

    While I rarely use the feature of having 2 windows for the same spreadsheet open at once under Windows, I expected the feature to work a certain way. It doesn't seem that hard to use.

    I encountered what I thought was a bug but maybe this is by design. Thought I'd ask about others' experience.

    The workbook has a number of sheets. Let me focus on 2 of them: attendance and labs. On the attendance sheet, there are about 35 columns, including col A for students' names. On the labs sheet, there are about 25 columns, including col A for students' names. Col A is frozen so that I can scroll to the right and enter attendance or lab scores, as the semester progresses, while keeping the names (Col A) on screen.

    If people are absent on a day a lab is due, they get a 0 for the lab.

    I marked the attendance sheet. I decided to open a new window so I could view the attendance and lab sheets at the same time - the idea being that I'd look at the attendance sheet in one window and see who was absent so as to record the 0 (we had a lot of absences yesterday!).

    When I scrolled right, say on the lab sheet, in window:2 (the new window), the freeze had been lost; but scrolling right on the same lab sheet in window:1 still had the freeze. If I closed window:1, the freeze was still gone in the only window that used to be window:2. Save, close and re-open the file, and the freeze is still gone. On the other hand, if I closed window:2, the freeze was still present in what is now the only window (the former window:1).

    Same is true regardless of what sheets I have open in the windows - freeze is gone in window:2, regardless if they're the same or different sheets in the 2 windows .

    I think this behavior is odd. I would think if the original workbook has a freeze on a sheet, it should still be there if I look at that sheet in window:1 or window:2. Is there some rational explanation for why things work this way (ie, it's a design feature) or is this a bug?

    TIA

    Fred

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Fred,

    I believe, and my testing seems to confirm, that the Freeze Panes is a Window setting not a Sheet setting. So if you look at a sheet with Freeze Panes in a new window that window does not have its Panes frozen.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    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
    The behavior you describe is exactly what I would expect based on my experiences with excel. the reasoning is as RetiredGeek has indicated: FreezePanes is a property of the Windows object, not the sheet, so when you create a new window, no panes have been added. Each window can have its own panes, allowing you to look at 2 different sections of the same worksheet, with different panes...

    Steve

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

    OK. First I'm not looking for verification that things work as you see them working based on experience with Excel - they always work as you see them working. But whether that's correct behavior or not, from what people would think should happen, is a different issue. If someone "creates" a set of requirements but the product works differently (whether bcs the programmer didn't understand the requirements or did but made a programming mistake) or meets those requirements but gives unexpected and "bothersome" results (bad requirements?), you still see it's working as it is. The question is what should the program be doing.

    For example, I once had a situation (used to work in the telecomm industry) where a single person set his 2 phones (different numbers) to forward unanswered calls to each other and probably thought nothing of it. Guess what happened when the person was not at either phone. How to get around that is a different issue but it sure looked like some kind of bug in the network until we found what was going on.

    I'm not naive enough to think that if a bunch of us agree that this feature should work differently that we'll have an MS guarantee that it will start to work that way in the next release. In the absence of that absurd wish, I'd just like to know how to change my behavior so I get the results I want/expect and not be surprised/puzzled by what is actually happening (maybe to the point of thinking it must be me).

    In this case, to be certain we're talking the same thing, I open Excel by double clicking a file. Excel opens with some sheet as active. I then click on the Window menu (2003) and ask for a "new window." In that new window, I change sheets. In the "original" window numbered :1, the freeze is effective while in :2 it is not (same or different sheet as in :1, and I have numerous sheets in this file where I've frozen for the names in Col A). I wasn't sure if you were talking about 2 instances of Excel, where I would definitely expect the freeze to be active on whatever sheet I was looking at in each window (and I've verified).

    So I think what you're both saying is that there is a benefit to having one window keep any freeze for any sheet that already had it (which would make sense to be the :1 window) and the other window(s) not, so in the case you're looking at the SAME sheet in >1 window that you can explore in the :2, :3, ... window(s) while keeping things frozen in the :1 window. I can live with that as a "useful" benefit. But remember, the menu item is "New Window." When I change the contents of a cell in one window, I do see it in the other window (because I'm dealing with the cell object's property of contents and that's independent of the window/pane).

    Even when I'm on different sheets in the 2 windows, I can see a case for allowing a sheet to keep the freeze while the other has no freezes unless you explicitly put on a freeze (not that it's so hard although, having taught Excel, some people have trouble knowing what cell to make active in order to accomplish a particular goal of scrolling/freezing). But I also would think that having a freeze in :1 but not in :2 is not so intuitive. Certainly in my case I want to keep Col A frozen so I can see students who were absent and record their 0 for the lab - certainly as the semester progresses and the proper col is not within the first few near Col A.

    I guess given the above, which seems to have some benefit, I now have learned that to operate in "my way" that I have to freeze the sheet in the :2 window. Not that it's so hard to do. But I just discovered what could be a useful feature, if it hasn't already been done by someone. That is, to have a little toolbar (or maybe use the status area on the bottom) to give you the current Freeze status of the active worksheet, which could differ based on the window/pane even for the same sheet. I know that Excel puts lines to separate the frozen/unfrozen areas, but my sheet is formatted to have borders drawn in exactly the same place so Excel's visual aid is lost to me.

    Or I could go back to what I must have always done in the past: use the attendance piece of paper that I've marked in class to drive the entry of the absence on the Attendance sheet and then switch to the Labs sheet and use that same piece of paper to record the 0. Or develop a better memory to be able to remember the 10 absent people to be able to enter their 0's.

    So I'll say I've learned something new here.

    I would offer an analogy with another Ecel feature: protection. Cells are protected by default. But you can still enter things into them until you Protect the sheet (barring little exceptions). I've also found this hard for people to understand.

    And if I freeze a sheet in the :2 window and then close the file (with both windows still open), Excel does not ask me if I want to save the file because of this change in "structure." When I re-open the file, both windows are there but the :2 sheet is not frozen (it's lost). If I freeze a sheet in the :1 window, same result - no query about saving changes and re-opening results in both windows with no freezes. One might question this behavior.

    Which brings me to my final point/question (finally!): what exactly is a pane that I'm freezing? And if there is something called a "pane," how is that I'm "opening a window" and "arranging windows" but "freezing a pane?" Surely when I use the "freeze pane" feature, I'm only affecting the active sheet. Does the Windows object's freeze pane property just mean that some sheet has a freeze but we're not telling you which one? Not sure I see the huge benefit of that property. The other sheets in the :1 or :2 windows don't have a freeze. Maybe my confusion is what this property of the Windows object is all about (which I can now thank you guys for adding to the almost infinite number of things that confuse me ).

    Thanks guys. Just felt like ranting a little.

    Fred
    Last edited by fburg; 2011-10-08 at 21:16.

  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
    In this case, to be certain we're talking the same thing, I open Excel by double clicking a file. Excel opens with some sheet as active. I then click on the Window menu (2003) and ask for a "new window." In that new window, I change sheets. In the "original" window numbered :1, the freeze is effective while in :2 it is not (same or different sheet as in :1, and I have numerous sheets in this file where I've frozen for the names in Col A). I wasn't sure if you were talking about 2 instances of Excel, where I would definitely expect the freeze to be active on whatever sheet I was looking at in each window (and I've verified).
    I was speaking of this behaviour. Essentially creating a new window into a sheet does not yet have any freeze panes on it (but they can be added).

    So I think what you're both saying is that there is a benefit to having one window keep any freeze for any sheet that already had it (which would make sense to be the :1 window) and the other window(s) not, so in the case you're looking at the SAME sheet in >1 window that you can explore in the :2, :3, ... window(s) while keeping things frozen in the :1 window. I can live with that as a "useful" benefit.
    How "useful" is debatable, it is possible to do it and and I can think of times when I would use it (freeze different parts of a sheet in different windows).

    But I also would think that having a freeze in :1 but not in :2 is not so intuitive. Certainly in my case I want to keep Col A frozen so I can see students who were absent and record their 0 for the lab - certainly as the semester progresses and the proper col is not within the first few near Col A.
    There are many things in XL I believe I would have programmed differently. I would have defaulted the new window to start with the current freeze panes. I agree that makes more sense, and I find it "annoying" at times that it does not. But I live with many annoyances in my life

    I guess given the above, which seems to have some benefit, I now have learned that to operate in "my way" that I have to freeze the sheet in the :2 window. Not that it's so hard to do. But I just discovered what could be a useful feature, if it hasn't already been done by someone. That is, to have a little toolbar (or maybe use the status area on the bottom) to give you the current Freeze status of the active worksheet, which could differ based on the window/pane even for the same sheet. I know that Excel puts lines to separate the frozen/unfrozen areas, but my sheet is formatted to have borders drawn in exactly the same place so Excel's visual aid is lost to me.

    Or I could go back to what I must have always done in the past: use the attendance piece of paper that I've marked in class to drive the entry of the absence on the Attendance sheet and then switch to the Labs sheet and use that same piece of paper to record the 0. Or develop a better memory to be able to remember the 10 absent people to be able to enter their 0's.
    Another option would seem to be to keep them both on the same sheet or have the lab sheet somehow lookup the entry. There are many ways to design a spreadsheet [You may even consider creating a database application, which may be more appropriate in something like ACCESS...]

    I would offer an analogy with another Ecel feature: protection. Cells are protected by default. But you can still enter things into them until you Protect the sheet (barring little exceptions). I've also found this hard for people to understand.
    I have found it hard for people to understand it as well. But this feature pre-dates excel. If I recall Lotus 123 worked similarly, so it has been this way for decades...

    And if I freeze a sheet in the :2 window and then close the file (with both windows still open), Excel does not ask me if I want to save the file because of this change in "structure." When I re-open the file, both windows are there but the :2 sheet is not frozen (it's lost). If I freeze a sheet in the :1 window, same result - no query about saving changes and re-opening results in both windows with no freezes. One might question this behavior.
    The behavior I would immediate question is why closing a workbook without saving it? Have you tried saving it before closing it and then re-opening it? If you do, I would expect the freeze panes to remain. Again, annoying that excel did not prompt you to save changes, and I would agree, I think it should register freezing panes as a change, but it does not. I find it annoying that volatile functions like TODAY(), and NOW(), trigger a save request even though nothing but the value has changed in the cells, but one can't have everything

    Which brings me to my final point/question (finally!): what exactly is a pane that I'm freezing?
    A pane is a portion of the windows on the worksheet. It is called that since when done a the worksheet can have a the look of a window pane...

    And if there is something called a "pane," how is that I'm "opening a window" and "arranging windows" but "freezing a pane?" Surely when I use the "freeze pane" feature, I'm only affecting the active sheet.
    Yes the activesheet in the activewindow. The "New Window" my be what is confusing to you. New Window means a new view of the current workbook. But anytime you change the active worksheet you are changing the window. It is just a view of the spreadsheet. The worksheets are objects in both the Workbook object and the Windows object. Some things are windows properties and some are workbook properties.

    Does the Windows object's freeze pane property just mean that some sheet has a freeze but we're not telling you which one?
    No the Freezepane property sets the freeze in the activewindow which contains a worksheet (the active worksheet). The distinction is between the one workbook but multiple views (windows) of that workbook so you can see the multiple worksheets. Only activesheets have freezepane information available: the freezepane does not exist for non-visible worksheets, which creates the behavior we are discussing. So if you have multiple windows in a workbook, how any sheet is "paned' will be determined by which window you are looking at it through...

    Not sure I see the huge benefit of that property. The other sheets in the :1 or :2 windows don't have a freeze.
    Yes only the activesheet (or activewindow) has that property. But XL remembers for each sheet in each window what the "scrollColumn" and "scrollrow" is for each sheet so that when that sheet is visible the appropriate pane will be created. Part of this behavior seems to stem from a decision to allow a worksheet to have different panes in different views. If it were a worksheet property, a particular sheet could only have one freeze pane (or split view) available. But since it is part of the windows property, any sheet can have as many freeze panes as there are windows...

    Does that make it a little clearer?

    Steve

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    I would offer an analogy with another Excel feature: protection. Cells are protected by default. But you can still enter things into them until you Protect the sheet (barring little exceptions). I've also found this hard for people to understand.
    When I was still teaching I found the best way to explain this behavior was to liken a cell to a door with an electronic lock installed {default}. While the electric is off {UnProtect Sheet} I can open the door {change the value in the cell}. If I turn the electric on {Protect Sheet} I can no longer open the door {change the cell}. If I want to have some cells always available for changed I need to uninstall the Lock {turn protection for the cell off} before turning the electricity on. I found most students understood the feature after this explanation and a few demonstrations.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    Thanks for staying with me on this.

    As said, once you understand what's happening, you learn to live with it - as much as you may not like it. And that's certainly the point I'm at now with this feature. So I think I'm good now with panes and windows, etc.

    You may even consider creating a database application, which may be more appropriate in something like ACCESS...]
    I agree that a DB approach may be better. Lots of advantages (adding students after the initial roster right before the beginning of the semester being a huge benefit of a DB approach - I have a student coming in now, 4 weeks into a 15-week semester). In fact, the school has an automated online grading system - I attended a class for it about a month ago. It had some benefits over my "system" but also some disadvantages. Problem is that I do a lot more work in Excel than in Access, so there'd also be a "re-learning" curve for Access.

    Have you tried saving it before closing it and then re-opening it?
    Yes - if I put a freeze in :2 and explicitly saved; upon reopen, the freeze is still there in the :2 window.

    Again, annoying that excel did not prompt you to save changes, and I would agree, I think it should register freezing panes as a change, but it does not.
    I think that if you protect/unprotect a workbook (didn't try worksheet) and go to close, you do get prompted to save. Sometimes I'm lazy and use the close button as a way of prompting the save. There is some rationale about not saving the workbook if I didn't make a change (eg, comparing latest version on hard disk vs backup).

    A pane is a portion of the windows on the worksheet. It is called that since when done a the worksheet can have a the look of a window pane...
    Not sure I follow this definition. I think it may have been late for both of us. "A portion of the window" [singular] or "windows" [a subset of ...]. I don't get the window pane analogy in terms of the Excel feature looking like one. I never thought of it that way. Then again, my mother always had me washing the windows in our apartment, so maybe I developed an aversion to anything Windows.

    Another option would seem to be to keep them both on the same sheet or have the lab sheet somehow lookup the entry
    In addition to the attendance and lab sheets, I have sheets for quizzes, tests, homework, projects, grading summaries and special computations (a consolidated "DB" sheet that feeds Word to create reports for students on how they're doing). So we'd be talking about 125 cols on a sheet (rows are students). I realize it's not that much but more than my mind would handle. I could probably do something with grouping so that the cols for "quizzes section" of the sheet open/close via the grouping +/-. But that's a radical redesign; may as well try Access. But I agree, lots of ways to do a design.


    Of course, this was all precipitated by the fact that 10 students were absent on Friday (there's the usual 3 or 4 who haven't attended class since the beginning of the semester but another 6 - I guess they didn't want to get their tests back) and my inability to remember that many things.

    Fred

  8. #8
    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
    In addition to the attendance and lab sheets, I have sheets for quizzes, tests, homework, projects, grading summaries and special computations (a consolidated "DB" sheet that feeds Word to create reports for students on how they're doing). So we'd be talking about 125 cols on a sheet (rows are students).
    Another way in Excel may be less columns and more rows, so the summaries can be output directly in a pivot table. Each row instead of being a person would be a piece of information: Name, date, type of info (quiz, test, homework, etc) and score. You can also use autofilter to display the sheet in particular ways for output or entry...

    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
  •