sum and match on different sheet

    I have a formula that works as intended.

    However when i point it to a different worksheet, it returns data in the sheet where the formula is located.


    formula in sheet1 with data in sheet1

    Code
    =ABS(SUM(INDIRECT(ADDRESS(MATCH("2 mom (4483)",$A$1:$A$37,0),3)):INDIRECT(ADDRESS(MATCH("2 mom (4483)",$A$1:$A$37,0),2+$T$7))))


    formula in sheet2 with data in sheet1

    Code
    =SUM(INDIRECT(ADDRESS(MATCH("2 mom (4483)",Sheet1!$A$1:$A$37,0),3)):INDIRECT(ADDRESS(MATCH("2 Mom (4483)",Sheet1!$A$1:$A$37,0),2+$T$7)))


    I use the formula to get the sum of the matched entry from Jan to whatever month# I put in in cell T7


    Since this formula only seems to work within the sheet you are in, can you recommend a way to do this.


    suminanothersheet.xlsx

    Hello,


    In your sheet2 ... you could use the following formula to get the sum based on the sheet1


    Code
    =SUM(INDIRECT("Sheet1!"&ADDRESS(MATCH("2 mom (4483)",Sheet1!$A$1:$A$37,0),3)):INDIRECT("Sheet1!"&ADDRESS(MATCH("2 Mom (4483)",Sheet1!$A$1:$A$37,0),2+$T$7)))


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    thanks, I did not know you could put a sheet name in front of address function

    Glad you could fix your problem :)


    Thanks for your Thanks

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

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

Register Yourself Login