Hi guys.
view-tab-in-excel.png
I wanted to ask for help regarding a problem I have with the Freeze Panes option:
If I freeze a part of the sheet, when I change the data the whole screen freezes and I have to close the workbook.
Now, if I unfreeze the sheet again, it doesn't freeze.
This happens to me both in Excel-enabled spreadsheets (xlsm) and in Excel Sheets (xlsx).
Here's a video of the behavior:
https://www.dropbox.com/scl/fi/b283h...=1jv3oi15&dl=0
Thanks to all.
Can you please tell us exactly which version of 365 you are running with? Is it a beta (insider) build?
File > Account to find out.
Can you provide a sample workbook that displays this behaviour? Please also provide a recipe to reproduce the freeze (step-by-step) so that we can replicate exactly what you are doing. Suffice it to say that I can't reproduce it based on what you've said so far.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Attachment 875507
Here it is
That's a screenshot, not an Excel workbook.
Please excuse me, I hadn't read your message properly.
Here is the book. Anyway, take a look at the video I shared at the beginning, because in it you will see the behavior and how to reproduce it.
Thank you very much.
Cab't access the video as I don't have DropBox. I'll have a look at the workbook.
Please provide a recipe so that I can try to reproduce the issue.
Last edited by AliGW; 07-27-2024 at 08:24 AM.
https://wormhole.app/EnDbj#Yju6WXxP3bwQPG0JLaguYA
Here is the video and the recipe to reproduce it
The video won't play on my system - it's completely black.
I shall ask just once more: please provide a recipe (description).
1. Open Excel
2. Select cell G53
3. Change the cell value to a different number.
4. In my case, the screen freezes here and I have to close Excel because it won't let me continue doing anything.
5. If you got that error in point 4 and had to close Excel, open it again.
6. Select the View tab.
7. Go to the Zoom section.
8. Expand Freeze.
9. Select Unfreeze Panes.
10. Repeat steps 2 and 3 again.
11. If you got an error in point 4, then you'll see that it doesn't happen here.
Yes - I can replicate it at point 4. However, I cannot replicate it in any other workbook. It is possible that your workbook is becoming corrupted - have you tried copying the data to a NEW workbook and trying again?
Yes, I just did it, but the exact same thing happens to me.
Look, I'm attaching the file.
One question. Could you please send me the data in a new file? I wonder if it's something in my Excel. Who knows.
It doesn't happen in the file you have just attached. I changed G53 to 44 and nothing got frozen - no problem. I'm attaching the edited file.
Of course, I did NOT enable macros. So, try saving the workbook as an .xlsx file to switch off any VBA and see if it happens then. I am beginning to suspect some VBA code is the culprit.
And attached here s an .xlsx file.
Things I've tried, besides copying the files into a new xlsm workbook:
1. I've run the workbook through two online file repair tools.
2. I've also done what Office Help suggests: https://support.microsoft.com/en-us/...a-801ddcd4ea53
3. I've reviewed the code, which, as I said, doesn't matter because I've also tried it in xlsx (without macros) and the same thing happens, in case the Application.Goto Range("A1"), True instruction didn't add the final True.
Then, as you indicated, I moved the workbook to xlsx, and it gives the same problem.
I've also tried your two files and both give me the same problem.
What are my suspicions?
It seems that if I move the panels, it doesn't give an error.
The problem arises when they are IMMOBILIZED AND ALSO THERE IS A MATRIX FUNCTION LIKE THE ONE IN THE K:R COLUMNS (maybe this matrix function can be simplified and it stops giving the error, who knows, I don't know how to make it any simpler).
Thank you very much.
I am sure that your formulae could be simplified, but I don't know what they are supposed to be doing (and I am not going to try to reverse engineer them).
If you explain what the purpose is, someone can have a look, BUT I do not believe they are the issue - if they were, then I would be experiencing the same issue, and I'm just not with your latest attachment. This, to me, points at something else.
Hi, I have tried to remove the columns K:AC, and I have not had any problems entering values.
Therefore, I think the problem comes, as I said before, from combining matrix formulas with freezing panels.
I explain what I want to achieve in those columns K:AC:
Column K: join, without repeating, the names of the folders that are in columns A and F.
Column L: get the value O (organize) of column B corresponding to the folder that appears in column K.
Column M: get the value H (done) of column C corresponding to the folder that appears in column K.
Column N: get the value O (organize) of column G corresponding to the folder that appears in column K.
Column O: get the value H (done) of column H corresponding to the folder that appears in column K.
Column P: sum of columns L and N corresponding to the folder that appears in column K.
Column P: sum of columns L and N (value O -organize-) corresponding to the folder that appears in column K.
Column Q: sum of columns M and O (value H -done-) corresponding to the folder that appears in column K.
Column R: sum of columns P and Q (values O and H -organize and done-) corresponding to the folder that appears in column K.
Columns T:AC are intermediate calculations to obtain the summary table of columns K:R, and they can certainly be dispensed with.
Thanks a lot.
As I can't reproduce the issue, there's nothing I can do - sorry.
I also don't have any problem.
Have you tried it on an other computer?
Willem
English is not my native language sorry for errors
Please correct me if I'm completely wrong
For what it's worth, I can't reproduce the fault. I open the file. I can scroll up and down with the top rows frozen. I change cell G53. I can still scroll. I change cells G5 and G55. I can still scroll. I did not need to close and re-open the workbook.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Guys, the problem is with the formula I have in cell K4.
I just have to delete it and the problem will be solved.
Don't ask me why you don't have the problem and I do.
I've tried replacing Select columns with Index and VLookup with XLookup, but it doesn't work.
If there was someone who could give me an easier way to do it according to what I said in a previous post and which I transcribe here:
I've searched on Google and I can see the issue in the tables, which I should add @ to, but I have no idea how to do that.Originally Posted by zelarra821
Please, someone who can give me a hand to fix this.
Thank you very much.
In this file that I attached, I have moved the summary table to a separate sheet (Summary).
In this way, I get it to not give me any kind of problem.
Now, if in cell K4 of the Files sheet I put:
=Summary!J1#
That's where the problem comes in because the screen freezes.
I don't know if it's because I have the title and the data of the tables in the Files sheet in different panels.
I can't reproduce that problem either. Tested on an iPad.
What did you do ?
Last edited by zelarra821; 07-28-2024 at 01:52 PM.
I puy =Summary!J1# in cell K4 of the Files sheet. It spills across and down. Then I scrolled up and down. Posted the file back. Does it work for you?
No, I'm so sorry.
I need somebody reduce formula which I get summary table in columns K:R.
Fix it:
Don't ask me why, but tables and a formula referring to a dynamic range (for example, J1#) cannot be on the same sheet.
Please Login or Register to view this content.
Therefore, these dynamic ranges must be moved to a different sheet, and the data displayed through a UDF.
Thanks to all.
Glad you've fixed it, however this:
is not true per se.Don't ask me why, but tables and a formula referring to a dynamic range (for example, J1#) cannot be on the same sheet.
Dynamic array formulae cannot be used inside a structured table - true. This is not the issue here, though. Generally speaking, tables and formulae referring to dynamic ranges can coexist perfectly well on the same worksheet. I have many, many examples of this in the 100s of worksheets I have in my collection (mostly from helping with solutions to problems here).
My point is: this anomaly is peculiar to you and your set-up. But, as I said, I am glad that you have found a workaround.
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.
Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
Could you show one of them?I have many, many examples of this in the 100s of worksheets I have in my collection (mostly from helping with solutions to problems here)
DoneAlso, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
Thanks for the rep.
Ditto!!!
I could show you many, although I am not sure what that would prove other than that I'm telling the truth. Unfortunately, I am getting ready to go out, so don't have the time to do so now. Sorry.Could you show one of them?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks