# Insert cell value as part of address in formula

1. ## Insert cell value as part of address in formula

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  Register To Reply

2. ## Re: Insert cell value as part of address in formula

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.  Register To Reply

3. ## Re: Insert cell value as part of address in formula

Hi

Assuming that your row count formula is in D1 then try ``Please Login or Register  to view this content.``
rylo  Register To Reply

4. ## Re: Insert cell value as part of address in formula

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  Register To Reply