Concatenate formula did not return expected value

  • Dear All,

    I am using CONCATENATE formula to get the last non-zero cell address in a range. However the main formula does not work although sub-formulas do work fine.

    This is the main formula I wrote:


    OFFSET('Draft Table'!$O$2,0,0,COUNTA(CONCATENATE("'Draft Table'!a2",":",CONCATENATE("a",MATCH('Dashboard (2)'!C21,'Draft Table'!A:A,0)))))

    CONCATENATE section of the formula above works without any problem if I just select that part. But when I add the other sections of the main formula, it did not give me what I want.

    The result of CONCATENATE formula is: 'Draft Table'!a2:a858, which is alright but COUNTA formula does not return 857 (what I expect) though returned 1 as a result.

    I could not find the any solutions to get 857 as the result of COUNTA formula. I would appreciated if you can help me.


  • Re: Concatenate formula did not return expected value

    I think you will need to incorporate the INDIRECT() function to indirectly reference your constructed range.


    [COLOR="#0000FF"]=OFFSET('Draft Table'!$O$2,0,0,COUNTA([COLOR="#FF0000"]INDIRECT[/COLOR](CONCATENATE("'Draft Table'!a2",":",CONCATENATE("a",MATCH('Dashboard (2)'!C21,'Draft Table'!A:A,0))))))[/COLOR]

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!