Make INDIRECT reference dynamic

[ollyhughes1982]

Hi,

I have a number of very similar INDIRECT formulas in a spreadsheet, example of one below:

=SUM(COUNTIF(INDIRECT({"L4","T4","AB4","AJ4","AR4","AZ4","BH4","BP4","BX4","CF4","CQ4"}),"No"))

I'm using the INDIRECT function as I want to count if what's in non-adjacent cells meets the criteria "No". These work, however, the reference aren't dynamic. Is there a better way that I could do this so that whenever I happen to insert new columns in spreadsheet, I don't have to go in and update all of these references? perhaps with some form of COUNTIF / COUNTIFS?

Thanks in advance.

Olly.

 

[ollyhughes1982]

Yes, it should be looking for *, I just quickly put that in for this example and realised it was *, not 1, for this example afterwards. That doesn't affect the formula not working though?

It'll actually be * and 0, as I have set the cell put in a 0 if it's null

 

[Fluff]

In that case it should be

=SUM(--(VSTACK(I4,Q4,Y4,AG4,AO4,AW4,BE4,BM4,BU4,CC4,CN4)="*"))

 

[ollyhughes1982]

In that case it should be

=SUM(--(VSTACK(I4,Q4,Y4,AG4,AO4,AW4,BE4,BM4,BU4,CC4,CN4)="*"))

Thanks.

The "Reverse Dish?" column is almost exactly the same, except it uses "#" and "0", instead of "*" and "0". so this will be the same solution.

The "Won Lag?" and "Broke?" columns have three possibilities: "Yes", "No" and "U/K". The U/K option is short for unknown, as quite often it isn't known whether they did or did not break, but there needs to be an entry in the box.