Need experts help on this after racking brain over and over. I have a data sheet, 'DATA', that contains Product and Pricing detail by row. Each row will have a column for Product Name (ie. Product A, Product B, Product C....etc), and a column for Location, (ie.Location 1, Location 2, Location 3....etc.) On another worksheet, I have a table that's a pricing schedule, 'PRICING SCHEDULE', for each product at each location. I've created a Named Range for each Product/Location line on the Pricing Schedule based on the previous product and location name example I gave looks like this: PRODA1, PRODB2, PRODC3. The pricing schedule will have the Price for the Product/Location combo.
The same product can be sold at multiple locations with a different price for each. So it will need a unique name in order to obtain the correct value. I'm using the Aggregate function in order to avoid having a longer formula with multiple IF Statements and And/OR operators. I'm just having trouble with the Aggregate functions array argument.
Aggregate works perfectly if I just directly type in the named range, but unfortunately, I have a dynamic file so I'm needing to reference in each data row the product and location to obtain the correct value from the other sheet. This has me creating a concatenation where I combine LEFT function to get the 1st 4 characters of the Product Name field and RIGHT function for the last character on both the Product Name field and the Location Name field. So for a row where the product is, 'Product B' for location 'Location 4', I get a concatenation of, ProdB4 which I have a named range setup as in the Pricing Schedule. Unfortunately, when I reference the cell that contains the concatenation, it generates a VOLATILE formula result. It also generates a Volatile formula result when entering the concatenation directly into the function's array argument. Here is my formula:
AGGREGATE(15,6,INDIRECT(CONCATENATE(LEFT(B2,4)&RIGHT(B2,1)&RIGHT(H2,1))/(CONCATENATE(LEFT(B2,4)&RIGHT(B2,1)&RIGHT(H2,1)))>$P2),1)
If I just type in PRODB4 where I am referencing my named range, the formula works perfectly, as the formula result give me a value. Again, I know there might be another formula option, but I don't want to get into a long IF statement, so would really need to keep using AGGREGATE. I am open to renaming the Named Ranges if you have a better suggestion, as well as obviously any formula changes you recommend.
Any help you can give with this will be greatly appreciated
The same product can be sold at multiple locations with a different price for each. So it will need a unique name in order to obtain the correct value. I'm using the Aggregate function in order to avoid having a longer formula with multiple IF Statements and And/OR operators. I'm just having trouble with the Aggregate functions array argument.
Aggregate works perfectly if I just directly type in the named range, but unfortunately, I have a dynamic file so I'm needing to reference in each data row the product and location to obtain the correct value from the other sheet. This has me creating a concatenation where I combine LEFT function to get the 1st 4 characters of the Product Name field and RIGHT function for the last character on both the Product Name field and the Location Name field. So for a row where the product is, 'Product B' for location 'Location 4', I get a concatenation of, ProdB4 which I have a named range setup as in the Pricing Schedule. Unfortunately, when I reference the cell that contains the concatenation, it generates a VOLATILE formula result. It also generates a Volatile formula result when entering the concatenation directly into the function's array argument. Here is my formula:
AGGREGATE(15,6,INDIRECT(CONCATENATE(LEFT(B2,4)&RIGHT(B2,1)&RIGHT(H2,1))/(CONCATENATE(LEFT(B2,4)&RIGHT(B2,1)&RIGHT(H2,1)))>$P2),1)
If I just type in PRODB4 where I am referencing my named range, the formula works perfectly, as the formula result give me a value. Again, I know there might be another formula option, but I don't want to get into a long IF statement, so would really need to keep using AGGREGATE. I am open to renaming the Named Ranges if you have a better suggestion, as well as obviously any formula changes you recommend.
Any help you can give with this will be greatly appreciated
