Help with code

[jsfon]

Hello all,

Considering the following:


Sheet 1 (dynamic named range for different fruits)

ColumnA | ColumnB | ColumnC
Banana
Apple
...


Sheet 2 (montly evaluation by different persons and on different criteria)

ColumnA(fruit) | ColumnB(month) | ColumnC(year) | ColumnD(taste) | ColumnE(colour)
banana | june | 2011 | good | poor
banana | june | 2011 | poor | good
Apple | june | 2011 | avarage | poor
banana | july | 2011 | good | average


If I define in sheet3 the month and year for my report ( A1=June and A2=2011), the macro should display in sheet 1 the average score for each fruit in the different criteria (columnB = ctaste, columnC = colour) based on: "poor"=1, "average"=2 and "good"=3.

So in this example (june 2011) the result would be:

Sheet 1 (dynamic named range for different fruits that is expandeble as new fruits are added from time to time)

ColumnA | ColumnB | ColumnC
Banana 2 2
Apple 2 1
cheries 'empty 'empty
...


Can you please help me with a VBAcode for this?

[Bob Phillips]

Post a workbook, your example is confusing.

[jsfon]

Workbook included.

[Bob Phillips]

No need for VBA< use this array formula

=SUM(IF((Sheet2!$B$1:$B$20=Sheet3!$A$3)*(Sheet2!$A$1:$A$20=Sheet3!$A$2)*(Sh eet2!$C$1:$C$20=$A2),(Sheet2!D$1:D$20="Poor")*1+(Sheet2!D$1:D$20="Average")*2+(Sheet2!D$1:D$20="Good")*3))/
MAX(1,SUM((Sheet2!$B$1:$B$20=Sheet3!$A$3)*(Sheet2!$A$1:$A$20=Sheet3!$A$2)*( Sheet2!$C$1:$C$20=$A2)))

[jsfon]

Hi xld, thank you for your help.

The result I have with the formula is 1 but it should be 1,5. Actually it always gives 1 divided by 1.

There is also another point, the fruit list will be increased (dynamic named range), how can I have a macro to insert the formula in each column when a new fruit is added. (the best option would be a code to add the formula to each row when the report is pulled and also adapt the reference to the fruit name accordigly to each entry in the list: A1,A2,A3 and so on as long as the named range).

[jsfon]

Sorry, my bad. Just realised it was an array formula. It gives the correct result when entered with shift+ctrl+enter.

Can you please help on the second point above? and how do I enter a array formula in the cell in code?

Thanks.

[Bob Phillips]

You use the FormulaArray property.

[jsfon]

Many thanks. Your help was presious.

Can you also help me with the rest of the code please?

Something like this:
For each row(i) in named range Fruitlist
Column 2 FormulaArray = the formula you provided but the ref A2 is replaced by the "A" & row number (i).

[jsfon]

Can someone help with the last part of the code please?

What I what is to insert an array formula in column B for each row of a named range but the array formula needs to be adapted to reference the fist cell of that specific row.

So in B2 it should insert the array: =SUM(IF((Sheet2!$B$1:$B$20=Sheet3!$A$3)*(Sheet2!$A$1:$A$20=Sheet3!$A$2)*(Sh eet2!$C$1:$C$20=$A2),(Sheet2!D$1:D$20="Poor")*1+(Sheet2!D$1:D$20="Average") *2+(Sheet2!D$1:D$20="Good")*3))/
MAX(1,SUM((Sheet2!$B$1:$B$20=Sheet3!$A$3)*(Sheet2!$A$1:$A$20=Sheet3!$A$2)*( Sheet2!$C$1:$C$20=$A2)))

And in cell B3 the array should be: =SUM(IF((Sheet2!$B$1:$B$20=Sheet3!$A$3)*(Sheet2!$A$1:$A$20=Sheet3!$A$2)*(Sh eet2!$C$1:$C$20=$A3),(Sheet2!D$1:D$20="Poor")*1+(Sheet2!D$1:D$20="Average") *2+(Sheet2!D$1:D$20="Good")*3))/
MAX(1,SUM((Sheet2!$B$1:$B$20=Sheet3!$A$3)*(Sheet2!$A$1:$A$20=Sheet3!$A$2)*( Sheet2!$C$1:$C$20=$A2)))

and so long as long as the named range. I need to do it this way because this is a dynamic range that grows over time.

Thanks