• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Use cell value in a formula

S

Scott Young

New Member
How do I add the text of a given cell to a formula. For example, I have 4 tabs: Jan, Feb, Mar and QTR. In tab QTR, I use the formula =SUMIF(jan!$A$3:$A$4,"test",jan!C3:C4)

I want to replace the "jan" in the formula with the contents of the header cells in the QTR tab. My 3 header cells will have text Jan Feb and Mar that matches the tab names.

In essence, rather than manually changing each formula to:
=SUMIF(jan!$A$3:$A$4,"test",jan!C3:C4)
=SUMIF(feb!$A$3:$A$4,"test",feb!C3:C4)
=SUMIF(mar!$A$3:$A$4,"test",mar!C3:C4)

I want the jan, feb, mar to be filled in by the header cell contents.

Thank you.
 
Scott Young said:
How do I add the text of a given cell to a formula. For example, I have 4 tabs: Jan, Feb, Mar and QTR. In tab QTR, I use the formula =SUMIF(jan!$A$3:$A$4,"test",jan!C3:C4)

I want to replace the "jan" in the formula with the contents of the header cells in the QTR tab. My 3 header cells will have text Jan Feb and Mar that matches the tab names.

In essence, rather than manually changing each formula to:
=SUMIF(jan!$A$3:$A$4,"test",jan!C3:C4)
=SUMIF(feb!$A$3:$A$4,"test",feb!C3:C4)
=SUMIF(mar!$A$3:$A$4,"test",mar!C3:C4)

I want the jan, feb, mar to be filled in by the header cell contents.

Thank you.
Click to expand...
Try this

=SUMIF(INDIRECT(A1&"!$A$3:$A$4"),"test",INDIRECT(A1 &"!C3:C4"))

Now enter Jan or Feb etc in A1
 
You must log in or register to reply here.
Back
Top