Is there a way I can use a number in a cell to fill in that number of
cells with a given value? For example, is there a way that I could
enter this kind of information:

Number blue 3
Number red 2

And get this returned to me:

Blue
Blue
Blue
Red
Red

Any suggestions would be appreciated!


--
Maven
------------------------------------------------------------------------
Maven's Profile: http://www.excelforum.com/member.php...o&userid=37826
View this thread: http://www.excelforum.com/showthread...hreadid=573870

Is there a way I can use a number in a cell to fill in that number of
cells with a given value? For example, is there a way that I could
enter this kind of information:

Number blue 3
Number red 2

And get this returned to me:

Blue
Blue
Blue
Red
Red

Maybe the CSV file below would help. The independent variable numbers are
in column D.

-------------------- cut here --------------------
"=VLOOKUP(ROW()-1,B:D,2,TRUE)",0,blue,3
"=VLOOKUP(ROW()-1,B:D,2,TRUE)",=B1+D1,red,2
"=VLOOKUP(ROW()-1,B:D,2,TRUE)",=B2+D2,green,6
"=VLOOKUP(ROW()-1,B:D,2,TRUE)",=B3+D3,orange,5
"=VLOOKUP(ROW()-1,B:D,2,TRUE)",=B4+D4,"=""""",
"=VLOOKUP(ROW()-1,B:D,2,TRUE)",,,
"=VLOOKUP(ROW()-1,B:D,2,TRUE)",,,
"=VLOOKUP(ROW()-1,B:D,2,TRUE)",,,
"=VLOOKUP(ROW()-1,B:D,2,TRUE)",,,
"=VLOOKUP(ROW()-1,B:D,2,TRUE)",,,
"=VLOOKUP(ROW()-1,B:D,2,TRUE)",,,
"=VLOOKUP(ROW()-1,B:D,2,TRUE)",,,
"=VLOOKUP(ROW()-1,B:D,2,TRUE)",,,
"=VLOOKUP(ROW()-1,B:D,2,TRUE)",,,
"=VLOOKUP(ROW()-1,B:D,2,TRUE)",,,
"=VLOOKUP(ROW()-1,B:D,2,TRUE)",,,
"=VLOOKUP(ROW()-1,B:D,2,TRUE)"
"=VLOOKUP(ROW()-1,B:D,2,TRUE)"
"=VLOOKUP(ROW()-1,B:D,2,TRUE)"
"=VLOOKUP(ROW()-1,B:D,2,TRUE)"
"=VLOOKUP(ROW()-1,B:D,2,TRUE)"
"=VLOOKUP(ROW()-1,B:D,2,TRUE)"
"=VLOOKUP(ROW()-1,B:D,2,TRUE)"
"=VLOOKUP(ROW()-1,B:D,2,TRUE)"
"=VLOOKUP(ROW()-1,B:D,2,TRUE)"
"=VLOOKUP(ROW()-1,B:D,2,TRUE)"

If i understand correctly?

You have a drop down list that populates based upon cell values

1. place a combobox on the spreadsheet

2. assume that cell C8 is for blue value & C9 for red value

in the VBE under Private Sub Worksheet_SelectionChange(ByVal Target As
Range) paste this code


Dim BlueNo, RedNo
ComboBox1.Clear


BlueNo = Range("C8").Value
RedNo = Range("C9").Value

For i = 1 To BlueNo
ComboBox1.AddItem ("Blue")
Next i


For i = 1 To RedNo
ComboBox1.AddItem ("Red")
Next i


"Maven" wrote:


Is there a way I can use a number in a cell to fill in that number of
cells with a given value? For example, is there a way that I could
enter this kind of information:

Number blue 3
Number red 2

And get this returned to me:

Blue
Blue
Blue
Red
Red

Any suggestions would be appreciated!


--
Maven
------------------------------------------------------------------------
Maven's Profile: http://www.excelforum.com/member.php...o&userid=37826
View this thread: http://www.excelforum.com/showthread...hreadid=573870

Another method, assuming that your colour is in column B from B1
downwards, and the number of required occurances is n column C from C1
downwards, using columns E F and G to create your list in column E:

in E1 put:
=INDIRECT("b"&G1)

in F1 put:
=SUM(C$1:C1)

in G1 put a number 1
in G2 put:
=IF(ROW()INDIRECT("f"&G1),G1+1,G1)

and formula drag columns E F and G to cover the required total of
entries.

when the list is complete you can copy column E and Paste Special =
Values then remove any non-required columns.

HTH




Maven Wrote:
Is there a way I can use a number in a cell to fill in that number of
cells with a given value? For example, is there a way that I could
enter this kind of information:

Number blue 3
Number red 2

And get this returned to me:

Blue
Blue
Blue
Red
Red

Any suggestions would be appreciated!


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=573870

This has been plaguing me. It will take some playing around to see
which solution is ideal for my project, but you've all been a huge
help.

Thanks!


--
Maven
------------------------------------------------------------------------
Maven's Profile: http://www.excelforum.com/member.php...o&userid=37826
View this thread: http://www.excelforum.com/showthread...hreadid=573870

Sorry Maven
I misread your question.

If you dont want a Dropdown list but want to populate cells with the colour
values without having to type in and copy a formula down the sheet. you
could use the code I sent before but with the following changes:

Again C8=BLUE & C9=RED
List starts at say D9
B8 ="BLUE"
B9="RED"

--------------------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim BlueNo, RedNo, count

Columns("D:D").ClearContents


BlueNo = Range("C8").Value - 1
RedNo = Range("C9").Value - 1
count = 0

For i = 1 To BlueNo
Range("D9").Offset(count, 0).Value = Range("B8").Value
count = count + 1
Next i


For i = 1 To RedNo
Range("D9").Offset(count, 0).Value = Range("B9").Value
count = count + 1
Next i


End Sub
---------------------------------------------------------------------------


This will give you an unlimited list length within excels limits


"Maven" wrote:


Is there a way I can use a number in a cell to fill in that number of
cells with a given value? For example, is there a way that I could
enter this kind of information:

Number blue 3
Number red 2

And get this returned to me:

Blue
Blue
Blue
Red
Red

Any suggestions would be appreciated!


--
Maven
------------------------------------------------------------------------
Maven's Profile: http://www.excelforum.com/member.php...o&userid=37826
View this thread: http://www.excelforum.com/showthread...hreadid=573870

scottfoxall Wrote:
If you dont want a Dropdown list but want to populate cells with the
colour
values without having to type in and copy a formula down the sheet.
you
could use the code I sent before but with the following changes:

I'm trying out all the suggestions, but I'm having trouble with this
one. I have the code in, but can you tell me how I should go about
using the code? Should I create a macro with this code, or is there
another way to go about this? Sorry - I'm still learning!


--
Maven
------------------------------------------------------------------------
Maven's Profile: http://www.excelforum.com/member.php...o&userid=37826
View this thread: http://www.excelforum.com/showthread...hreadid=573870

You can run this code whenever the is a change in a value made on the
spreadsheet.

EG:

Say your list is on sheet1

Press (Alt+F11)

You are now in the VBE (Visual Basic Editor)

You should see on the left VBAProject1 "#####"
Where #### is your file name

make sure this is expanded out so you can see:
Worksheet1("Sheet1")
Worksheet2("Sheet2")
Worksheet3("Sheet3")
ThisWorkbook

If you want to run the code on the event of change value in sheet1:

Double click Sheet1

You are now in the code window. This should already be on the event value
change i.e:
---------------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)


End Sub
------------------------------------------------------------------------

If not, then there are two pull downs at the top of the code window. If you
see "General" change this to Worksheet. Now you should see the above? If
not select it from the list of events at the top right of the code window.

Paste the code in between the above statements. eg

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'********PUT THE CODE HERE*****

End Sub

Hope that helps?

"Maven" wrote:


scottfoxall Wrote:
If you dont want a Dropdown list but want to populate cells with the
colour
values without having to type in and copy a formula down the sheet.
you
could use the code I sent before but with the following changes:

I'm trying out all the suggestions, but I'm having trouble with this
one. I have the code in, but can you tell me how I should go about
using the code? Should I create a macro with this code, or is there
another way to go about this? Sorry - I'm still learning!


--
Maven
------------------------------------------------------------------------
Maven's Profile: http://www.excelforum.com/member.php...o&userid=37826
View this thread: http://www.excelforum.com/showthread...hreadid=573870

CODE EXPLAINED:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'*******THIS DEFINES THE VARIABLES*******
Dim BlueNo, RedNo, count

'*******THIS CLEARS THE LIST ASSUMING ITS ALL IN COLUMN D*******
Columns("D:D").ClearContents

'*******THIS GETS THE VALUES FROM THE SPREADSHEET RANGE MEANS CELL******
BlueNo = Range("C8").Value
RedNo = Range("C9").Value

'*******THIS SETS A COUNTER TO 0******
count = 0

'******THIS WILL LOOP AROUND THE CODE BLUENO OF TIME (THE VALUE YOU HAVE FOR
BLUE)
'******IT PUTS THE VALUE IN RANGE D9 BUT OFFSET BY THE VALUE OF COUNT WHICH
'******INCREASES BY ONE ON EACH LOOP
'******FIRST LOOP PUTS THE VALUE OF B8 WHICH IS "BLUE" IN RANGE/CELL D9
'******SECOND LOOP PUTS THE "BLUE" INTO D9 OFFSET(COUNT,0) WHICH IS CELL D10
'******OFFSET(Y,X) ON EACH LOOP Y ("COUNT") INCREASES BY 1
For i = 1 To BlueNo
Range("D9").Offset(count, 0).Value = Range("B8").Value
count = count + 1
Next i

'******SAME AS ABOVE BUT FOR RED VALUES
For i = 1 To RedNo
Range("D9").Offset(count, 0).Value = Range("B9").Value
count = count + 1
Next i


End Sub


"Maven" wrote:


scottfoxall Wrote:
If you dont want a Dropdown list but want to populate cells with the
colour
values without having to type in and copy a formula down the sheet.
you
could use the code I sent before but with the following changes:

I'm trying out all the suggestions, but I'm having trouble with this
one. I have the code in, but can you tell me how I should go about
using the code? Should I create a macro with this code, or is there
another way to go about this? Sorry - I'm still learning!


--
Maven
------------------------------------------------------------------------
Maven's Profile: http://www.excelforum.com/member.php...o&userid=37826
View this thread: http://www.excelforum.com/showthread...hreadid=573870