And that’s why Microsoft should never be in the car business.
or worse the Airplane business
Major solution - removed SUMIFs referencing a linked workbook.
I didn’t do it! I fixed it!
We had to manually import some large data tables to the workbook, and now it doesn’t crash and saves in seconds instead of literally 2+ hours.
5 Likes
You’re welcome!
1 Like
Dude, I’m aware! There are still linked workbooks but they only pull directly from a cell rather than 100k cells summing 100k cells.
The department wanted everything to remain linked and I pretty much said, “Okay, well then here is the workbook you asked me to produce with it linked. It usually will open without crashing and if you’re lucky and have nothing else open you might be able to change a formula.”
Lol, missed this catastrophe.
I’m going to venture a guess here that it’s possible to do your sumiffing inside the linked file.
I realize that the sumiffing probably uses conditions from your file… but I bet you could account for that creating a small table of every possible condition. Then from your file, use like a vlookup or something against your little table instead of the giant sumifs.
Another weird answer might be to use a pivot table linked to “external data”. As they are somewhat more natural for carrying giant piles of data. But not my first recommendation.
1 Like
linking files - i do it, but to a certain spot.
conditions that reach across files? that’s a mess
That would have been possible… then directly linking to the sum.
I’ll be honest, I know SUMIF can be a performance killer on larger data, but since I’ve always avoided linked workbooks where possible I didn’t know how terrible it is across workbooks.
If you really want to mess with your department, link to the sumifs formula, and make the sumifs formula use conditions that are linked to your file. 
1 Like
The obvious solution is to be like us and have 90% of your reporting still come to you on paper. Then you can manually enter everything and never have this problem. Your welcome.
/s
3 Likes
Excel was acting really fruity then I realized I misspelled the function as =SUMPRODUCE.
7 Likes
It does that seasonally.
4 Likes
Bump…
I’m usually pretty smrt about Excel.
I have a file that is ignoring the “Print_Area” named range in several (but not all) tabs. My tabs often contain a number of references that help to fill the printed area’s info, to the right of the printed area. I don’t want these references printed, but they are shown in the print preview.
My fix at the moment is to create a new tab, paste all the columns of the old tab, update the print_area named range, delete the old tab, rename the new tab as the old tab’s name.
But I still wonder why Excel is ignoring the print_area ranges of the tabs.
Any ideas?
Check the page width, if it set to automatic sometimes when the data is two pages wide excel includes everything in the print area.
Checked.
The page width being “automatic” is not affecting other tabs with the same extraneous info outside the "print_area range.
The “basics” are not working:
- Highlight the area to be printed, and click on “Set Print Area” in the Layout menu tab. Ignores it, though it does create the named range “Print_Area” for the tab. When I click on “Print Titles” in the Layout menu tab, the print area is blank.
- Click “Print Titles” in the Layout menu tab, click the “print area” line and highlight the range to be printed. That is ignored as well.
Might check to see if the page’s/tab’s property is somehow locked?
- Right click tab
- Select “Protect Sheet”
Maybe look at the VBA properties to see if “DisplayPageBreaks” is set to True?
Finally: Look at the “Settings” (e.g., Print Active Sheet vs. Print Entire Workbook) in the Print Preview menu. There’s an option for “Ignore Print Area” that is essentially a check box. (I found checking this recreates the problem described at least.)
Not protected.
I saw this. It was not checked.
From what I can tell, in the past I seem to have highlighted a number of tabs, then edited Cell A1 (Cannot remember why, as I shouldn’t have). Each one of these tabs was “contaminated” somehow. I made new tabs (not move/copy, as that would migrate the issue), pasted the data from the old to the new, used Basic #2 above (Basic #1 would not work) to define the print area, then deleted the old tab.
No, I’m not printing anything, but I am making PDFs.
1 Like
I’d suggest.
First, I would clear the print area.
Then re-define the print area
Third, go to the top ribbon PAGE LAYOUT, then PAGE SETUP, then the right tab (SHEET) of the listed tabs and ensure that there are no added columns or rows to repeat at the top or left
Also, make sure the Print Settings are on the ACtive SHeets, not the workbook. (VA ninja’d me here)
I am not sure if Print_area is a special version of a range name. If it is, I suppose it needs to have a scope associated with each sheet. Does the Name Manager look good?