=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
Change Formula Based On Cell Value In A1



Re: Change Formula Based On Cell Value In A1
Untested:
=SUMIF(SHIPPED!$A$3:$A$13,A2,INDIRECT("SHIPPED!"&A1&"$3:"&A1&"&""13"))

Re: Change Formula Based On Cell Value In A1
I tried the formula, but get a "Ref" error when I post it.

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"))


Re: Change Formula Based On Cell Value In A1
Kenneth, Please don't put code tags round formulas, only round VBA code.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!