how to automatically update column referencing number from Table

[avk5021]

Let's say I have formula like this "=SUMIFS(pdoplan[Column47],pdoplan[Column9],"BZ"&"*",pdoplan[Column11],$C$26&"*")" which takes data from pdoplan table. Now I cannot figure out how to drag my formula horizontally (on other excel sheet) and for pdoplan[Column47] to keep changing to 48, 49, 50 and so on. Because right I have to change the number manually. So if anyone can help suggesting if there's a way around this or I have to do it manually.
Thanks

 

[Alex Blakenburg]

Try this:

• Select the Cell or Column with the formula.
• Point the mouse cursor on the bottom right corner of the selected area or cell. It wil change to a black cross.
  (I think it is referred to as the fill handle)
• Left click and drag the cross into the next column.
• If you only selected a single cell and you want it in all rows copy paste it to all rows in the column.

 

[avk5021]

Try this:

• Select the Cell or Column with the formula.
• Point the mouse cursor on the bottom right corner of the selected area or cell. It wil change to a black cross.
  (I think it is referred to as the fill handle)
• Left click and drag the cross into the next column.
• If you only selected a single cell and you want it in all rows copy paste it to all rows in the column.

It does change, but it also changes the criteria which refer to other columns as well. Is there a way to fix the criteria columns and changes only the sum column?

 

[Fluff]

You can anchor columns like

=SUMIFS(pdoplan[Column47],pdoplan[[Column9]:[Column9]],"BZ"&"*",pdoplan[[Column11]:[Column11]],$C$26&"*")

 

[Alex Blakenburg]

It does change, but it also changes the criteria which refer to other columns as well. Is there a way to fix the criteria columns and changes only the sum column?

Fluff has given you the answer. If you want to read up on it you can find it here on Mynda's MyOnlineTraining
https://www.myonlinetraininghub.com...xt=How to Make Structured References Absolute
Look for the headings
• Dragging with the Fill Handle
• How to Make Structured References Absolute

 

[avk5021]

You can anchor columns like

=SUMIFS(pdoplan[Column47],pdoplan[[Column9]:[Column9]],"BZ"&"*",pdoplan[[Column11]:[Column11]],$C$26&"*")[/CODGreat
[QUOTE="Fluff, post: 5782682, member: 289073"]
You can anchor columns like[CODE=xls]=SUMIFS(pdoplan[Column47],pdoplan[[Column9]:[Column9]],"BZ"&"*",pdoplan[[Column11]:[Column11]],$C$26&"*")[/
[/QUOTE]

[/QUOTE]

You can anchor columns like

=SUMIFS(pdoplan[Column47],pdoplan[[Column9]:[Column9]],"BZ"&"*",pdoplan[[Column11]:[Column11]],$C$26&"*")

Great Thanks Fluff! it works! Thanks a lot.

 

[avk5021]

Fluff has given you the answer. If you want to read up on it you can find it here on Mynda's MyOnlineTraining
https://www.myonlinetraininghub.com/excel-table-absolute-structured-references#:~:text=How to Make Structured References Absolute
Look for the headings
• Dragging with the Fill Handle
• How to Make Structured References Absolute

Also thanks for the info Alex. Much appreciated.

 

[Alex Blakenburg]

My pleasure.

 

[Fluff]

Glad we could help & thanks for the feedback.