SOLVED

replace part of formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1512359%22%20slang%3D%22en-US%22%3Ereplace%20part%20of%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1512359%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20formula%20like%20this%3C%2FP%3E%3CP%3E%3DSUMPRODUCT(LOOKUP(MID(%3CSTRONG%3EB2%3C%2FSTRONG%3E%2CROW(INDIRECT(%221%3A%22%26amp%3BLEN(%3CSTRONG%3EB2%3C%2FSTRONG%3E)))%2C1)%2CP1%3AP22%2CO1%3AO22))%3C%2FP%3E%3CP%3E%3DSUMPRODUCT(LOOKUP(MID(%3CSTRONG%3EB3%3C%2FSTRONG%3E%2CROW(INDIRECT(%221%3A%22%26amp%3BLEN(%3CSTRONG%3EB3%3C%2FSTRONG%3E)))%2C1)%2CP1%3AP22%2CO1%3AO22))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhat%20i%20went%20is%20copy%20this%26nbsp%3Bformula%20%26nbsp%3Bto%20many%20lines%20and%20change%20the%20%3CSTRONG%3EB2%3C%2FSTRONG%3E%20by%20incremental%20like%3CSTRONG%3E%20b3%2Cb4%2Cb5%3C%2FSTRONG%3E%20but%20leave%20%26nbsp%3Bthis%20part%20%3CFONT%20color%3D%22%23000080%22%3E%3CEM%3E%3CU%3EP1%3AP22%2CO1%3AO22%3C%2FU%3E%3C%2FEM%3E%3C%2FFONT%3E%20the%20same%20for%20all%20records%20%26nbsp%3B%3C%2FP%3E%3CP%3Ecan%20anyone%20help%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1512359%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1512498%22%20slang%3D%22en-US%22%3ERe%3A%20replace%20part%20of%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1512498%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F723849%22%20target%3D%22_blank%22%3E%40hershel13%3C%2FA%3E%26nbsp%3B%20The%20use%20of%20the%20symbol%20%22%24%22%20before%20a%20column%20letter%20or%20row%20number%20will%20%22lock%22%20it%20(make%20it%20absolute%20reference)%26nbsp%3B%20without%20that%20%22%24%22%20it%20is%20a%20relative%20reference.%26nbsp%3B%20Try%20this%3A%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DSUMPRODUCT(LOOKUP(MID(%3C%2FSPAN%3E%3CSTRONG%3EB2%3C%2FSTRONG%3E%3CSPAN%3E%2CROW(INDIRECT(%221%3A%22%26amp%3BLEN(%3C%2FSPAN%3E%3CSTRONG%3EB2%3C%2FSTRONG%3E%3CSPAN%3E)))%2C1)%2C%24P%241%3A%24P%2422%2C%24O%241%3A%24O%2422))%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have formula like this

=SUMPRODUCT(LOOKUP(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1),P1:P22,O1:O22))

=SUMPRODUCT(LOOKUP(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1),P1:P22,O1:O22))

 

what i went is copy this formula  to many lines and change the B2 by incremental like b3,b4,b5 but leave  this part P1:P22,O1:O22 the same for all records  

can anyone help ?

1 Reply
Best Response confirmed by hershel13 (New Contributor)
Solution

@hershel13  The use of the symbol "$" before a column letter or row number will "lock" it (make it absolute reference)  without that "$" it is a relative reference.  Try this:

=SUMPRODUCT(LOOKUP(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1),$P$1:$P$22,$O$1:$O$22))