Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- Microsoft Excel
- Excel
- Varying Cells Ranges in a Formula, without VBA

- Subscribe to RSS Feed
- Mark Discussion as New
- Mark Discussion as Read
- Pin this Discussion for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Dec 26 2021 02:13 PM

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:

- Column A, the formula would be "sum(D1:D7)"
- Column B, the formula would be "sum(E8:E14)"
- ...
- Column X, the formula would be "sum(X7n:X7n+6)"
- "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*of each range of the sum__beginning__line - on a third line, a range with the varying
*values of the ordinal of the*of each range of the sum__end__line

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

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

Labels:

7 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Dec 26 2021 10:14 PM

@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).

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Dec 27 2021 05:13 PM

@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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Dec 27 2021 05:49 PM

Well, I would like to send You a screenshot, or a pdf attachement, but I don't see how to do!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Dec 27 2021 05:52 PM

And even don't see where to find the answer. Could tell me please?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Dec 27 2021 10:25 PM

@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**)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Dec 28 2021 11:51 AM

"You wrote "And even don't see where to find the answer." Did you see the file I attached " ---> Yes I have seen and open it

"and click in cells A1, B1, G1 and H1?" --> I didn't know I could do that, I will try it

"As for sending a screenshot, use the camera symbol (far right of the picture below) above the area where you write your message." ---> I didn't noticed these tools, I guess I need to "open full text editor".

"and click in cells A1, B1, G1 and H1?" --> I didn't know I could do that, I will try it

"As for sending a screenshot, use the camera symbol (far right of the picture below) above the area where you write your message." ---> I didn't noticed these tools, I guess I need to "open full text editor".

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Dec 28 2021 12:01 PM - edited Dec 28 2021 12:02 PM

""You wrote "And even don't see where to find the answer." Did you see the file I attached "

---> Yes I have seen and open it"

-----> Well; I realise that I had only open the preview! ^_^ ~~ Now I have opened it in Excel and then Yes Indeed, I have seen your 2 kinds of Formulas