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.