Create Concatenated List with Incremental Values
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. |
Create Concatenated List with Incremental Values
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. |
Create Concatenated List with Incremental Values
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. |
Create Concatenated List with Incremental Values
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. |
All times are GMT +1. The time now is 07:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com