Forum Discussion

KSuresh720's avatar
KSuresh720
Copper Contributor
Jul 26, 2021

GETPIVOTDATA and XLOOKUP formula producing an #REF error within SharePoint workbook.

Hi all,

 

I am having trouble diagnosing a formula issue regarding a GETPIVOTDATA formula capturing data from pivot table in another workbook within the same SharePoint file. The formula is as follows:

 

=GETPIVOTDATA("Sum of CM",'https://xxxxxxxx.sharepoint.com/sites/xxxxxxxx/Shared  Documents/0.1 xxxxxxx/Stock Management Schema Excel/[Stock Management Worksheet v3.1.xlsm]PT_DocketsIn'!$U$3)

 

Additionally, the following XLOOKUP formula is producing the same #REF error.

 

=XLOOKUP([@[RS_Run]],'https://xxxxxxxxx.sharepoint.com/sites/xxxxxxxxxx/Shared Documents/0.1 xxxxxxxxxx/Stock Management Schema Excel/Stock Management Worksheet v3.1.xlsm'!Table4_2[Resort_Run],'https://xxxxxxxxx.sharepoint.com/sites/xxxxxxxxx/Shared Documents/0.1 xxxxxxxxxx RESOURCES/Stock Management Schema Excel/Stock Management Worksheet v3.1.xlsm'!Table4_2[Supplier])

 

Is anyone able to spot any semantic errors in the formula as I am struggling to. Other XLOOKUP and GETPIVOTDATA formulas within the worksheet seem to work as intended, so I am unsure why these are not.

 

Any advice would be greatly appreciated. 

 

NOTE: Please ignore the 'x's, I have redacted the domain name for security purposes. 

 

Krishna 

 

 

excel

1 Reply

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 27, 2021

    KSuresh720 

    I see nothing wrong in formula. Repeated on my file:

    =XLOOKUP([@[RS_Run]],
 'https://tenant.sharepoint.com/sites/mysite/Models/Test Models/test.xlsx'!Table1[A],
 'https://tenant.sharepoint.com/sites/mysite/Models/Test Models/test.xlsx'!Table1[B]
)

    it works.

Resources