Hi

I have what hopefully is a simple question. I have a forumla:

=IF(COUNTA(INDIRECT("D3:M3"))3,"Training",COUNTA( INDIRECT("D3:M3")))

That works perfectly, but when i copy it down, it doesnt update the cell numbers, eg:

=IF(COUNTA(INDIRECT("D3:M3"))3,"Training",COUNTA( INDIRECT("D3:M3")))
=IF(COUNTA(INDIRECT("D4:M4"))3,"Training",COUNTA( INDIRECT("D4:M4")))
=IF(COUNTA(INDIRECT("D5:M5"))3,"Training",COUNTA( INDIRECT("D5:M5")))

I have made sure that workbook calculation is set to "Automatic" as well as i know thats the most common mistake.

Can anyone help me?

Thanks very much

Danny

Because the cell references are within quotes Excel will not progress the row numbers as you drag down.

Ahhh i see, i didnt realise that thank you.

Do you know of any way around this? Unfortunately this wasnt my formula, im working with a sheet that already existed.

Basically it just calculates checks if anything exitis in each row, columns D to M. If there are more than 3 columns with something in then it marks a user for training. It also counts up in the training column as it does that, going from 1 to 2 to 3 to training.

Thanks for your help so far mate, much appreciated.

Danny

Without seeing the workbook it's not easy to work out an alternative formula. I'm a bit stumped as to why you're using INDIRECT in there.

If you can post a sample of the workbook that would help. Either post it here or you can email it to me on pubnut @ gmail . com (with no spaces obviously).

No problem,

Heres a sample sheet.



So basically the user of the sheet, will hide column H and then add a column between C & D, adding in the days date. Therfore keeping the data we need to be able to query back as far as 10 days (up to column M).

Column B is the one with the formula in it. So for example, Column B, Row 2's formula is:

=IF(COUNTA(INDIRECT("D2:M2"))3,"Training",COUNTA( INDIRECT("D2:M2")))

It's a voicemail update check spreadsheet.

Hope that make sense.

Thanks again

Try the below dormula in B2 and copy down.

=IF(COUNTA(INDIRECT("D"&ROW()&":M"&ROW()))3,"Trai ning",COUNTA(INDIRECT("D"&ROW()&":M"&ROW())))

Excellent! What a legend! that works perfectly! Thank you!

Really thank you this has been bugging me for a long time!

Very much appreciated!

Danny

Not a problem Danny. Happy to help.

I didn't understand at first why you were using INDIRECT, but the example made it all clear.

Personally I would have managed it slightly differently to that, but you have a working formula now and that's what counts :)

S.

"azonicds2" wrote:
I have what hopefully is a simple question. I have a forumla:
=IF(COUNTA(INDIRECT("D3:M3"))3,"Training",COUNTA( INDIRECT("D3:M3")))
That works perfectly, but when i copy it down, it *doesnt*
update the cell numbers, eg:
=IF(COUNTA(INDIRECT("D3:M3"))3,"Training",COUNTA( INDIRECT("D3:M3")))
=IF(COUNTA(INDIRECT("D4:M4"))3,"Training",COUNTA( INDIRECT("D4:M4")))
=IF(COUNTA(INDIRECT("D5:M5"))3,"Training",COUNTA( INDIRECT("D5:M5")))

Omit the use of INDIRECT. (INDIRECT should be avoided anyway, for other
reasons.) Simply write:

=IF(COUNTA(D3:M3)3,"Training",COUNTA(D3:M3))

The problem with this approach is when the user inserts a new column D, the references in the formula shift along and the data would not be captured in the calculation.

Personally I would have gone for something like :

=IF(COUNTA(C3:M3)4,"Training",COUNTA(C3:M3")-1)

This would have avoided the need for using INDIRECT but would have allowed the insertion of a new column D each day.