0

I'm looking for assistance with a problem i'm having when using Excel. The best way to explain the problem i'm encountering is that i'm using the auto-fill drag down to copy and paste essentially a formula.. I'm currently using this function on sheet 1:

 =sum('Sheet2'!E2*Sheet3!B3+Sheet3!B2)

Now, when dragging down the formula it changes which is expected, but not my intended results. The formula changes to the following:

=sum('Sheet2'!E3*Sheet3!B4+Sheet3!B3)
=sum('Sheet2'!E4*Sheet3!B5+Sheet3!B4)

My intended result is to have one reference cell change, but keep the last two in the formula the same reference as such:

 =sum('Sheet2'!E2*Sheet3!B3+Sheet3!B2)
 =sum('Sheet2'!E3*Sheet3!B3+Sheet3!B2)
 =sum('Sheet2'!E4*Sheet3!B3+Sheet3!B2)
 =sum('Sheet2'!E5*Sheet3!B3+Sheet3!B2)

With the intended first row populated with the formulas:

=sum('Sheet2'!E2*Sheet3!B3+Sheet3!B2)
=sum('Sheet2'!F2*Sheet3!C3+Sheet3!C2)   
=sum('Sheet2'!G2*Sheet3!D3+Sheet3!D2)   
=sum('Sheet2'!H2*Sheet3!E3+Sheet3!E2)   
=sum('Sheet2'!I2*Sheet3!F3+Sheet3!F2)   
=sum('Sheet2'!J2*Sheet3!G3+Sheet3!G2)   
=sum('Sheet2'!K2*Sheet3!H3+Sheet3!H2)   
=sum('Sheet2'!L2*Sheet3!I3+Sheet3!I2)

as the drag down formula changes from row to row, the last two references need to remain the same on sheet 3, but sheet 2 change as the row increases, so

'Sheet2'!E3
'Sheet2'!E4
'Sheet2'!E5
'Sheet2'!E6

closed as off-topic by DavidPostill Dec 5 '18 at 21:21

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "This question is not about computer hardware or software, within the scope defined in the help center." – DavidPostill
If this question can be reworded to fit the rules in the help center, please edit the question.

1

You need to add the $ symbol before the portions that you don't want to change, such as:

=sum('Sheet2'!E2*Sheet3!B$3+Sheet3!B$2)
  • Note, sometimes these are referred to as fixed references. However if you delete or add rows on those sheets, Excel will adjust them. If you really want to fix them permanently, then you need to use INDIRECT. – Rey Juna Aug 7 '18 at 18:19

Not the answer you're looking for? Browse other questions tagged or ask your own question.