Forum Discussion
Using Indirect() function with a dynamically set named range
You could try this:
Select C1 on Sheet2 (this is important).
Create a new defined name named Eval that refers to =Evaluate(Sheet2!A1&Sheet2!B1)
Enter the formula =Eval in C1 (or in C1:E1 as in the screenshot below)
This can be filled down if you have other sheet names and range names.
This is really interesting. I haven't come across using =evaluate( ) function in the named range.
However, unfortunately, this can't be a solution for my particular application due to the workbook structure.
I could have many [input] sheets with 'dozens' of local range names.
- Let's say I have local named ranges called Price1, Price2, ... PriceN
- This is a time series array. Right now they are statically defined say A(row) : CV(row) - 100 values
- I have 10 input sheets called Store1, Store2, Store3, Store4.
- They all have the same local range names.
- I have a sheet called [Calculations]
- in here, I use Sheet names and named ranges using = Indirect ( Sheet!Local_Named_Range)
- This works flawlessly and does what I need them to do. Very easy to work with
But this forces me to work with range names that are defined using the longest possible length all the time. I can reduce my workbook's calculations dramatically if I can make the range named dynamically defined. There is no practical way for me to use your recommendation without fundamentally re-working my workbook (which I am not allowed to do). I was hoping there is a way for me to change the formula to make this work