2 formulas based on the outcome of an IF - way to show which formula is bringing answer?

Hi there,

Is this possible.

What I have is a spread sheet that contains a grid that I am populating from 2 differing sources.

Sources are as follows:

Another grid of actual values based on invoices received.

Another grid containing start and end dates, working days and pay rates.

I want to pull all this data into a single master grid. I have done this using the following formula:

=IF(SUMIFS(ActualsPivot!$J:$J,ActualsPivot!$K:$K,CombinedGrid!$A6,ActualsPivot!$I:$I,CombinedGrid!AL$5)=0,$AI6,SUMIFS(ActualsPivot!$J:$J,ActualsPivot!$K:$K,CombinedGrid!$A6,ActualsPivot!$I:$I,CombinedGrid!AL$5))

cell AI6 in the above example formula is where the forecast value is located (will be replaced by a formula later on)

OK, so that is working in that if there is no value for that person and week in the actuals, it returns Zero, so the IF then takes the value in cell AI6.

Now what I would like to be able to do is define on my spread sheet which formula is bringing back the answer? I was initially thinking Conditional formatting to show a different colour per formula.....but I'm not sure where to start, and may not be possible in that method.

Thanks in advance.

As your formula returns either the value from A16 or from that long SUMIFS, then couldn't you check in Conditional Formatting to see if the cell is the same as A16 ?

Hope this helps.

Pete

There is chance that the actuals will be equal to the forecast value so this wont work for all rows.

Well in that case you will need to check if the SUMIFS formula is equal to zero (or not equal to, depending on your logic). Unfortunately, CF does not allow you to refer to another sheet, so you may need to use another column in the same sheet to generate the result of this formula:

=SUMIFS(ActualsPivot!$J:$J,ActualsPivot!$K:$K,CombinedGrid!$A6,ActualsPivot!$I:$I,CombinedGrid!AL$5)

and so your first formula can be simplified and use this result. Then you can use CF | New Rule | Use a formula... from the bottom of the list, and put this formula in the dialogue box:

=$H2=0

assuming the new formula is in H2. Then click the Format button | Fill tab and choose your colour, then OK twice to exit the dialogue box.

Hope this helps.

Pete

Hmmm I see where you are going but I have 52 columns of data to bring through, and don't really want that being 104 if I had to add an additional column to show this zero/not zero value.

Well, in fact it will be more efficient. Your formula now has two long SUMIFS, so if you use the helper column it will become:

=IF(H2=0,A16,H2)

so there is only one SUMIFS formula, and the same for the other columns.

Hope this helps.

Pete

EDIT, and you can always hide the helper columns so that your sheet will look the same as it is now.

Hi Pete_UK,

I'm sure that your suggestion will work, but I think the result I am after is more complex than this.

I'm all for keeping it simple mind.

Well, I've given you my advice - it's up to you whether you act upon it or not.

Pete