# Offset function I think

#### jeremypyle

##### Board Regular
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

Last edited:

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### lrobbo314

##### Well-known Member
Don't know how you would do that with a formula.

This VBA will do it though.

Code:
``````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

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

#### lrobbo314

##### Well-known Member
Can you use PowerQuery, or do you need to use a formula for some reason?

#### jeremypyle

##### Board Regular

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

#### dreid1011

##### Well-known Member
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))))

Last edited:

#### jeremypyle

##### Board Regular

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

##### Well-known Member
I may play with it again tomorrow if you don't have a solution.

#### jeremypyle

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

#### jeremypyle

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

Book1
ABCDEF
11
247312
33
44
51
62
73
84
95
106
117
121
132
143
151
Sheet1
Book1
ABCDEF
11
247312
33
44
51
62
73
84
95
106
117
121
132
143
151
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.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,299
Messages
5,853,152
Members
431,549
Latest member
NnAa

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back