Function to list names dynamically based on Qty

[rimmidi]

Hi,

I have interesting problem to solve and am looking for some help regarding the same.

I have three columns. Product, Model Qty, Model. Each Product has Quantity listed beside. There will be a default Qty as 2 and the Models named after the product and qty are to be displayed. If I change the Qty to 3 or 4, then I would like the Models to increase accordingly and vice-versa. Also would like to sort the list of models as listed the product column. I have attached a sample excel sheet. Please help.

Thanks,
Ranga

[rimmidi]

Can anybody help me on this request ?

[rimmidi]

Any help..guys ?

[dredwolf]

Be a little bit patient please at least 9 members have looked at your file and are thinking about it, not sure how long you have been working on it, but it seems a sure bet that it has been longer than the people who have looked at your sample..

[dredwolf]

Also...is the "product list" going to be organized 'Alphabetically', as in your example?

[:) Sixthsense :)]

If you are interested, I can offer you VBA code in seconds but not interested to give a try with Worksheet Functions

[rimmidi]

Hi,

The product list need not be sorted.

Thanks,
Ranga

[rimmidi]

Not a Problem. please share what you have..Thanks,

[:) Sixthsense :)]

Do right click on sheet tab and select view code and copy and paste the below in the code window

Please Login or Register  to view this content.

Refer the attached excel for details.

[rimmidi]

Hi Sixthsense,

The code worked well for me. Thanks a bunch.

[:) Sixthsense :)]

Glad it helps you and thanks for the feedback and rep

[rimmidi]

Hi SixSense,

I am running into a small problem while executing the script in my sheet. I have used a SAVE button to move the output to another sheet called Analytics. The above script works fine until i hit SAVE. Once i hit save, the above script stops doing what it was doing. I think the SAVE script is disabling the functionality of the above script but i don't know what it is. Can you please help ?

SAVE Button Script

Sub CopyData()
'To save the data from INPUT Sheet to ANALYTICS Sheet
Sheets("Analytics").Range("M" & Rows.Count).End(xlUp).Offset(1, -12).Resize(15, 13).Value = Sheets("Input").Cells(3, 1).Resize(15, 13).Value

End Sub


Your scripts changed according to my sheet.

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("D:E")) Is Nothing Then Exit Sub
Dim nEndRw As Long, nTemp As Long, i As Long, x As Long, nStCel As Long

Range("F2:F" & Rows.Count).ClearContents
nEndRw = Cells(Rows.Count, "D").End(xlUp).Row
nStCel = 2

Application.ScreenUpdating = False

For i = 2 To nEndRw
If IsNumeric(Cells(i, "E").Value) And Cells(i, "D").Value <> "" Then
nTemp = Cells(i, "E").Value
For x = 1 To nTemp
Cells(nStCel + x, "F").Value = Cells(i, "D").Value & "-" & x
Next x
nStCel = nStCel + nTemp
End If
Next i

Application.ScreenUpdating = True

End Sub

[:) Sixthsense :)]

The code seems to be perfect and not seeing anything to stop the event

[rimmidi]

Sixthsense,

It works fine now. I didn't update the scripts after adding few columns and that has caused the deactivation of the script. Thanks for looking into it.

Thanks again.

[:) Sixthsense :)]

Glad you fixed it and thanks for the feedback