+ Reply to Thread
Results 1 to 4 of 4

Insert cell value as part of address in formula

  1. #1
    Registered User
    Join Date
    02-16-2012
    Location
    Adelaide
    MS-Off Ver
    Excel 2003
    Posts
    42

    Insert cell value as part of address in formula

    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

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,243

    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.

  3. #3
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    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

  4. #4
    Registered User
    Join Date
    02-16-2012
    Location
    Adelaide
    MS-Off Ver
    Excel 2003
    Posts
    42

    Re: Insert cell value as part of address in formula

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1