is it possible to use a cell reference that refers to the name of a named range

Hello, I am trying to use a cell reference that refers to the name of a named range in a formula. For example, in the formula "=average(a2,>1)", the cell reference "A2" actually contains the text of the named range "DATA1.1.1_". Is this even possible? If not, is there a way I can make this work? I have a about 800 very long formula's that I need to write and the only thing that is changing in each formula is one named range. I would like be able to set a list of named ranges, then just autofill the formula down the column.

I used an Averageif formula and got a #REF! error. Thanks for the suggestion though!

I don't see any reason why an AVERAGEIF formula wouldn't work in the same way?
The only reason it wouldn't work is if you're trying to pull information from another workbook that is closed, as INDIRECT doesn't like that.

I figured out why I am getting the #REF! error and fixed it, but the formula is still not returning what I need it to. I have created an example workbook to illustrate what I need to create. Cell Sheet2 A3 uses the actual named range in a COUNT Formula and returns the expected results. The cell Sheet2 B3 uses the indirect formula within a count formula to return the same text as the named range but does not return the correct results.

Thanks!

Martin, good eye. I substituted the named range in place of one of the concatenated cells, but it still does not return the correct results. I suspect it may be related to the issue of dynaming ranges inside indirect. I will research that issue.

Thanks!

Ok, a couple of points:

1. When you reference a cell using INDIRECT, you don't put "" marks around it.
2. You're looking at INDIRECT(B2), but this needs to be INDIRECT(B1)
3. You have a space after the 1.1.2 in cell B2

That said, it still didn't work when I made these changes, but for some reason I can't seem to locate where your ranges are. When I re-made some ranges, and changed the formula in B1 to match the new named range, it worked fine. So you may want to check your ranges are functioning properly.

If you look on Sheet4 you will see where all of the named ranges resolve to. Essentially every column on Sheet1 is a named range in the format of sheetname&headerrow. The underscore at the end is present because the macro used to create the names replaces any spaces as named ranges cannot contain spaces. From my research, the issue I am facing stems from the inability of the indirect function being able to resolve a dynamic named range. This is why when you created the new non-dynamic named ranges the formula worked. I guess i will have to write each formula out with the dynamic named ranges as they cannot be removed at this point.

Hmm, that's some hard luck.

If you have that many to do though, and the formula is exactly the same except the named range, at least it should be pretty easy to write a macro to insert the formulae?

Yeah, it sucks. I don't have any idea how to write the macro so I was going to start another thread.

Martin, the dynamic ranges are necessary as the data set grows every month. I would have to set the ranges to to the very limits of excel for it to contain all the data by the end of the lifespan of this sheet. However, in the mean time, to reduce calculation time, I only need to run the calculations on the rows that actually contain data.