Offset function I think

[jeremypyle]

I have the following values in cells:

b2:4
c2:7
d2:3
e2:1


I want to put a formula in cell f1 that I can drag down that will do the following:

f1:1
f2:2
f3:3
f4:4
f5:1
f6:2
f7:3
f8:4
f9:5
f10:6
f11:7
f12:1
f13:2
f14:3
f15:1


How would I do this? I think I would use offset formula somehow. It may not be offset. Please let me know what formula to put in f1:

Thanks so much

 

[lrobbo314]

Don't know how you would do that with a formula.

This VBA will do it though.

Sub CountOut()
Dim AR() As Variant: AR = Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row).Value
Dim sRow As Long: sRow = 2


For i = 1 To UBound(AR)
    For j = 1 To AR(i, 1)
        Cells(sRow, 3).Value = j
        sRow = sRow + 1
    Next j
Next i


End Sub

 

[jeremypyle]

Thanks for your help. I is formula though that I am looking for rather than VBA

 

[lrobbo314]

Can you use PowerQuery, or do you need to use a formula for some reason?

 

[jeremypyle]

I will need a formula as this is part of another formula

 

[dreid1011]

You could try this... though if your example is only a partial of your actual data, then this could become too big/cumbersome as t' written very specifically to the data you have shown.

I just wanted to see if I could get the results you show with a formula... In F1 and copy down.

=IF(ROW()<=$B$2,ROW(),IF((ROW()-$B$2)<=$C$2,ROW()-$B$2,IF((ROW()-$B$2-$C$2)<=$D$2,ROW()-$B$2-$C$2,IF((ROW()-$B$2-$C$2-$D$2)<=$E$2,ROW()-$B$2-$C$2-$D$2,0))))

 

[jeremypyle]

That does work. However if I get more columns, it is going to become quite a long formula. Would like to know if there is an easier way of doing. I can't seem to figure it out though. Thanks so much for your help though. I quite often have a similar type of problem as this one so wanting to learn the most efficient way of solving this without too long of a formula. Very appreciate your help though

 

[dreid1011]

I may play with it again tomorrow if you don't have a solution.

 

[jeremypyle]

awesome thank you so much. Look forward to hearing from you tomorrow if I haven't worked it out yet

 

[jeremypyle]

Here is an excel sample of what I am trying to do:

Book1
	A	B	C	D	E	F
1						1
2		4	7	3	1	2
3						3
4						4
5						1
6						2
7						3
8						4
9						5
10						6
11						7
12						1
13						2
14						3
15						1
Sheet1

Book1
	A	B	C	D	E	F
1						1
2		4	7	3	1	2
3						3
4						4
5						1
6						2
7						3
8						4
9						5
10						6
11						7
12						1
13						2
14						3
15						1
Sheet1

Since the number 4 is in b2, I want the first 4 cells in column F to be 1,2,3,4.
Since the number 7 is in c2, I want the next 7 cells in column F to display 1,2,3,4,5,6,7
Since the number 3 is in d2, I want the next 3 cells to display 1,2,3
Since the number 1 is in e2, I want the next cell to display 1

Please note that there might be more columns than this. For example, I might put the formula into cell z1 and then drag the formula down in z.