I'm making a worksheet to show pricing for elements of custom packages.
I have the actual formula for the pricing worked out;
Cell M11:
=IF($K11=1,INDEX($F$5:$F$25,MATCH(1,("x"=$B$5:$B$25)*($I11=$E$5:$E$25),0)),IF($K11>1,MAX(IF(($B$5:$B$25="x")*($E$5:$E$25=$I11),$F$5:$F$25))+10*($K11-1),0))
But I also need to use conditional formatting to show which price is being selected for every part of the package.
First I choose what's included in the package by entering "x" (column B)
If the selections have different methods (column E), then the highest price for that method is charged.
Every subsequent same method is charged +$10 (the formula above is doing this)
For the conditional formatting, I have this formula so far;
=AND($B5="x",$E5=$I$8,$F5=$L$8)
I have this condition entered for each method with $I$8 and $L$8 changed to correspond to the different methods and the highest price of each method.
How do I make it so that only the first instance of a specific combination of criteria is highlighted?
For example cells F7 and F8 have met all the criteria ("x" entered in B, same method in E, match the highest price) but I need the first instance of $29 (Cell F7) to be highlighted, and any secondary instances to highlight the $10 cell instead (cell G8)
Same goes for cells F16 and F18. They're both "x", and both the same method, and same price, but I need the first instance to highlight the "$25", and the second instance to highlight the "$10"
I'm just stuck and appreciate any help given! Thanks!
I have the actual formula for the pricing worked out;
Cell M11:
=IF($K11=1,INDEX($F$5:$F$25,MATCH(1,("x"=$B$5:$B$25)*($I11=$E$5:$E$25),0)),IF($K11>1,MAX(IF(($B$5:$B$25="x")*($E$5:$E$25=$I11),$F$5:$F$25))+10*($K11-1),0))
But I also need to use conditional formatting to show which price is being selected for every part of the package.
First I choose what's included in the package by entering "x" (column B)
If the selections have different methods (column E), then the highest price for that method is charged.
Every subsequent same method is charged +$10 (the formula above is doing this)
For the conditional formatting, I have this formula so far;
=AND($B5="x",$E5=$I$8,$F5=$L$8)
I have this condition entered for each method with $I$8 and $L$8 changed to correspond to the different methods and the highest price of each method.
How do I make it so that only the first instance of a specific combination of criteria is highlighted?
For example cells F7 and F8 have met all the criteria ("x" entered in B, same method in E, match the highest price) but I need the first instance of $29 (Cell F7) to be highlighted, and any secondary instances to highlight the $10 cell instead (cell G8)
Same goes for cells F16 and F18. They're both "x", and both the same method, and same price, but I need the first instance to highlight the "$25", and the second instance to highlight the "$10"
I'm just stuck and appreciate any help given! Thanks!