Beginner Question - Sheets I'm editing become inactive until I click another sheet or workbook (Intermittent)

[Conor26667]

Hi there, everyone!

I just registered to the forum to ask about something that has been mildly inconvenient for a few months now. I'm a bit of a beginner, but I've committed to getting better at using Excel going forward (including starting a training course). So, here's what I've got:

I created a workbook to track a potential stock trading strategy over time. It has 2 main components: prices at various intervals and percentage changes relative those prices. This workbook has 5 sheets that I use, each of which shows basically the same information in a different way. All the sheets use formulas to calculate the percentage changes and a hypothetical account value (as if I were trading an account in real life). Based on the values in each cell, I color code them either light green or light red.

The problem is that while I'm editing any of these sheets, the sheet will suddenly not allow any more editing. When it happens I can't type in any cell, change a cell's color or other properties, scroll using the scrollwheel, use the keyboard to navigate (arrow keys, tab, enter, etc), or copy and paste anything. I can click on any cell and highlight it, and scroll using the the scroll bars, but I can't seem to do much else. However, as soon as I click any cell in another sheet or even a different workbook, it unlocks the sheet I was working on and I can go back to editing as if nothing had happened. Sometimes, this will fix it for quite a while, other times it will only fix it for 1 or 2 edits. Once it starts, it seems to get worse (more frequent) the more editing I try to do. At it's worst, I'll edit a cell and then have to go into another sheet each time I want to edit another cell. This is a big issue when I've got to go down the line across multiple sheets editing 20 or 30 cells (changing sheets between each edit). I'm not sure what is causing this to happen and could use some guidance on how to prevent it from happening in the future. It generally happens everyday, several times a day (worse in the afternoon as I keep the workbook open from about 8:30am to 5:30pm). Could I just be leaving it open too long? Are there just too many formulas in the workbook? Is there a simple setting I can change?

Any assistance is appreciated.

 

[JLGWhiz]

I am guessing, but you probably have volatile formulas set up which calculate during your edits. Excel, unlike VBA, does not have error messages or alerts for memory cache problems, so it could be that your editing is causing escessive memory usage and Excel just shuts down to avoid a catastrophic crash. When you go to a different sheet, it allows Excel to dump the temporary memory stored for the sheet you were editing and you can return to it and continue editin. As I said, I am guessing at the problem because I have no idea how much data you are dealing with or what formulas you might be using. But it is one scenario that could result in the symptoms described.

 

[Conor26667]

I thought that there could be an issue with that. However, I don't think there's really that much data being processed. Once I apply the formula to a given cell, I thought it was just set. I'm not *usually* making changes to the cells that it looks at for the calculation. I have a lot of cells that have been calculated for months without changes. Is excel still trying to constantly calculate these cells each time I enter the particular sheet? Currently, the file shows as being only 913KB. I don't know exactly how much that really means as it's just how much it is when saved to the hard drive. I assume it takes up more temporary data when it's in use, but I really don't know on that. In the grand scheme of things, I know there are many people working with much larger data sets with millions of entries. Whether they are heavy on the formulas or not, I don't know though.

 

[JLGWhiz]

It is a little difficult to troubleshoot from afar for that type of problem. If you have IT specialists in your company, it might be a good idea to contact them and ask for assistance. They can probably take a look at your formulas, etc. and isolate the problem faster than can be done by remote analysis.

 

[Conor26667]

My company's IT guy (singular) is at the corporate office in another state. And this is more of a personal project that I don't want anyone to know I'm working on. It doesn't take a huge amount of time, but it's probably best that I keep it to myself. I've been wanting to pick his brain on it, but I'm hesitant to open that door for now.

 

[Michael M]

your not running any macros are you....especially worksheet events ??

 

[Conor26667]

No. I am not really familiar with macros beyond the term. It's on my list of things to learn in the near future, but I'm not quite there yet.

 

[Michael M]

If you copy tha file to another location, onto a laptop (off server) for instance, do you still have the same problem.

 

[JLGWhiz]

You can also get a general idea of how many formulas you have and what types they are by clicking 'Show Formulas' under 'Formulas' on the ribbon. If your sheet is loaded with formulas and lots of them use Index, Now, Today, Offset and Indirect, then those are memory eaters that can be self feeding when you start editing. If that is the case, you would need to rething your formula strategy and reduce the number of volatile functions in use, or modify them so they only fire under explicit conditions. To view your data again, jjust click the 'Show Formulars' icon again.

 

[glfiedler]

Perhaps simply setting calculations to manual, perform all your editing tasks, then setting calculations back to automatic might do the trick.