none
Excel formula to use cell as text reference RRS feed

  • Question

  • I have this formula: =SUMIFS(Loan!$P:$P,Loan!$A:$A,">0"&'Monthly Breakdown'!$B$3,Loan!$A:$A,"<="&EOMONTH('Monthly Breakdown'!$B$3,0))

    which sums column P in Sheet "Loan" on a certain condition. There are multiple sheets in the workbook.

    I would like to have a single cell, "A1" linked to the formula, where I can input the name of the worksheet. The formula will then reference that specific worksheet.

    I have tried with & "A1" but I cannot get it to work

    Thank you,

    CC

    Saturday, February 2, 2019 9:24 AM

Answers

  • You can use the INDIRECT function for this:

    =SUMIFS(INDIRECT("'"&A1&"'!$P:$P"),INDIRECT("'"&A1&"'!$A:$A"),">0"&'Monthly Breakdown'!$B$3,INDIRECT("'"&A1&"'!$A:$A"),"<="&EOMONTH('Monthly Breakdown'!$B$3,0))


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by czap1 Saturday, February 2, 2019 11:06 AM
    Saturday, February 2, 2019 9:47 AM

All replies

  • You can use the INDIRECT function for this:

    =SUMIFS(INDIRECT("'"&A1&"'!$P:$P"),INDIRECT("'"&A1&"'!$A:$A"),">0"&'Monthly Breakdown'!$B$3,INDIRECT("'"&A1&"'!$A:$A"),"<="&EOMONTH('Monthly Breakdown'!$B$3,0))


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by czap1 Saturday, February 2, 2019 11:06 AM
    Saturday, February 2, 2019 9:47 AM
  • Thank you again for your kind assistance

    CC

    Saturday, February 2, 2019 11:06 AM