CODEWITHSUNDEEP
excel
ShdwKnght333
ShdwKnght333

Reputation: 330

How to make excel only change some cell values when auto filling

I have a formula for calculating a sum based on dates.

=SUMIF('Daily Expense'!A:A,CONCATENATE("2/",B1,"/2017"),'Daily Expense'!B:B)

Now I want all the columns of a row to have the same formula with only 1 change like

=SUMIF('Daily Expense'!A:A,CONCATENATE("2/",C1,"/2017"),'Daily Expense'!B:B)

For the next column and D1 for the next one and so on.

When I use the auto fill method of dragging the mouse from the bottom right it fills the function and changes it to

=SUMIF('Daily Expense'!B:B,CONCATENATE("2/",C1,"/2017"),'Daily Expense'!C:C)

All I want to change is the cell value in the concatenate function not the others.

Any idea how to do this?

Upvotes: 0

Views: 1415

Answers (1)

David
David

Reputation: 1232

A:A is a relative reference which will update as you move the formula. You just need to change it to absolute, like $A:$A:

=SUMIF('Daily Expense'!$A:$A,CONCATENATE("2/",B1,"/2017"),'Daily Expense'!$B:$B)

Now when you autofill it will only update the B1 reference.

Upvotes: 1

Related Questions