I would like to create a list by concatenating two values. The first
value is a 4-letter alpha code selected from a control named
DropDown1, and the other is an incrementing number starting with the
value in cell A9 and ending with A11, always 1 or 2-digit numbers.

Example:
DropDown1 value= ABCD
A9 = 7
A11 = 10

Desired output is a list starting in cell A14:
ABCD07
ABCD08
ABCD09
ABCD10

I've been at this for hours, and don't seem to be on the right track.
Any direction would be appreciated.

Hi
lets assume your drop down (listbox from 'Data - Validation - List')
houses in cell A8 then enter the following in A14:
=TEXT(A9;""""&$A$8&"""00")

in A15 enter
=IF(A14<$A$11,TEXT(A14+1;""""&$A$8&"""00"),"")
and copy down


--
Regards
Frank Kabel
Frankfurt, Germany


Vicki wrote:
I would like to create a list by concatenating two values. The first
value is a 4-letter alpha code selected from a control named
DropDown1, and the other is an incrementing number starting with the
value in cell A9 and ending with A11, always 1 or 2-digit numbers.

Example:
DropDown1 value= ABCD
A9 = 7
A11 = 10

Desired output is a list starting in cell A14:
ABCD07
ABCD08
ABCD09
ABCD10

I've been at this for hours, and don't seem to be on the right track.
Any direction would be appreciated.

Many thanks! I made progress with that. I'd prefer to handle this
procedure with a macro button and VBA code as admin staff will be
creating this list. I run into problems with the IF statement because
of concatenating text and values. Any ideas?


"Frank Kabel" wrote in message ...
Hi
lets assume your drop down (listbox from 'Data - Validation - List')
houses in cell A8 then enter the following in A14:
=TEXT(A9;""""&$A$8&"""00")

in A15 enter
=IF(A14<$A$11,TEXT(A14+1;""""&$A$8&"""00"),"")
and copy down


--
Regards
Frank Kabel
Frankfurt, Germany


Vicki wrote:
I would like to create a list by concatenating two values. The first
value is a 4-letter alpha code selected from a control named
DropDown1, and the other is an incrementing number starting with the
value in cell A9 and ending with A11, always 1 or 2-digit numbers.

Example:
DropDown1 value= ABCD
A9 = 7
A11 = 10

Desired output is a list starting in cell A14:
ABCD07
ABCD08
ABCD09
ABCD10

I've been at this for hours, and don't seem to be on the right track.
Any direction would be appreciated.

Since you wrote dropdown1, I'm guessing that you used a dropdown from the Forms
toolbar on your worksheet.

If yes, this may get you started:

Option Explicit
Sub testme01()

Dim myDD As DropDown
Dim iRow As Long
Dim rng1 As Range
Dim rng2 As Range
Dim destCell As Range
Dim iCtr As Long
Dim myStr As String
Dim okToFill As Boolean

With ActiveSheet
Set myDD = .DropDowns("dropdown1")
Set rng1 = .Range("a9")
Set rng2 = .Range("a11")
Set destCell = .Range("a14")
End With

With myDD
If .Value 0 Then
myStr = .List(.ListIndex)
Else
MsgBox "please make a selection in the dropdown!"
Exit Sub
End If
End With

okToFill = False
If IsNumeric(rng1.Value) _
And IsNumeric(rng2.Value) Then
If rng1.Value < rng2.Value Then
okToFill = True
For iCtr = rng1.Value To rng2.Value
destCell.Value = myStr & Format(iCtr, "00")
Set destCell = destCell.Offset(1, 0)
Next iCtr
End If
End If

If okToFill = False Then
MsgBox "please fix: " & rng1.Address(0, 0) & " and/or " _
& rng2.Address(0, 0)
End If

End Sub

Vicki wrote:

I would like to create a list by concatenating two values. The first
value is a 4-letter alpha code selected from a control named
DropDown1, and the other is an incrementing number starting with the
value in cell A9 and ending with A11, always 1 or 2-digit numbers.

Example:
DropDown1 value= ABCD
A9 = 7
A11 = 10

Desired output is a list starting in cell A14:
ABCD07
ABCD08
ABCD09
ABCD10

I've been at this for hours, and don't seem to be on the right track.
Any direction would be appreciated.

--

Dave Peterson

Beautiful! Works perfectly. This is exactly what I am trying to do.
Thank you so much for the jump-start!


Dave Peterson wrote in message ...
Since you wrote dropdown1, I'm guessing that you used a dropdown from the Forms
toolbar on your worksheet.

If yes, this may get you started:

Option Explicit
Sub testme01()

Dim myDD As DropDown
Dim iRow As Long
Dim rng1 As Range
Dim rng2 As Range
Dim destCell As Range
Dim iCtr As Long
Dim myStr As String
Dim okToFill As Boolean

With ActiveSheet
Set myDD = .DropDowns("dropdown1")
Set rng1 = .Range("a9")
Set rng2 = .Range("a11")
Set destCell = .Range("a14")
End With

With myDD
If .Value 0 Then
myStr = .List(.ListIndex)
Else
MsgBox "please make a selection in the dropdown!"
Exit Sub
End If
End With

okToFill = False
If IsNumeric(rng1.Value) _
And IsNumeric(rng2.Value) Then
If rng1.Value < rng2.Value Then
okToFill = True
For iCtr = rng1.Value To rng2.Value
destCell.Value = myStr & Format(iCtr, "00")
Set destCell = destCell.Offset(1, 0)
Next iCtr
End If
End If

If okToFill = False Then
MsgBox "please fix: " & rng1.Address(0, 0) & " and/or " _
& rng2.Address(0, 0)
End If

End Sub

Vicki wrote:

I would like to create a list by concatenating two values. The first
value is a 4-letter alpha code selected from a control named
DropDown1, and the other is an incrementing number starting with the
value in cell A9 and ending with A11, always 1 or 2-digit numbers.

Example:
DropDown1 value= ABCD
A9 = 7
A11 = 10

Desired output is a list starting in cell A14:
ABCD07
ABCD08
ABCD09
ABCD10

I've been at this for hours, and don't seem to be on the right track.
Any direction would be appreciated.