# What is _xlfn.CUBESET?



## Legacy 328066 (May 29, 2015)

I have a fairly complex (for me!) Excel 2013 spreadsheet using Power Query and Power Pivot to pull information from various SharePoint Lists, CSV files and OData feeds from other Excel spreadsheets to provide Actual vs Budget on the Maintenance Costs of the houses we have. (We are a Charity working with local Churches to house the homeless).

I am getting a constant error message when I refresh the Budget vs Actuals Pivot Table "Reference is not valid". The Pivot Table seems to be updating correctly however.

Using the ASAP Utilities (which I was pointed to by a post on here I think) I am using the Information / Create a List of all Range Names in Workbook and am getting an error reported thus:

Name	         Refers to
_xlfn.CUBESET	 =#NAME?

All the other Named Ranges in the workbook seem to be fine.

I have no idea what _xlfn.CUBESET is and my Google-fu has failed me in finding out 

Can anybody give me some pointers as to where I can look to find where this is and whether it is safe to delete. I am assuming it is because the PivotTable seems to refresh quite happily even with the error!

Thanks.

Ian Watkins


----------



## RoryA (May 29, 2015)

_xlfn.CUBESET is the CUBESET worksheet function. You normally see this where you open a workbook in an earlier version of Excel that doesn't have a particular worksheet function. (e.g. IFERROR in 2003 may appear as _xlfn.IFERROR).


----------



## Legacy 328066 (May 29, 2015)

Thanks.

Yes, I'd seen that as an explanation, but this workbook has only ever been opened in Excel 2013, so I discounted that as a reason.

Any ideas how I can delete it?


----------



## RoryA (May 29, 2015)

Can you actually see the name in Name Manager? I'd guess that it's an issue with ASAP rather than the workbook?


----------



## Legacy 328066 (May 29, 2015)

AIUI the ASAP utilities show Named Ranges that aren't displayed in Name Manager, so, no I can't see it in Name Manager.


----------



## RoryA (May 29, 2015)

Which version of ASAP are you using?


----------



## Legacy 328066 (May 29, 2015)

I downloaded it yesterday, so I guess the latest 

About says: 5.5.1 (December 10, 2014).

It's the unregistered version as I work for a Charity.


----------



## RoryA (May 29, 2015)

It's an issue with how the new formulas are stored internally. I added a simple =FORMULATEXT() function in a cell and then ran this code:

```
Sub foo()
    Dim nm As Name
    For Each nm In ActiveWorkbook.Names
        Debug.Print nm.Name, nm.RefersTo
    Next nm
End Sub
```

It reported an _xlfn.FORMULATEXT name referring to =#NAME? You can't delete it so I wouldn't worry about it.


----------



## Legacy 328066 (May 29, 2015)

Thanks.

However, this *appears* to be giving me a "Reference is not valid" error. Perhaps it's not this then if you can add one and it doesn't cause a problem?

Hmmmm....

Where to go to now?

Thanks for your help.

Ian


----------



## RoryA (May 29, 2015)

Do you have any charts in the workbook? They're the most common cause of that error message.


----------



## Legacy 328066 (May 29, 2015)

I have a fairly complex (for me!) Excel 2013 spreadsheet using Power Query and Power Pivot to pull information from various SharePoint Lists, CSV files and OData feeds from other Excel spreadsheets to provide Actual vs Budget on the Maintenance Costs of the houses we have. (We are a Charity working with local Churches to house the homeless).

I am getting a constant error message when I refresh the Budget vs Actuals Pivot Table "Reference is not valid". The Pivot Table seems to be updating correctly however.

Using the ASAP Utilities (which I was pointed to by a post on here I think) I am using the Information / Create a List of all Range Names in Workbook and am getting an error reported thus:

Name	         Refers to
_xlfn.CUBESET	 =#NAME?

All the other Named Ranges in the workbook seem to be fine.

I have no idea what _xlfn.CUBESET is and my Google-fu has failed me in finding out 

Can anybody give me some pointers as to where I can look to find where this is and whether it is safe to delete. I am assuming it is because the PivotTable seems to refresh quite happily even with the error!

Thanks.

Ian Watkins


----------



## Legacy 328066 (May 29, 2015)

I did have, and they weren't being used, but I've deleted them and the error message still persists.

I've done some fairly major restructuring of this workbook (mainly to change sources from Excel OData which I have found slow even with Buffering the PQ query) to SharePoint Lists (the SP List is also easier to share with our distributed workforce), but this error pre-dates this work.

Much head-scratching has ensued......


----------



## RoryA (May 29, 2015)

When does the error message actually pop up? Only when you refresh that pivot table?


----------



## Legacy 328066 (May 29, 2015)

Yes. And as I say the PT is refreshing accurately.


----------



## RoryA (May 29, 2015)

Did you make any changes to the PQ data/queries after you had already set up the pivot table?


----------



## Legacy 328066 (May 29, 2015)

Several. It's been a continuing work in progress for several months. Most people would do half of the work in a database, but as we don't have one, I've had to make do.

Is there any way to work out what's actually causing the error?


----------



## RoryA (May 29, 2015)

I assume this is a PowerPivot pivot table, not a regular one?


----------



## Legacy 328066 (May 29, 2015)

Yep, several PQ queries, feeding a PowerPivot table.


----------



## RoryA (May 29, 2015)

Can you share the actual file?


----------



## Legacy 328066 (May 29, 2015)

Probably not. The data contains sensitive information. Also it does alot of dynamic reads from SharePoint lists and CSV files.


----------



## RoryA (May 29, 2015)

I guessed as much. Can you delete and recreate the pivot table?


----------



## Legacy 328066 (May 29, 2015)

I have a fairly complex (for me!) Excel 2013 spreadsheet using Power Query and Power Pivot to pull information from various SharePoint Lists, CSV files and OData feeds from other Excel spreadsheets to provide Actual vs Budget on the Maintenance Costs of the houses we have. (We are a Charity working with local Churches to house the homeless).

I am getting a constant error message when I refresh the Budget vs Actuals Pivot Table "Reference is not valid". The Pivot Table seems to be updating correctly however.

Using the ASAP Utilities (which I was pointed to by a post on here I think) I am using the Information / Create a List of all Range Names in Workbook and am getting an error reported thus:

Name	         Refers to
_xlfn.CUBESET	 =#NAME?

All the other Named Ranges in the workbook seem to be fine.

I have no idea what _xlfn.CUBESET is and my Google-fu has failed me in finding out 

Can anybody give me some pointers as to where I can look to find where this is and whether it is safe to delete. I am assuming it is because the PivotTable seems to refresh quite happily even with the error!

Thanks.

Ian Watkins


----------



## Legacy 328066 (May 29, 2015)

I could and I may have to do that, but it is complex with conditional formatting and other stuff. Painful.....


----------



## RoryA (May 29, 2015)

It's probably worth a quick test in a copy of the workbook. Just recreate a basic version and see if it refreshes without error.


----------



## Legacy 328066 (May 29, 2015)

OK, thanks for your help.


----------

