I have a workbook that prompts me to save even if I immediately close it without even moving the cursor location. I have narrowed it down to the following formula (if I delete the column with this formula then the prompt stops). Note: The named ranges are dynamic and on a separate tab named "Validation". They will always be the same size.
The odd thing is in my research it appears as though most people agree that INDEX() MATCH() is non volatile. Is there a way to fix this so it stops recalculating just by opening the workbook? Is the solution to use VLOOKUP() and making the named ranges one range with two columns? I would prefer to keep Index-Match because I already use the named ranges elsewhere.
Code:
=IFERROR(INDEX(Validation_ProductCategory,MATCH(I41,Validation_ProductType,0)),"---")The odd thing is in my research it appears as though most people agree that INDEX() MATCH() is non volatile. Is there a way to fix this so it stops recalculating just by opening the workbook? Is the solution to use VLOOKUP() and making the named ranges one range with two columns? I would prefer to keep Index-Match because I already use the named ranges elsewhere.
