how to solve the problem with dynamic criteria list

[Eric Tsang]

Dear all

I may have post the same question many times but everything the answer has further push me for a better solution.

thanks for all Excel guru

See below :
price.PNG

the criteria is listed in a table - G5:G8

If the unit in B column show any items as list in G column, the price state the same , otherwise , it will be divided by the no. of participants
i.e is D8:E8

Thanks in advance
Eric

[MarvinP]

Hi Eric,

This looks like you need to learn about Dynamic Named Ranges and then how to use the Advanced Filter. Find the attached where I've set up the problem on Sheet 2. You might also need to do a DNR for the result data and add some formula at the bottom of it. I've also recorded a very short macro that will use the DNRs to recalculate after you change the yellow cells.

See if this helps..

AdvFilter using DNRs.xlsm

There might be an easier way to do this problem using Tables and Slicers. A Pivot table and slicers might also do what you need but would look a little different that what you show.

[Eric Tsang]

Dear Marvin

Thanks and appreciated

Your way is very sophisticated. We can not use Marco in the workplace.. (funny ah)

The idea is right.

if the Name= the Name in the Yellow Zone. Their amount stay the same.
if their Name is not list in the Yeloow Zone, their amount need to be "divided"bythe participants. ( as we are making a shared quotation)

For the Dynamic Range, i can first convert the text into Table, and then Name them. It will automatic become Dynamic.
However, it does not work for verticial, i.e the header column. In a table, it can expand but the name range is not grow as the same as the horizonal rows

Thanks and i remember i saw the method somewhere, i will keep searching

Appreciated

I have further attached the excel file for your comment

[JeteMc]

On Sheet 2 select the two cell highlighted in yellow (D3:D4) and move them up to D2:D3
Modify the formula in H3 to read, then copy down to H5: =IF(COUNTIFS(CriteriaDNR,F3),G3,G3/$H$2)
Test by changing Name 25, which should now be in cell D2, to Name 20.
Let us know if you have any questions.

[kvsrinivasamurthy]

In D9 then dragged across.

=IF(COUNTIF($G$3:$G$5,$B9)>0,$C9,$C9/D$8)