Forum Discussion
Excel file changing formula for SharePoint location
I have a formula for a quote system for our sales team. I have had it in place for a couple of years with updates. I have an index function that looks up our centrally stored price list, one formula for the part description, another for the price (based on pricing model there could be 3 appropriate prices). This has all worked until recently. I have not made any changes but Excel has decided to change the formula.
=INDEX('https://multiforce.sharepoint.com/Test Sync/[Multiforce_Pricing_v3.xlsx]Controllers'!$B$3:$B$106,MATCH(Quote!A18,'https://multiforce.sharepoint.com/Test Sync/[Multiforce_Pricing_v3.xlsx]Controllers'!$A$3:$A$106,0),1)
Gets changed to:
=INDEX([Multiforce_Pricing_v3.xlsx]Controllers!$B$3:$B$106,MATCH(Quote!A18,[Multiforce_Pricing_v3.xlsx]Controllers!$A$3:$A$106,0),1)
I have directly edited the formula. I have gone into an older quote file and copied an older formula. Each time, Excel changes the formula to the 2nd version.
I need a remotely located sales team to be able to "find" the pricing file on SharePoint.
Any thoughts?
fuelforce Perhaps silly to suggest, but do you happen to have the Pricing sheet open? Because, then Excel will only show you the workbook name in the formula. Close the Pricing file and you should see the entire file path.
2 Replies
fuelforce Perhaps silly to suggest, but do you happen to have the Pricing sheet open? Because, then Excel will only show you the workbook name in the formula. Close the Pricing file and you should see the entire file path.
Riny_van_Eekelen That was the issue!! Thanks. I didn't realize Excel would do that with an open sheet. Saved my bacon there!
