Advertisements

Quick tip: Prevent #DIV/0! and #VALUE! errors in Excel files

Recently I was helping a client visualize their excel data using Power BI and ran across an error in Power BI when it couldn’t deal with “#DIV/0!” as a cell value in Excel: “DataFormat.Error: Invalid cell value ‘#DIV/0!’.”  You get this error in excel whenever you have a calculated formula that is trying to divide…

Advertisements

How to increase lookup column limit in SharePoint lists

Lookup columns include workflow status columns, traditional lookup columns to other lists, and person/group columns. This includes the two default people fields “Created by” and “Modified by”. If your list has more than 12 of these, you may receive the following error:

“This view cannot be displayed because the number of lookup and workflow status columns it contains exceeds the threshold (12) enforced by the administrator.”

list view threshold.PNGIn SharePoint Online, you’re not able to increase the lookup column limit. Lists created prior to the June 2013 CU update are capped at 8 lookup columns, while those afterward are allowed 12.

However, on-premise SharePoint (server) allows you to change this limit to your heart’s content.

Solution: “The user who attempted to complete the task is not the user to whom the task is assigned”

You might run into this issue when running task processes in SharePoint Designer. “Reason: The user who attempted to complete the task is not the user to whom the task is assigned.” Assuming the person should actually be able to complete the task, check the following: Make sure the user who was assigned the task…

Solution: “We’re sorry. We can’t open {file} because we found a problem with its contents.”

When using SharePoint Designer workflows to automatically create documents from a template you might run into an error message:

“We’re sorry. We can’t open {file} because we found a problem with its contents.”

document contents error

Or if you’re just opening it within the browser web app instead of the Word client, you might have seen:

“Sorry, Word Online ran into a problem opening this document. To view this document, please open it in Microsoft Word.”

But if you do open it in Word you then get the first error message. Luckily it’s a simple fix.

Solution: “Visio cannot open the file because it’s not a Visio file or it has become corrupted.”

visioerror

If you’re new to exporting SharePoint Designer workflows to Visio, chances are you’ve seen this message.

“Visio cannot open the file because it’s not a Visio file or it has become corrupted.”

Opening the exported Visio file is not intuitive (export, open), but also isn’t complicated. Instead of opening the file directly, you need to import it from within Visio.

Solution: Using a Yes/No checkbox in a Microsoft Flow condition statement

2017-09-17_13-59-37

It’s not uncommon to want to use yes/no checkboxes when building Microsoft Flow conditions. [Field] is equal to “Yes” or [Field] is equal to true won’t work because it reads the Yes or true as a string rather than a value. So when the flow runs, even if the checkbox is checked (true), the run history says the expression result was false.

Fortunately it’s a simple two-step fix. Follow these steps to be able to use yes/no checkboxes as conditions in your flows: