Excel 2016 error

[Evol B4]

Hello all,

I have issue where one of our Excel spreadsheets suddenly shows following error message.

External Data Refresh Failed
This Command requires at least two rows of source data.
You cannot use the command on selection in only one row.
We were unable to refresh one or more data connections to the workbook.
The fallowing connections failed to refresh.
Range a187:R187

The range provided has only got text in it no connections etc.

Any Ideas please?

Kind Regards,

Evol B4

 

[ashleedawg]

Your workbook is linked to another file (probably another Excel workbook) and one of the two of them has a problem. The cell range "A187:R187" mightbe referring the other workbook, and that error sounds like you have code (or a macro) trying to do something that can't be done without at least two rows of data. For example, an attempt to "sort" a single cell would not produce an error, but an an attempt to "remove duplicates" from a single cell would produce an error.

Googling the error "Where are cells A187:R187 getting their data?" and looking through some of the search results leads me to believe this is often caused by a Pivot Table that is set to automatically "Refresh data when opening the file". If that's the case, then disabling that option (mentioned in the lower part of this page from Excel's documentation) should stop the error from appearing.

...and then you'll just need to figure out what's wrong with the Pivot Table. (Without seeing what data, etc, you're working with, nobody can help with that!)

Alternatively, you could simply restore your backup copy of this workbook from before the error occurred and re-do any updates that have been made since then, watching carefully for any anomalies along the way. ("Of course" you keep regular backups, right?)