I made a named range that uses indirect to return a table with about 43K objects in it. Im beginning to understand that it is taxing Excel incredibly since I use the data from the table in many calculations on my worksheet. How would I implement the Choose function into this formula:

=INDIRECT("'"&'Loss Template'!$S$33&"'!"&"$A$1:$M"&COUNTA(INDIRECT("'"&'Loss Template'!$S$33&"'!"&"A:A")))

I'm trying to see if this will help my file not use 18 GB to calculate my workbook whenever I run a macro where that indirect named range changes constantly.

  • how many different option can 'Loss Template'!$S$33 be? CHOOSE will require that you put each option as a full reference. This could get quite large if there are too many. – Scott Craner Jul 16 at 16:52
  • 13 options. I dont mind writing one big choose formula to make the named range if that helps make it not volatile. – PotterFan Jul 16 at 17:01
  • It would be better to write a Worksheet_Change event that updates the formulas when ever the value changes. – Scott Craner Jul 16 at 17:38
  • well the thing is the raw data changes with each option. what if i made the each option a table instead of its own worksheet. Would that make it less taxing? – PotterFan Jul 16 at 17:46
  • Can you elaborate on what you mean? I can provide additional context if you need that to help. – PotterFan Jul 16 at 18:06

I made a choose formula after researching and it seems to make a difference.


| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.