Insert cell value as part of address in formula

[Deutz]

Hi and thanks in advance.

I have a cell with a formula which displays the number of used rows on a sheet.

I have another cell with a formula on the same sheet which displays "OK" if all the values in columns Q and U are TRUE, or "OUT" if FALSE ...

=IF(COUNTIF($Q$12:$Q$865, TRUE) + COUNTIF($U$12:$U$865, TRUE) = $H$4*2, "OK", "OUT")

At the moment the ranges in the two COUNTIFs are static coded from row 12 to 865. I would like to replace the 865 with the value in the cell which displays the total number of used rows on the sheet. Is there a way to insert this dynamic value into the formula?

Regards

Deutz

[davesexcel]

It would be a lot of work to replicate your sheet.

Here is a sample of a sum formula using indirect
=SUM(INDIRECT("Q"&1&":Q"&MATCH(9.999999E+306,Q:Q)))
It will sum all the numbers in column Q
When you add a new number to column Q, it will be added.

If you want to supply a sample workbook, a more direct answer can be given.

[rylo]

Hi

Assuming that your row count formula is in D1 then try

Please Login or Register  to view this content.

rylo

[Deutz]

Thankyou both for your suggestions.

I would supply my workbook but it contains a lot of sensitive data so not an option.

I tried the Offset method suggested and that worked well.

Thanks again
Deutz