Indirect formula to return text value

    I've got he following formula which sumproduct multiple spreadsheets based where and item reference is found.


    Code
    =IF(M4<>"",SUMPRODUCT(SUMIF(INDIRECT("'"&NBS_LOADINGS&"'!A3:A1000"),J4,INDIRECT("'"&NBS_LOADINGS&"'!E3:E1000"))),"")


    What I'm trying to do is use the above formula "amended" to return a text value. I can only get it to true or false and it will not declare the text


    The idea is that it will only return a text value is the code above =0 value.


    I've tried to upload a but for some reason will not let me.


    Help appreciated

    Re: Indirect formula to return text value


    This formula is used to deliver a summary from sheets that you specyfied in


    NBS_LOADINGS


    so what if you had more than one value to return?
    SUMPRODUCT itself is used to summarized not to index data.
    You need copletly different formula.
    The forum may not allow to post your workbook if you had it in xlmb format.
    But this is just my guess.

    Re: Indirect formula to return text value


    I was considering extending the formula with an additional if statement. Therefor If the formula above returns a Zero value then it would look for a text value and return that. Ideally there would be an order of precedence (The text values would be:- "Incl","Excl", "Omit", "Duplicate"). But at this point in time I would be happy with returning the 1st text value located.


    Still trying to upload sample with no luck.
    Type xlsx
    Macros; Removed
    File size 278b

Participate now!

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

Register Yourself Login