Incrementing IF formula's by rows

**Alexvs** · 12-08-2010, 01:06 PM

Hi,

I'm trying to copy an IF statement across cells but can't work out how to increment the rows without affecting the columns:

I have a similar table to the one attached and would like to copy the formula in the sales tab across the row for each agent but increment only the row value. We have about 90 agents so I don't want to do it one by one by manually changing the reference. I've only included one row just for an example and included the lookup table in the same workbook which is the 01-12 tab.

Is there any easy way to achieve this?

Thanks

Alex

**DonkeyOte** · 12-08-2010, 01:31 PM

Numerous possibilities... one would be:

Please Login or Register  to view this content.

**Alexvs** · 12-09-2010, 05:10 AM

Thanks for pointing me in the right direction. I had to repeat the INDEX part of the formula for the second column that I needed to include in the sum and it's working perfectly. This is what I did:

=IF(SUM(INDEX('01-12'!$B$2:$B$14,COLUMNS($C3:C3)),INDEX('01-12'!$C$2:$C$14,COLUMNS($C3:C3)))>0,"Sick","")

Thanks

**DonkeyOte** · 12-09-2010, 05:24 AM

Not sure I follow why you need to include Column C ("absent" rather than "sick") but if that's the case you should use:

Please Login or Register  to view this content.

**Alexvs** · 12-09-2010, 12:10 PM

It's because we monitor sick and absense individually but want to class both as sick on that report so we can monitor repeat offenders. The other report I've copied the formula into has the two columns seperated so have others between the two which is why I've done the formula the way I have. Its working perfectly and have now filled the report for the whole month and doing the same for previous month.

Thanks again.