Thread: Array function highlighting cells changing from positive/negative over 2 worksheets

Hi,


If someone could I would be hugely grateful.


I have a workbook based on temperatures that has 3 worksheets in it. What I would like help with is that I need to be able to display positive/negative sign changes (comparing 2 columns on different worksheets) and then display the contents of a different cell (that contains city name) but that is in the row.

One worksheet has average temperatures for various cities over a certain week and is called WEEK (current)

The 2nd worksheet has average temperatures for the same cities over the previous week and is called WEEK (previous).

The 3rd worksheet is a summary worksheet called Summary. On this sheet I would like to create the array function that will compare column C on both WEEK (current) and WEEK (previous) and then if there is a sign change (positive to negative or vica versa) the contents of the cell in column A (this is the city name) in the same row as the relevant cell of the WEEK (current) worksheet.

There could be multiple changes or there could be none but the results need to be displayed on the summary worksheet and not simply highlighted using excel's conditional highlighting capability.

Pls pls pls could someone help me with the formula for this? I would be so grateful.

Thanks Christian

If you could post an example workbook that would be a great deal of help.

Is there only one entry for each city per sheet? Do the city names always appear on the same rows? How are you treating temperatures of zero?

That's not how I read the OP's requirements at all, but I guess we're waiting for them to give us some guidance.

Anyway, as an intellectual exercise, assuming each city name appears only once in each sheet, that the city names are not always in the same order, that a change to or from zero is not counted as sign change and that data begins on row 2 then in column A on the summary sheet, starting at row 2, the following formula is needed ... =IF('Week (Current)'!A2="","",IFERROR(IF('Week (Current)'!C2*INDEX('Week (Previous)'!C:C,MATCH('Week (Current)'!A2,'Week (Previous)'!A:A,0),1)<>ABS('Week (Current)'!C2*INDEX('Week (Previous)'!C:C,MATCH('Week (Current)'!A2,'Week (Previous)'!A:A,0),1)),'Week (Current)'!A2,""),""))