User Profile
DouglasWilsonCAgmail
Copper Contributor
Joined Feb 02, 2023
User Widgets
Recent Discussions
Copying entire Column then inserting copied cells (column) takes 10-15 minutes to complete???
Hello. I have a spreadsheet with a column or two that I need to shuffle around. This column is largely empty from a data perspective. It does have a number of formulas and conditional formatting in place between rows 12 to 1500 but only approx. 18-19 rows with any kind of external data entered. When I attempt to copy a column from top row to last row then "insert copied cells" in between two other columns, it can take from 0-15 minutes...you read that right folks. 10-15 minutes to complete that task. I have pulled up my Task Mgr. to watch what is happening from a hardware perspective. Before initiating this action my CPU is 8-10% and RAM sits at 650MB-700MB out of 16Gb of total ram and no meaningful programs open and actively affecting either category. Once I initiate the action I described, the CPU moves up a bit but not anywhere above 40%. The RAM usage typically goes as high as 6,500MB with the highest level at or around 10,500MB-11,600MB or around 88%-92% of available RAM. Once the task completes the RAM continues at this level for up to an additional 10-15 mins or longer to go back to the original 650MB-700MB range. Other far smaller sheets don't experience this issue and generally my computer runs cleanly. It started doing all this with this spreadsheet (probably file specific causes at play here) around 3 days ago for no apparent reason. No massive data input or additional formulas or conditional formatting added (but a small number of cells did have these attributes added during this past 3 days which is pretty much always the case as this sheet evolves). I have included a small capture of the file details but will wait to upload the entire workbook until someone lets me know they want to take a look at this issue and need the file to do so. I do have backups of the current file but unfortunately not sequentially named so as to preserve the last known good working file. Just copies of the file with this problem... I appreciate your assistance in advance! DouglasNeed to use COUNTIFS with OFFSET and COUNTA to create a dynamic range within COUNTIFS
I am using the following formula, =COUNTIFS($D$7:$D$68,L9, $F$7:$F$68,">0") within the attached sheet. The two ranges need to be dynamic as thousands of rows will be added in time as my data grows. I have used the combination of OFFSET and COUNTA to accomplish this with a Data Validation List but am not sure of the proper syntax to implement it within the COUNTIFS formula above. Or, maybe there is another solution? Thanks for your help in advance!Problems with Excel 365 sheet, COUNTIFS formula...see attached sheet with narrative about my issue
I would need to attach a sample sheet in order to adequately explain the issue at hand. I don't see a paperclip here. Let's start out by giving me instructions on how to attach an Excel sheet please. Update...It would seem I am not able to upload the functioning spreadsheet so here is a .jpg. Hopefully you will understand what I am looking to accomplish. Thanks in advance!
