selective freeze panes excel

edmacey
edmacey used Ask the Experts™
on
There might be an easier way of doing this but

I have an excel sheet with columns A and B frozen so they are visible when scroll right.

I have a text in row 1 merged across columns A to Z.

The document spans columns A to BZ

I would like the header text in row 1, the document title, to remain visible when scrolling to the right, i have tried putting it in a text box and selecting do not move with cells option but that doesn't seem to work either.

Is my question understandable? I can upload an excel sheet to demonstrate if wanted. Any help would be greatly appreciated.

Thanks Ed.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
If I understand correctly then unfreeze the panes and select cell B2, now freeze panes again .. try scrolling and is that what you want?

Chris

Author

Commented:
Dear Chris, I have attached an example excel sheet for demonstration. Ed.
demonstrationforexpertsexchange.xls
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
What do you want ... column a throug J to remain in view as well as row 1?

Chris
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
So just row 1, from column a to j to remain in view, or actually just that text to remain in view, in whatever way that is achievable. Ed.
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
Ah

I begin to understand.  I don't believe you can do that but I need to look further to validate that theory and try some alternatives.

Maybe someone else will have an answer in the meantime but I will respond later otherwise.

Chris

Author

Commented:
Cheers Chris, I await your response, hopefully it will be positive. Ed.
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
Sorry

Everything I try fails, I didn't think it possible and I haven't been able to 'picture' a work-around either.

Chris

Author

Commented:
Chris, just before I close this. Is there anyway to anchor a picture in excel so it doesn't move during freeze frame move. Thanks Ed.
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
edmacey

Sorry but not to my knowledge.  It might be possible to relocate it during a freeze frame move to appear stable, but I wouldn't want to try and do so myself.

Chris
Here's a different way to acheive what you are trying to do without freezing panes. Just use a form scrollbar
Scroll-Without-Freeze-Pane.xls

Author

Commented:
Dear Tiran,

This works great, really well, except is there a way for the information in the scrolled area not to be part of a different sheet, as it will be this information which will be edited. It could be edited from the other sheet, but my whole focus for trying to find the freezing not freezing certain bits was to make the input area look more attractive to the user.

Ed.
The whole effect of scrolling is really a trick. It relies on the cell formulas to display the information you want in a scrollable fashion. You can either place it elsewhere in the same spreadsheet or another idea is to lay them in rows and modify the formulas to transpose but it would really complicate your solution a lot. I would say your best bet (if you stay with this solution) is to have an easy to edit worksheet vs easy to view sheet. Otherwise you are back to relying on frozen panes etc which introduce a lot of other issues down the road.
another workaround is to use the statusbar to display the text by putting this macro in the workbook module

Private Sub Workbook_Open()
Application.StatusBar = Range("A1")
End Sub


Ofcourse you would lose the functionality of the status bar.

Saqib
Here is another workaround which I used to enjoy sometime ago. Not very elegant but it served the purpose.

Whenever the title  scrolls out you have to click on a cell to activate the selection_change event.

Saqib
demonstrationforexpertsexchange-.xls

Author

Commented:
I'm sure that this is achievable so please can you keep this open for the moment.
Ed,

What are your comments on my proposal?

Saqib

Author

Commented:
This doesn't achieve what was asked in the question but does offer a work around. It isn't the one i wanted but I think that that is unachievable. Many thanks ssaqibh. Ed.
Why did you grade this as "B" without commenting on it?

Author

Commented:
I did comment on it, see author comments:
This doesn't achieve what was asked in the question but does offer a work around. It isn't the one i wanted but I think that that is unachievable. Many thanks ssaqibh. Ed.
Sorry, my mistake. Did not see it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial