Recording the result from data validation -list

[Yeoman.jeremy]

Hi there.
I've created a basic quoting system using excel, and my latest project in its developments is to record how a customer heard about the business in a drop down validation list.

I've got the list working fine and everything, I've just got no idea how to record it on the separate workbook.


There is a command button which saves the sheet, and then clears it so it's ready for the next quote, and i also use this button to record other statistics related to checkboxes.

Is there a way i can make excel read what is selected in the drop down box, and add one to the other workbook in the column that corresponds with that option?

 

[mikerickson]

If you are using a list with Data Validation, the cell contents can be treated just like any other cell.

 

[Yeoman.jeremy]

What I'm wondering, is how do I do something like:
If the cell is "Fliers", then add one to a different workbook in the cell named "fliers" etc etc etc without doing a ridiculously lengthy 'IF'

 

[mikerickson]

You mentioned a command button that saves the sheet and clears it, preparing for the next entry.
What is the code that is behind that button?
What is the cell in the source sheet with the DV on it? Where do you that choice put in the archive sheet?

 

[Yeoman.jeremy]

Ok, so the button i mentioned does a lot more than just saving and clearing, it runs other IF statements and call macros from other modules etc.
here it is.

Sub NewJobSheet_mcr()
If Sheets("Job Sheet").Range("F10") = "" Then

Else
Application.ScreenUpdating = False

Dim wbJobSheet As Workbook
Dim wbStats As Workbook
Dim strTestFileName As String
Dim srtPath As String
 
   Set wbJobSheet = ThisWorkbook ' create reference to the workbook the code is in
 
   strPath = "C:\Users\owner\Desktop\Presentation Job Sheet\"
   
   strTestFileName = "Quoting Statistics"
 
   ' open and create a reference to the 'Test Over WBk' workbook
    On Error Resume Next

If Sheets("Job Sheet").CheckBox35 = True Then
Set wbStats = Workbooks.Open(strPath & strTestFileName)
With wbStats.Sheets("Sheet1")
.Range("D5").Value = .Range("D5") + 1
End With
End If
If wbJobSheet.Sheets("Job Sheet").CheckBox38 = True Then
   With wbStats.Sheets("Sheet1")
.Range("f5").Value = .Range("f5") + 1
End With
End If
With wbStats.Sheets("Sheet1")
.Range("E5").Value = .Range("E5") + 1
wbStats.Close (True)
End With
Call ClearForm_mcr
  Call Uncheck


Call AlterDuplicate_mcr
 ThisFile = Range("J6").Value
    ChDir "C:\Users\owner\Desktop\Development\Quotes to sort"
    ActiveWorkbook.SaveAs FileName:=ThisFile
 Call ReturnDuplicate_mcr
   Call ClearForm_mcr
  Call Uncheck
[g2] = [g2] + 1


    [A31] = "Laying Conventional"
     [B30] = "Greenlay 9.5mm/95kg"
     [A32] = "Uplift Req'd"
    [A33] = "Smoothedge Conc:"
    [A34] = "Smoothedge Wood:"
    [A41] = "Travel Town"
    [A42] = "Cust to shift furniture"
    [F52] = "Travel Town"
    [F40] = "Travel Town"
    'sets the default values for the cells with listings.
    
    
    

Application.ScreenUpdating = True

End If

End Sub

I know it looks really messy, but I didn't really make it with the thought that anybody else would need to be reading it at any stage.

The cell that the validation is in, is

Sheets("Job Sheet").Range("F10")

And in another workbook there is a range of L5:L19
in which the different options are headed up next to them, in the 'K' column

 

[mikerickson]

I can't figure out what you are trying to do with the entries in that cell.

 

[Yeoman.jeremy]

Basically, I want to be able to see where my advertising $$$ are best being spent, so I included in my quoting system a list which gave options for all the possible ways a customer could have heard about us.

I need a way, that when a specific button is pressed it looks at what is in that box, and depending on what it is records it in another workbook.

In the other workbook, i have

Fliers
Word of Mouth
Yellow Pages
Radio
NewsPaper
Shop Signage
Mailout
Car Signage
Sponsorship
Alliance
Website
TV
Council Consent Mailouts
Other

And next to it I want the values.

So if the validated cell has "Radio" selected when the button is pressed, it adds one to the 'Radio' value in the other workbook and etc