# Excel formula to use cell as text reference

• ### 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

• 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 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 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