transaction sheet in source is slow

jwenga

Hi Vidas,
Any reason why the transaction sheet in the source workbook would become slow/sluggish? I'm unable to scroll on that particular sheet. All other sheets in the source workbook scroll/move as expected.
Thanks,
Joe

Trenzalore

The first place I'd look is at the conditional formatting rules.
1. select a cell inside the transactions table
2. Home--> Conditional Formatting

You should only see about 5 or 6 rules in there, if you see more, then you need to clean them up. I had hundreds due to copying and pasting at one point and it dragged down the response time of the sheet dramatically. I regularly check that out now.

It should look similar to this:

jwenga

That would appear to be it. How to determine which rules to keep? Mimic a newly downloaded demo sheet?

jwenga

This is what the demo rules look like in the demo files that you can download from the site when starting new.

gsbaker

There appears to be a reference error in these Conditional Formatting rules (in the 9th line/rule):



This same "#REF!" error seems to exist in both the Demo and Empty versions of the PS3.0.0 and 3.1.0 Source files. I am not sure how significant this error is - but something that Vidas may want to investigate??

jwenga

I'm feeling pretty silly right now. I have inadvertently turned on freeze panes. Uggghhh, thanks for your help. These are my rules now after deleting some trying to figure out my isse. Does anyone see an issue with my formatting rules?

gsbaker

The rules you show appear to be the same as the first 4 rules in Vidas' original files. The only issue that I see is that the rules you have NOT included, appear to be designed to highlight cells that may represent error conditions.

By not having those rules it may be more difficult to identify errors in the Source file that may be the cause of unusual or erroneous results in your Reports file.

jwenga

Since I already deleted them, is there any way to get them back or recreate them?

Trenzalore

you can just create a new rule and insert it into the sheet. Download a fresh file from the web site, then look at the conditional formatting of the file you just downloaded and manually duplicate them into your current working file. You'll have to edit each rule to look at the details. That's option one.

Option two would be use the fresh copy as your new working copy. I went this route b/c I had so many conditional formatting rules that it wasn't worth spending the time deleting them one by one. Just remember that you don't want to copy paste or you will copy the formatting to the new sheet/file; you want to copy and "paste values". And remember, that it's not just transactions to copy, you'll have to copy data from each sheet (i.e. accounts, symbols, etc.).

Both options are a bit tedious.

Before you do either, make sure your sheet is responsive now that you whittled it down to just four rules. You don't want to go through the trouble if your sheet still super slow until you figure that out.

gsbaker

Some additional thoughts on @Trenzalore post. If you decide to start over from a fresh copy of the source file:

1. When you paste, as @Trenzalore indicates, make sure you "paste values" so as to not re-paste the cell formatting (and conditional formats)
   
2. Make sure you are only copying and pasting data from columns that do NOT contain formulae (e.g. Columns A - O in the Transactions tab. Otherwise you will overwrite the formulae in those columns with text if you "paste values" as noted above.
   

jwenga

Thanks Trenzalore and gsbaker - I ended up going with a new workbook and I'm back to zero issues. Have a good day...

VidasMatelis

Glad the issue is resolved with the help from other community members!

gsbaker

@VidasMatelis, you may want to take a quick look at the conditional formatting in the Transactions tab of the Source file, as there appears to be a "#REF!" in line/rule #9. See my post above from Sept 22.