Reference To Named Range Causing Volatile Aggregate Forumula

Just one clarification to avoid any confusion. Prior to writing the post, I had indeed had the concatentation in another cell on the applicable row in DATA sheet. I had been using an INDIRECT function and then referencing the cell with the concatenation. However, when typing out my message to you, I thought about just attempting to do the concatenation inside the AGGREGATE formula along with INDIRECT function. Neither method works for me, as it shows a VOLATILE reading. Again if I actually type the NAMED RANGE in the AGGREGATE formula, it gives me the expected value. Sorry for any confusion this may have provided.

Hi,

Not sure I understand what you are getting at, to be perfectly honest. INDIRECT is well known to be volatile; why the assertion that referencing a Named Range is causing the volatility?

Or do you have a different definition of 'volatility' in mind?

Regards

I don't disagree. If you replace the Indirect/Concatentation in highlighted cell with the actual named range. The formula works. I need that to be done for each row down. I tried INDIRECT since the named ranges were on another sheet, thinking that might be the issue. It's not though.

So what's the issue? What do you mean by 'volatility' here?

Perhaps re-post your workbook with an instance of the formula which doesn't work.

Regards

I don't understand what you want but this would get you the same result as W2

=AGGREGATE(15,6,PaySched/(PaySched[Position]=B2)/(PaySched[Location]=H2)/(PaySched>P2),1)

Nailed it BO_Ry. I really appreciate your help with this.

After further review, Bo's solution does not work. Using the table fields will not work for this problem, as the same Product/Position is found in multiple locations and does not keep the same pricing. That is why I created name ranges for each Product/Position and it's applicable location. Sorry for the confusion. Bo's solution does remove the Volatile reading in Aggregate and displays each instance of the array. So on right track, but I just need to do the same with the named ranges.

Sorry for thinking the solution was solved and creating additional confusion.

I think your whole approach probably needs reworking here. Sometimes it's better to let the experts on this forum work on their own solution to the problem, rather than pressing them for your own (rather convoluted, I'm afraid to say) solution to be modified.

With that in mind, might I suggest that you ditch any further conversations re your attempted Named Range solution and instead re-post a workbook with expected results filled in in column W? Importantly, I would also recommend doing this for some entries in the Location Facility column which are different from Location 1.

Regards

I appreciate the response, but I don't believe I'm asking for a "convoluted solution". I'm trying to obtain/create a named range from two different cells in a data row. Production A and Location 1, for example, will be ProdA1. When I type 'ProdA1' directly into the Aggregate function you can see the function arguments are correct, as the formula result is giving an actual value and not 'Volatile'. Bo_Ry's solution was close, as his did give a value. However it's doing a lookup on the 1st matching position in the table field. I have the same product in multiple locations with a different associated price, which is why my named range and concatenation is using both the location and product/position. In my opinion, a convoluted solution would be one where I simplify my formula by using If statements with And/Or operators. The result would be 3 additional lines.

I don't feel an additional workbook is needed for this solution. In essence there has already been more data than necessary. My highlighted field with the Aggregation function needs the array to reference product and location in the form of a table field or named range. I'm open to another naming convention for named range if it makes utilizing those two columns easier. I just need to create a unique name that is recognized and accepted in a function argument.

It's a shame that you see it that way, since there may well exist a solution which is entirely different to - and much simpler than - that which you are currently insisting upon implementing.

However, if you're not prepared to do as as I ask then could you at least respond to my earlier questions? That is:

What do you mean by 'volatility' here, assuming you have some definition different to the conventional one? INDIRECT - a volatile function - is the only way to create references to Named Ranges via dynamic concatenation of text strings. Nevertheless, it works, despite said volatility. Given that you seem insistent on going down this route, wouldn't a volatile - yet working - workbook be an acceptable solution?

Secondly, can you give an instance of the formula in your original workbook which does not give the results you would expect? You only give one, in cell W2, which seems to produce a correct result - copying this formula down also gives the results I would expect, i.e. 7.2915 for Position A/Location 1 and a #NUM! error for Position W/Location 1 (unfortunately, these are the only two combinations you give for testing).

Regards

Administrative Note:

RenoMartinez - please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

Administrative Note:

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. No help to be offered until the link is provided.)

However, you do need to provide the links requested, please. Thank you.

if you or others feel that I violated policy as that was not my intention

Nonetheless, rule #3 is pretty clear:

https://www.excelforum.com/forum-rul...rum-rules.html