Forum Discussion

hcikla's avatar
hcikla
Copper Contributor
Feb 14, 2022

How to move formula

I have formula example as below: 

=SOM.ALS($M$5:$M$19;$AK$4;$Z$5:$Z$19)+SOM.ALS($M$5:$M$19;$AK$5;$Z$5:$Z$19)+SOM.ALS($M$5:$M$19;$AK$6;$Z$5:$Z$19)+SOM.ALS($M$5:$M$19;$AK$7;$Z$5:$Z$19)+SOM.ALS($M$5:$M$19;$AK$8;$Z$5:$Z$19)+SOM.ALS($M$5:$M$19;$AK$9;$Z$5:$Z$19)+SOM.ALS($M$5:$M$19;$AK$10;$Z$5:$Z$19)+SOM.ALS($M$5:$M$19;$AK$11;$Z$5:$Z$19)+SOM.ALS($M$5:$M$19;$AK$12;$Z$5:$Z$19)+SOM.ALS($M$5:$M$19;$AK$13;$Z$5:$Z$19)+SOM.ALS($M$5:$M$19;$AK$14;$Z$5:$Z$19)

 

When I want to move this formula (see above) to different cell with my mouse, I cant select all formulas (above) to move. I have to move every formula one by one to different cell. Is there option to select all the formulas and just move one time all of them? 

 

Formulas and Functions

9 Replies

  • JMB17's avatar
    JMB17
    Bronze Contributor
    Feb 15, 2022
    If you want the range references to change as you copy the formula down the column, then you could omit the $ in front of the row references (the $ fixes the row/column references). Also, I think you could shorten your formula to

    =SOM(SOM.ALS($M5:$M19; $AK$4:$AK$14; $Z5:$Z19))

    If you don't have office 365, then you would need to hit Ctrl+Shift+Enter after keying/copying the formula to the formula bar, or use sumproduct instead of the sum function.
    • hcikla's avatar
      hcikla
      Copper Contributor
      Feb 14, 2022
      deze formule bijvoorbeeld:
      =SOM.ALS($M$5:$M$19;$AK$4;$Z$5:$Z$19)+SOM.ALS($M$5:$M$19;$AK$5;$Z$5:$Z$19)+SOM.ALS($M$5:$M$19;$AK$6;$Z$5:$Z$19)+SOM.ALS($M$5:$M$19;$AK$7;$Z$5:$Z$19)+SOM.ALS($M$5:$M$19;$AK$8;$Z$5:$Z$19)+SOM.ALS($M$5:$M$19;$AK$9;$Z$5:$Z$19)+SOM.ALS($M$5:$M$19;$AK$10;$Z$5:$Z$19)+SOM.ALS($M$5:$M$19;$AK$11;$Z$5:$Z$19)+SOM.ALS($M$5:$M$19;$AK$12;$Z$5:$Z$19)+SOM.ALS($M$5:$M$19;$AK$13;$Z$5:$Z$19)+SOM.ALS($M$5:$M$19;$AK$14;$Z$5:$Z$19)
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Feb 14, 2022

        hcikla 

        Wat moet je één voor één verplaatsen, en wat gaat er mis als je de hele formule in één keer verplaatst?

    • hcikla's avatar
      hcikla
      Copper Contributor
      Feb 14, 2022
      Hallo Hans, Als ik de formule één of meer regels naar beneden wil verplaatsen, dan moet ik ze één voor één verplaatsen. Bestaat er geen optie in excel om alles één keer te verplaatsen?

      Hi Hans, If I want to move the formula down one or more lines, I have to move them one by one. Is there no option in Excel to move everything once?

Resources