CODEWITHSUNDEEP
excelgoogle-sheetsexcel-formula
Matt
Matt

Reputation: 165

Combine INDIRECT with IF AND COUNTIF

I'm working on a Google Sheets based database for work, which shows availability on the dive boats, which instructors are available etc.

I need to copy some some similar formulas to the rows below. There are 365 rows for each day of the year, and several columns with different formulas I need to copy. Sound's straight-forward enough right, but, there's a couple of things that makes this harder.

Here's one of the formulas...

=IF(AND(COUNTIF(MER!$T4:$T23,AE3)=0,COUNTIF(CHO!$T4:$T23,AE3)=0, COUNTIF(KEP!$T4:$T23,AE3)=0,COUNTIF(SUP!$T4:$T23,AE3)=0),AE3,"")
  1. I am working from Sheet 'AVL'. It basically references ranges in five different sheets in the same workbook to look for a match against a list of dive instructors we employ who are available to work on a daily basis.
  2. I need to copy the formula to the rows directly below, so close to 200 rows for the remainder of this year, and on a new sheet for 2017, a further 365 rows.

If I copy as normal on google sheets, > Paste special > Paste formula, the formula increases the row number on the formula by one row. EG:

=IF(AND(COUNTIF(MER!$T4:$T23,AE3)=0,COUNTIF(CHO!$T4:$T23,AE3)=0, COUNTIF(KEP!$T4:$T23,AE3)=0,COUNTIF(SUP!$T4:$T23,AE3)=0),AE3,"")
=IF(AND(COUNTIF(MER!$T5:$T24,AE4)=0,COUNTIF(CHO!$T5:$T24,AE4)=0, COUNTIF(KEP!$T5:$T24,AE4)=0,COUNTIF(SUP!$T5:$T24,AE4)=0),AE4,"")

That's no good to me, because the ranges I need to reference from the other sheets are completely different, but, the cell referenced with the staff name on, on the sheet I'm working on 'AVL' moves down by one row which is correct.

The ranges from the five other sheets needs to move down by 26 rows each time. EG:

=IF(AND(COUNTIF(MER!$T4:$T23,AE3)=0,COUNTIF(CHO!$T4:$T23,AE3)=0, COUNTIF(KEP!$T4:$T23,AE3)=0,COUNTIF(SUP!$T4:$T23,AE3)=0),AE3,"")
=IF(AND(COUNTIF(MER!$T30:$T49,AE4)=0,COUNTIF(CHO!$T30:$T49,AE4)=0, COUNTIF(KEP!$T30:$T49,AE4)=0, COUNTIF(SUP!$T30:$T49,AE4)=0, COUNTIF(OTH!$T30:$T49,AE4)=0),AE4,"")
=IF(AND(COUNTIF(MER!$U56:$U75,AE5)=0,COUNTIF(CHO!$U56:$U75,AE5)=0, COUNTIF(KEP!$U56:$U75,AE5)=0,COUNTIF(SUP!$U56:$U75,AF5)=0, COUNTIF(OTH!$U56:$U75,AE5)=0),AE5,"")

Etc etc.

I've used the INDIRECT function previously for something else, and was wondering if I should be looking at combining the formula I used for something else, with the formula I need to copy 1,000's of times. EG...

=INDIRECT("CHO!B" & (3 + 26*(ROW()-3)))

adjusted and modified and combined with...

=IF(AND(COUNTIF(MER!$T4:$T23,AE3)=0,COUNTIF(CHO!$T4:$T23,AE3)=0, COUNTIF(KEP!$T4:$T23,AE3)=0,COUNTIF(SUP!$T4:$T23,AE3)=0),AE3,"")

to somehow increase the ranges by 26 rows every time I copy the formula, while leaving the cell I'm matching against AE3 etc, to just increase by one row.

Upvotes: 0

Views: 1015

Answers (1)

user4039065
user4039065

Reputation:

Build your cell ranges with a pair of non-volatile¹ INDEX functions.

For example, these two cell ranges are exactly the same.

=T4:T23
=INDEX(T:T, 4):INDEX(T:T, 23)

The rest is just simple maths and a ROW function. Put this in the top row.

=IF(AND(COUNTIF(INDEX(MER!$T:$T, (ROW(1:1)-1)*26+4):INDEX(MER!$T:$T, (ROW(1:1)-1)*26+23), AE3)=0,
        COUNTIF(INDEX(CHO!$T:$T, (ROW(1:1)-1)*26+4):INDEX(CHO!$T:$T, (ROW(1:1)-1)*26+23), AE3)=0,
        COUNTIF(INDEX(KEP!$T:$T, (ROW(1:1)-1)*26+4):INDEX(KEP!$T:$T, (ROW(1:1)-1)*26+23), AE3)=0,
        COUNTIF(INDEX(SUP!$T:$T, (ROW(1:1)-1)*26+4):INDEX(SUP!$T:$T, (ROW(1:1)-1)*26+23), AE3)=0), AE3, "")

Fill down as necessary.

¹ Volatile functions recalculate whenever anything in the entire workbook changes, not just when something that affects their outcome changes. Examples of volatile functions are INDIRECT, OFFSET, TODAY, NOW, RAND and RANDBETWEEN. Some sub-functions of the CELL and INFO worksheet functions will make them volatile as well.

Upvotes: 1

Related Questions