Home

Issue with excel formulas on sheets/tables connected to SharePoint Online

%3CLINGO-SUB%20id%3D%22lingo-sub-1196544%22%20slang%3D%22en-US%22%3EIssue%20with%20excel%20formulas%20on%20sheets%2Ftables%20connected%20to%20SharePoint%20Online%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1196544%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20exporting%202%20lists%20from%20SharePoint%2C%20then%20running%20formulas%20involving%20fields%20from%20both%20sheets%20to%20create%20reporting%2C%20etc.%20I'm%20then%20refreshing%20the%20tables%20in%20excel%20to%20update%20the%20data%2C%20so%20the%20connection%20needs%20to%20stay%20intact.%20I'm%20also%20using%20PowerPivot%20to%20create%20a%20data%20model%20to%20work%20from%20with%20many%20pivot%20table%20etc.%3CBR%20%2F%3EThe%20issue%20I'm%20having%20is%20that%20certain%20formulas%20(like%20VLOOKUP%20with%20IFERROR)%20are%20not%20working%20while%20the%20sheets%20are%20formatted%20as%20a%20connected%20table.%20if%20I%20convert%20the%20%22formatted%20as%20table%22%20with%20%22convert%20to%20range%22%20then%20the%20formulas%20work%20fine%2C%20but%20this%20breaks%20to%20connection%20to%20SharePoint.%3CBR%20%2F%3E%3CBR%20%2F%3EIs%20what%20I'm%20doing%20even%20possible%3F%20Am%20I%20barking%20up%20a%20tree%20I%20shouldn't%20be%3F%20How%20can%20I%20make%20this%20work%3F%20Open%20to%20all%20suggestions.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1196544%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Visitor

I am exporting 2 lists from SharePoint, then running formulas involving fields from both sheets to create reporting, etc. I'm then refreshing the tables in excel to update the data, so the connection needs to stay intact. I'm also using PowerPivot to create a data model to work from with many pivot table etc.
The issue I'm having is that certain formulas (like VLOOKUP with IFERROR) are not working while the sheets are formatted as a connected table. if I convert the "formatted as table" with "convert to range" then the formulas work fine, but this breaks to connection to SharePoint.

Is what I'm doing even possible? Am I barking up a tree I shouldn't be? How can I make this work? Open to all suggestions.

Thanks!

Related Conversations
Horizontal Scrolling
stephen607 in Excel on
0 Replies
How to in Excel
HarryNetherlands in Excel on
1 Replies
difference between copy and cut in excel
jabed in Excel on
1 Replies
Combining lists of differing items
moonlight1212 in Excel on
1 Replies
Power Query Union Tablas sin Campos en Comun
Exemilenio in Excel on
1 Replies