SOLVED

Limiting fill values in a formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1393491%22%20slang%3D%22en-US%22%3ELimiting%20fill%20values%20in%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1393491%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20a%20cell%2C%20I%20have%20the%20following%20formula%3A%26nbsp%3B%3D(B2*E1%2BB2)-G1.%20I%20want%20to%20autofull%20by%20dragging%20that%20cell%20downwards%2C%20but%20I%20only%20want%20the%20B2's%20to%20auto%20increase%2C%20while%20keeping%20E1%20and%20G1%20the%20same.%26nbsp%3B%20For%20example%2C%20instead%20of%20%3D(B3*E2%2BB3)-G2%2C%20then%26nbsp%3B%3D(B4*E3%2BB4)-G3%2C%20etc.%2C%20when%20what%20I%20want%20is%26nbsp%3B%3D(B3*E1%2BB3)-G1%2C%20then%26nbsp%3B%3D(B4*E1%2BB4)-G1%20etc.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1393491%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-1393505%22%20slang%3D%22en-US%22%3ERe%3A%20Limiting%20fill%20values%20in%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1393505%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F259254%22%20target%3D%22_blank%22%3E%40ala6xxxx%3C%2FA%3E%26nbsp%3BThat%20would%20be%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D(B2*%24E%241%2BB2)-%24G%241%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOoops%3A%202%20minutes%20too%20late%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1393502%22%20slang%3D%22en-US%22%3ERe%3A%20Limiting%20fill%20values%20in%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1393502%22%20slang%3D%22en-US%22%3E%3D(B2*%24E%241%2BB2)-%24G%241%3CBR%20%2F%3EThen%2C%20you%20drag%20down%20using%20auto%20fill%20handle%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EThat%20will%20be%20your%20formula%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1393712%22%20slang%3D%22en-US%22%3ERe%3A%20Limiting%20fill%20values%20in%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1393712%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421698%22%20target%3D%22_blank%22%3E%40Abiola1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWorks%20perfectly.%20Thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1393732%22%20slang%3D%22en-US%22%3ERe%3A%20Limiting%20fill%20values%20in%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1393732%22%20slang%3D%22en-US%22%3EGlad%20to%20hwlp%3CBR%20%2F%3E%3CBR%20%2F%3ESo%20stay%20safe%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

In a cell, I have the following formula: =(B2*E1+B2)-G1. I want to autofull by dragging that cell downwards, but I only want the B2's to auto increase, while keeping E1 and G1 the same.  For example, instead of =(B3*E2+B3)-G2, then =(B4*E3+B4)-G3, etc., when what I want is =(B3*E1+B3)-G1, then =(B4*E1+B4)-G1 etc. 

4 Replies
Highlighted
Best Response confirmed by ala6xxxx (New Contributor)
Solution
=(B2*$E$1+B2)-$G$1
Then, you drag down using auto fill handle


That will be your formula
Highlighted

@ala6xxxx That would be

 

=(B2*$E$1+B2)-$G$1

 

Ooops: 2 minutes too late

Highlighted

@Abiola1 

Works perfectly. Thanks!

Highlighted
Glad to hwlp

So stay safe