In an excel file, I have 511 named ranges in a workbook that reference numerous tables of data plus I have about 72k indirect formulas used to pull the data into report tables or for charting (yes it's big!). Whenever you add some text to a cell and press enter, it takes about 3-4 seconds to amend and move to the next cell.
I've gone through and checked and by removing all the indirects I think I've come to realise that this is the culprit. I have read about using the Choose() formula:
choose(<index>,Namerange1,Namerange2,Namerange3)
...but the problem is that I'd need to add in all the named ranges in question into the formula but given that I have 511, it is not going to be possible! I have tried a number of ways to reference a cell with the named range in but it always turns it to text (ie it puts the named range in double quotes) which stops it from working being used as a named range. I've also tried converting the named range to an actual address but again it's converted to a text string and not seen as an address from within the lookup / index formula I'm using.
So for example I want to be able to reference a a named range label in cell b2 = MyRange
I then want to use a generic formula in all my tables:
vlookup(<value>,choose(1,B2),false)
where choose(1,b2) would evaluate to MyRange My problem is it currently evaluates to "MyRange"
Is there a way to do this (without using a UDF which might slow everything down further)?
Thanks