Change Formula Based On Cell Value In A1

  • =SUMIF(SHIPPED!$A$3:$A$13,A2,SHIPPED!$B$3:$B$13)

    I would like for the formula to change to what is in A1.. If I change A1 to C, I would like for the formula to replace A with a C in the formula.

    SUMIF(SHIPPED!$A$3:$A$13,A2,SHIPPED!$C$3:$C$13)

    Thanks

  • Re: Change Formula Based On Cell Value In A1


    I get a ref error on the attached. I want to use Shipping column "A" and Column "B" to change the formula.


    By changing this the worksheet will use the desired shipped units. Once I get the formual for shipping, I can easily and quickly adjust the Receiving and Adj formulas.


    What I have is shipping orders which come in and I cut and paste to the next column.


    I need the formula to adjust to the new column.



    Thanks

  • Re: Change Formula Based On Cell Value In A1


    I suggest that you rethink your data layout. It should be in a database format. For each order add a new row.


    Indirect() will not help if you don't have it resolving to point to the correct data. Let's look at the first formula.
    F3's formula: =SUMIF(SHIPPED!$A$3:$A$13,A3,INDIRECT("SHIPPED!"&A1&"$3:"&B1&"&""13"))


    A3:A13 contains "c". This does not match the value of A3 which is "B". There are several other problems. The best method to troubleshoot this kind of thing is to hard code each part and then use string concatenation to build your string to match the hard coded part.


    I will show you "a" formula for F3 but I highly recommend that you follow my first suggestion. You can add some "$" in the string to make the A1 and B1 absolute if needed. The formula gets a bit messy since your are summing from another worksheet.


    =SUMIF(INDIRECT("SHIPPED!"&SHIPPED!A1&"3:"&SHIPPED!A1&"13"),A3,INDIRECT("SHIPPED!"&SHIPPED!B1&"3:"&SHIPPED!B1&"13"))

Participate now!

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