[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How do I create an interactive form in excel?

Posted on 2010-01-04
4
Medium Priority
?
585 Views
Last Modified: 2012-06-21
I need to create an excel spreadsheet that works sorta like an interactive form on the web.  I have dropdown menus in excel for example a lot number for a house, drop down for the house plan, dropdown for counter tops .. etc.  Each of these choices in the drop down has a price associated with it.  Id like to be able to populate a cell with these prices based on the dropdown selection so I can add a formula in for total cost based on several different drop down configurations.  A makeshift effort is attached.  I have figured out the dropdown menus, just not sure how to link their selections to and populate another cell.

Thanks in advance,
Chase
SalesTest.xlsx
0
Comment
Question by:jdroger2
  • 2
4 Comments
 
LVL 5

Expert Comment

by:fhillyer1
ID: 26173400
I don't see the worksheet but you can refer the values like this
cells(X,Y).Value = Form.Dropdown.Value

0
 
LVL 20

Expert Comment

by:Ardhendu Sarangi
ID: 26173720
Hi roger,
This is doable by using macros on the page which will change the value in the cells based on the dropdown item selected. See attached file..

Thanks,
Ardhendu
Private Sub Worksheet_Change(ByVal Target As Range)
   
   Dim i As Integer
   Dim str As String
   
   'Do nothing if more than one cell is changed or content deleted
   If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    
   If Not Intersect(Target, Range("A2")) Is Nothing Then
    'Stop any possible runtime errors and halting code
    On Error Resume Next
    'Turn off ALL events so the change does not put the code into a loop.
    Application.EnableEvents = False
    Select Case (Left(Target, 1))
        Case "A"
            Range("B2") = 2000
        Case "B"
            Range("B2") = 4000
        Case "C"
            Range("B2") = 5000
        Case Else
            Range("B2") = 10000
    End Select
          
    'Turn events back on
    Application.EnableEvents = True
    'Allow run time errors again
    On Error GoTo 0
    End If
End Sub

Open in new window

SalesTest.xls
0
 
LVL 5

Accepted Solution

by:
fhillyer1 earned 2000 total points
ID: 26173961
this is easier than what i suggested, after opening your sales worksheet
just add the following formulas
on column B2 add
=VLOOKUP(A2,Sheet2!A:B,2,FALSE)
on column D2 add
=VLOOKUP(C2,Sheet2!C:D,2,FALSE)
and on column F2 add
=VLOOKUP(E2,Sheet2!E:F,2,FALSE)

now when you change the dropdowns it will look for the housing style on
the list and will retrieve the cost
then the lot as well and finally the countertop

0
 
LVL 1

Author Comment

by:jdroger2
ID: 26174880
@Pari123, I was unable to make your file work properly but thank you for your assistance.

@Fhillyer, perfect. Exactly the formula I was looking for.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
New style of hardware planning for Microsoft Exchange server.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question