Formula to reference named ranges

I am trying to create a formula that will "run" a named range that has a complex formula built into it.

While reading about named ranges to simplify formulas (a simple name instead of a data range, say Sheet1!A1:A100, could be named "Data" or something to shorten and simplify a long formula), I also stumbled upon the idea that instead of a named range referencing a data set, you could actually incorporate a formula into that named range.

Say I created two named ranges, called FORM1 and FORM2. (in actuality there could be dozens of named range possibilities created)

In cell A2 is a formula (or my idea) that simply would read very simply =A1. If you type "FORM1" into Cell A1, then A2 would "turn into" the named range formula FORM1, or if you typed "FORM2" into A1, then A2 would "turn into" or run the named range called FORM2.

Is this possible? All the research I have looked into would call this a dynamic reference (or so I think), however everything I look up returns comments an conversation regarding creating a dynamic reference named range. I don't think I want the named range to be dynamic, just the calling up of the named range.

I hope this make sense, and I can create a simple example sheet if needed.

Thanks.

If I've understood what you mean you'd need to use INDIRECT for that. INDIRECT is a volatile function (it calculates every time any change is made to the workbook rather than just when a change is made that directly affects the cell it's used in) so it can cause performance issues.

But I could have misunderstood so perhaps that example file would be a good idea.

BSB

If you have lots of INDIRECT formulas, or any of the volatile functions, then it's highly possible that is the cause of the sluggishness. Certainly a contributing factor anyway.

I think we need to see a sample workbook that explains exactly what you're trying to do.

BSB

I just posted a sample file, it was attached to my previous post.

What should the result be where it says REF?

BSB

For the file as it was posted, it would be 1.93, since the dropdown box said to use the AREA formula. Whatever the solution (formula) is, would be copied and pasted into column G, and then dragged down from row 3 to 8. As you change the formula in the dropdown in Column F, the value in column G would change.

I just posted the file with an INDIRECT function. I had once thought INDIRECT was the way to go, but I can't get anything to come up other than REF. So either my syntax within the INDIRECT is wrong, or INDIRECT can't be used.

OK, I understand what you're trying to do now and I'm not sure INDIRECT is the right approach after all.

This is not something I've ever needed to do so I don't know if it can be done the way you're thinking, however, there are possible solutions.

One would be to use helper columns like you have in columns I to K at the moment. I know they are only there for checking purposes but if you had all your formulas laid out like that then hidden away you could use a lookup formula like the below to return the relevant result.
In G3 and copied down:

Formula:

Please Login or Register  to view this content.

Of course if there are lots of formulas and lots of rows of data this could cause sluggish response from the workbook so may not be ideal.

My preferred option would be VBA. If that's an option for you then a simple worksheet change event would pick up whenever the values in column F are changed and put the appropriate formula in column G.

Something like this in the worksheet module:

Please Login or Register  to view this content.

Other than that I'm stuck for ideas.

BSB

The code I provided previously was merely a demonstration of one method that may have worked for you.

I've slightly advanced that approach in the attached by using a table and a dynamic named range.

The table ensures you don't need to drag your data validation in column F down further than your data goes (the table will expand downward when you add new rows of data).

The dynamic named range (called BSB ) counts the number of rows in the table and by adjusting the defined range (F3:F8) in the VBA previously it all works out well in the end.

Now, if your company doesn't allow VBA (many do not) then this is all a moot point. But if they do then you've got yourself a nifty (free) solution to a small problem.

If the company is of any decent size then an IT network is more than likely in place and emailing this file about shouldn't be an issue as it could be put in a central folder and accessed from there.

If you want a formula only solution then you'll be waiting for someone else to pick the query up or you'll be paying hundreds of dollars an hour for me to spend time on a non VBA solution.

I'm happy for you to go either way on that

BSB