Offset function I think

jeremypyle

Board Regular
Joined
May 30, 2011
Messages
166
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
Joined
Jul 14, 2008
Messages
3,464
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 30, 2011
Messages
166
Thanks for your help. I is formula though that I am looking for rather than VBA
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,464
Office Version
  1. 365
Platform
  1. Windows
Can you use PowerQuery, or do you need to use a formula for some reason?
 

jeremypyle

Board Regular
Joined
May 30, 2011
Messages
166

ADVERTISEMENT

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

dreid1011

Well-known Member
Joined
Jun 4, 2015
Messages
2,136
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
Joined
May 30, 2011
Messages
166

ADVERTISEMENT

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 :)
 

jeremypyle

Board Regular
Joined
May 30, 2011
Messages
166
awesome thank you so much. Look forward to hearing from you tomorrow if I haven't worked it out yet
 

jeremypyle

Board Regular
Joined
May 30, 2011
Messages
166
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.
 
Master Excel Bundle

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.

Forum statistics

Threads
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?

Disable AdBlock

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
Top