Use OFFSET to add cells in a row and ending at a specific point

[fddekker]

I want to add cells, always starting in a particular column, but end at a different column for each row, according to a pre-determined factor.

Below is an example:

http://s1300.photobucket.com/user/fddekker/media/Offset_zps4c8eaxk0.png.html?sort=3&o=0

How can I use the offset function in a formula to determine the last point and not the starting point.

(Any reason I can't post a photobucket image in the thread?)

Thanks in advance

 

[Joe4]

You cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

 

[fddekker]

Thanks for that. I have not posted in a while and was unaware of the new tools. Note that the link to the video is down.

So here is the example:

Book1
	A	B	C	D	E	F	G	H
1		Heading 1	Heading 2	Heading 3	Heading 4	Heading 5	Offset	Total
2	Amount 1	10	10	10	10	10	1	40
3	Amount 2	10	10	10	10	10	4	10
4	Amount 3	10	10	10	10	10	2	30
5	Amount 4	10	10	10	10	10	3	20
6	Amount 5	10	10	10	10	10	1	40
Sheet1

I would like to add each row, but the last column should be dynamic, based on the number in column G (in this example). I have tried using the OFFset functionality and the R1C1 reference method, but must be doing something wrong with both.

Thanks!!

 

[Joe4]

So, walk me through this.
Are we adding to the yellow cells or the white cells?
Are we always adding 10 to each cell? If not, how do we determine what we are adding in each cell?
Are we populating and incrementing the value in column A?
Where are we getting the value to Offset by? Is that being input from somewhere?

 

[fddekker]

Joe, sorry I was not clearer:

Are we adding to the yellow cells or the white cells?
We need to add the yellow cells. All the rows will have the same number of entries, but depending on the number entered in column G, the number of columns should be added. So, Column G's entry tells me how many columns NOT to add. The correct totals are in column H.

Are we always adding 10 to each cell? If not, how do we determine what we are adding in each cell?
No, not always 10; It contains data downloaded from another program.

Are we populating and incrementing the value in column A?
No, column A was just my description. It could be anything. But do note that the data will not always start in column B. (I want to do the same addition on several sheets, and the blocks of data is not always in the same columns)

Where are we getting the value to Offset by? Is that being input from somewhere?
The OFFset is calculated according to a formula on another set of data. For this exercise, assume that it is given and will be in a cell towards the right of the data that should be summed.

Hope it helps!

 

[Joe4]

I got it now. I totally misunderstood, and thought you were talking about adding cells or rows and populating them.

Try this formula in H2 and copy down:

=SUM(B2:OFFSET(B2,0,4-G2))

 

[fddekker]

Perfecto! I just could not get the syntax right.
Thanks!

 

[Joe4]

You are welcome!
Glad I was able to help.