MobileMobile | Continue

Excel Bar

Excel Bar

Your excel questions will be responsed by our excel experts within 24hrs.Our service is free.

 Forgot Pass?
 Register Now
Find
Hot Search: Vlookup Match VBA
View: 260|Reply: 4

Populating a range of cell, based on 2 values

[Copy link]

1

Topics

2

Posts

7

Integral

Newbie

Rank: 1

Integral
7
Post on 2-11-2017 17:04:14 | All posts |Read mode
I have searched old threads but came up empty, so I am putting up my query here. I need to populate a range of cells, e.g., from C1 to Cx, based on the values of cells A1 and B1.
For example, A1 = John Doe (it will be the name of a person); and B1 = 12. I would like to have John Doe copied 12 times, from C1 to C12.
Thank you for your help.
Reply

Props Report

5

Topics

677

Posts

1523

Integral

King

Rank: 6Rank: 6

Integral
1523
Post on 2-11-2017 19:41:24 | All posts



Try this
Enter formula in C1 and copy down


  



      =INDEX(A$1:A1,ROUNDUP(ROWS(B$1:B1)/B$1,0))
      
      
vABC
1
John Doe

12

John Doe
2
John J Deer

John Doe
3
John Doe
4
John Doe
5
John Doe
6
John Doe
7
John Doe
8
John Doe
9
John Doe
10
John Doe
11
John Doe
12
John Doe
13
John J Deer
14
John J Deer
15
John J Deer
16
John J Deer
17
John J Deer
18
John J Deer
19
John J Deer
20
John J Deer
21
John J Deer
22
John J Deer
23
John J Deer
24
John J Deer
25













Reply Support Opposition

Props Report

8

Topics

1046

Posts

2284

Integral

King

Rank: 6Rank: 6

Integral
2284
Post on 2-11-2017 20:44:27 | All posts



For i = 1 To Range(B1).Value
     Range(C  i).Value = Range(A1).Value
Next iOr just
Range(C1:C  Range(B1).Value) = Range(A1).Value











Reply Support Opposition

Props Report

1

Topics

2

Posts

7

Integral

Newbie

Rank: 1

Integral
7
 Author| Post on 2-11-2017 21:35:00 | All posts



Thank you very much, I knew there was a way to do it. Now I just need to bone up on the functions you used. Gracias!




Reply Support Opposition

Props Report

1

Topics

83

Posts

183

Integral

Conqueror

Rank: 3Rank: 3

Integral
183
Post on 2-11-2017 22:58:43 | All posts



One of the most useful but, probably, under utilized tools in Excel is the Evaluate Formula Tool.
Using it will help you see how Excel evaluates each argument in a function.
Select the cell with the formula then go to the Ribbon Menu  Formulas  Evaluate Formula to activate the tool.
While this tool is very useful, one must understand the required arguments in the formula syntax to make full sense of the results observed when using it.
Reply Support Opposition

Props Report

Points policy of this forum

Archiver|Mobile|Small dark house|Contact us|Excel Bar

GMT-5, 9-21-2017 14:33 , Processed in 0.150790 second(s), 20 queries .

Powered by Discuz! X3

© 2001-2013 Comsenz Inc.

!fastreply! Top !return_list!