Forum Discussion

DidierDeBordeaux's avatar
DidierDeBordeaux
Copper Contributor
Dec 26, 2021

Varying Cells Ranges in a Formula, without VBA

Hello. Is there someone to tell me how to Vary Cells Ranges in a Formula, without VBA.

 

For example, I would like to make the Sum of a series of ranges of cells which varies in the formula in function of the column.

 

Like:

  1. Column A, the formula would be "sum(D1:D7)"
  2. Column B, the formula would be "sum(E8:E14)"
  3. ...
  4. Column X, the formula would be "sum(X7n:X7n+6)"
  5. "7n" and "7n+6" for the end of the ranges, because it is for adding a week of data each time.

My idea was to create:

  • on a first line, a range with the varying values of the names of the columns of each range of the sum
  • on a second line, a range with the varying values of the ordinal of the beginning line of each range of the sum
  • on a third line, a range with the varying values of the ordinal of the end line of each range of the sum

I wanted to create formulas like:

  • "sum(concat(A$1;A$2):concat(A$1;A$3))
    • A1 having the name of the column of the sum formula
    • A2 having the ordinal of the first line of the range of cells of the sum formula
    • A3 having the ordinal of the last line of the range of cells of the sum formula
  • that i could copy-paste by pulling the formula across the lines of my spreadsheet

But Excel doesn't accept it 😕 so sorry if I can't give You the example in a existing spreadsheet. 

 

If You are sure it is not possible without VBA, would You be Kind Enough to suggest a model of code I could copy-paste and work with? please

 

Regards,

Didier 

 

 

 

 

 

 

Microsoft® Excel® pour Microsoft 365 MSO (Version 2111 Build 16.0.14701.20254) 64 bits

excel
Formulas and Functions
Macros and VBA

7 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Dec 27, 2021

    DidierDeBordeaux Not entirely sure how your data is organised and where you want to add the SUM formulae, but perhaps the attached workbook helps.

     

    The first example assumes that you have columns with continuous data from which you want to sum specific (smaller) blocks of data. The other assumes that you have smaller blocks of data moving across columns and down for every week. In the latter case, the formula is simple. For the first, it becomes a bit less straight-forward. See if you can get it to work in your own schedule(s).

    • DidierDeBordeaux's avatar
      DidierDeBordeaux
      Copper Contributor
      Dec 28, 2021
      And even don't see where to find the answer. Could tell me please? 😉
      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Dec 28, 2021

        DidierDeBordeaux You wrote "And even don't see where to find the answer." Did you see the file I attached and click in cells A1, B1, G1 and H1?

         

        As for sending a screenshot, use the camera symbol (far right of the picture below) above the area where you write your message.

        If you still don't succeed, send me a direct message (click on my name tag and press Message)

         

         

    • DidierDeBordeaux's avatar
      DidierDeBordeaux
      Copper Contributor
      Dec 28, 2021
      Well, I would like to send You a screenshot, or a pdf attachement, but I don't see how to do! 😛
    • DidierDeBordeaux's avatar
      DidierDeBordeaux
      Copper Contributor
      Dec 28, 2021

      Riny_van_Eekelen Very Thanks for Your Informations. I will study them right now. But I Understand that it was not very Clear for You to know what I Meant or Need! 😛 Regards, Didier 🙂

Resources