Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
drop down list for IF equation
Morning all.
I have an IF equation, and want to know if I can place a dropdown menu/list in the equation, to call to a name within the list. This equation is located in a single cell, and will be dragged down to subsequent cells-- once the correct name is selected. I only want the dropdown in the first cell. E.g. =IF(A4="DecreedOwner'sName","B","C") for the "DecreedOwner'sName", I'd like to have a list of names to choose from. E.g. John Doe Sam Davies Jake Joely Betty Davis etc.... I was thinking some kind of macro would need to be called to. Is this possible? If so, how would I do this? Thank you. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
drop down list for IF equation
Hi Steve,
Is this what you seek? In a workbook on sheet1, i placed an active x combobox from the control toolbox over cell a3. On Sheet2 in cells a1:a3, I put a list of names then back on sheet1, I populated cells a4:a17 with names. and in cell c4, I entered this formula: =IF(A5=$A$4,"B","C") and copied down to cell c18. Dan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
drop down list for IF equation
Hi Dan,
Thanks for the response. I appreciate it. Actually, I was hoping to have the list be a global list for use in ALL of my files-- which makes placing it in a single file unusable. We've got over 800 files. Which would mean a single combobox in all of the files, on the one worksheet. I'm thinking more a macro that would allow the dropdown to call back to. Next, you said ActiveX combo box. Is that what I'd need to use for the worksheets? Again, thank you. "dan dungan" wrote: Hi Steve, Is this what you seek? In a workbook on sheet1, i placed an active x combobox from the control toolbox over cell a3. On Sheet2 in cells a1:a3, I put a list of names then back on sheet1, I populated cells a4:a17 with names. and in cell c4, I entered this formula: =IF(A5=$A$4,"B","C") and copied down to cell c18. Dan . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
drop down list for IF equation
Hi Steve,
I'm not sure I have the expertise to help and I'm still not clear about your desired outcome. Here are some questions they may clarify your goals for the group to offer some help. Do you want a macro to write the formula to all the rows in 800 workbooks? Are all the workbooks laid out the same? If you got this to work, what would that do for you? I hope this helps, Dan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
drop down list for IF equation
Hi again.
"Do you want a macro to write the formula to all the rows in 800 workbooks?" No. A single row, and only one file at a time-- as I, or another tech. does a file update. I can take the equation, and drag it down once the other data is entered. "Are all the workbooks laid out the same?" For the worksheet in question-- Yes. This particular worksheet was designed by me, and is being placed in old workbooks as we do updates to them. Thus, the data would be confined to this worksheet of each workbook. What would this do for me... It'd help me a lot, as it would 3 others with whom I work. The goal is to have a list of the names, extract them from the drop down, and upon being placed in the primary cell, be able to copy that specific name, in that specific equation, down through a range that varies from 10-600 rows, depending on the file. Presently, I'm having to manually extract the name from another worksheet, and paste it into the "DecreedOwner'sName" portion of the equation. E.g. =if(A4="DecreedOwner'sName","B","C") becomes =if(A4="John Smith","B","C") or =if(A4="AnyOneOf800Names","B","C") It's gotten tedious. First, I can fill in all the names. I'd only need a generic sample of a handful-- maybe two or threee sample names. Next, I just need the basic code to connect the names to the dropdown menu. I've learned that the ActiveX menu is the one that I want to place in the worksheet. It appears rather a simple task to insert the menu into the location on the worksheet. I just need to know how to connect this: =EMBED("Forms.ComboBox.1","") to a cell on my worksheet. I hope that clears it up. If not, please let me know. Again-- thank you very much. "dan dungan" wrote: Hi Steve, I'm not sure I have the expertise to help and I'm still not clear about your desired outcome. Here are some questions they may clarify your goals for the group to offer some help. Do you want a macro to write the formula to all the rows in 800 workbooks? Are all the workbooks laid out the same? If you got this to work, what would that do for you? I hope this helps, Dan . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
drop down list for IF equation
Hi Steve,
I'm still not clear about your needs, but maybe this from the archives in 2004 will give you some ideas: 3. Vic Eldridge Newsgroups: microsoft.public.excel.programming From: (Vic Eldridge) Date: 16 May 2004 21:14:50 -0700 Local: Sun, May 16 2004 8:14 pm Hi Abhinav, Try running the InsertCombos macro I've made for you. As it creates the comboboxes, it uses the contents of cells A1:A5 (on Sheet2) to define the list for the comboboxes. If you needed a different list for each Combobox, post back with more details. Make sure the ShowWebPage macro is in a standard module, it will be run whenever you make a selection from one of the ComboBoxes. Regards, Vic Eldridge Sub InsertCombos() Dim cel As Range For Each cel In Range("A1", Range("A65536").End(xlUp)) If cel.Value < "" Then With ActiveSheet.DropDowns.Add( _ Left:=cel.Offset(0, 1).Left, _ Top:=cel.Top, _ Height:=cel.Height, _ Width:=80) .ListFillRange = "Sheet2!$A$1:$A$5" .OnAction = "ShowWebPage" End With End If Next cel End Sub Sub ShowWebPage() With ActiveSheet.DropDowns(Application.Caller) ActiveWorkbook.FollowHyperlink Address:= _ "http://www.example.com/" & .List(.ListIndex) End With End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
drop down list for IF equation
Morning Dan,
Ok, I found the original post that Vic was responding to. It appears his post was putting a combobox in one column, at the top, based on the values of the previous column in that worksheet. My goal is to place a single combobox- regardless of the prior column's values. My secondary goal is to have that combobox insert a name into an if equation- in that cell. My tertiary goal is to be able to drag that finished equation, with the name selected by the combobox selection, all the way down to the bottom of my dataset. E.g. =if(A4="combobox_Name","B","C") where combobox_Name" is the value I select from the combobox. Thus, I'm trying to learn if I can place a combobox_Value to be part of the IF equation. And if so, how is it done. "dan dungan" wrote: Hi Steve, I'm still not clear about your needs, but maybe this from the archives in 2004 will give you some ideas: 3. Vic Eldridge Newsgroups: microsoft.public.excel.programming From: (Vic Eldridge) Date: 16 May 2004 21:14:50 -0700 Local: Sun, May 16 2004 8:14 pm Hi Abhinav, Try running the InsertCombos macro I've made for you. As it creates the comboboxes, it uses the contents of cells A1:A5 (on Sheet2) to define the list for the comboboxes. If you needed a different list for each Combobox, post back with more details. Make sure the ShowWebPage macro is in a standard module, it will be run whenever you make a selection from one of the ComboBoxes. Regards, Vic Eldridge Sub InsertCombos() Dim cel As Range For Each cel In Range("A1", Range("A65536").End(xlUp)) If cel.Value < "" Then With ActiveSheet.DropDowns.Add( _ Left:=cel.Offset(0, 1).Left, _ Top:=cel.Top, _ Height:=cel.Height, _ Width:=80) .ListFillRange = "Sheet2!$A$1:$A$5" .OnAction = "ShowWebPage" End With End If Next cel End Sub Sub ShowWebPage() With ActiveSheet.DropDowns(Application.Caller) ActiveWorkbook.FollowHyperlink Address:= _ "http://www.example.com/" & .List(.ListIndex) End With End Sub . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
drop down list for IF equation
ok. I asked a colleague of mine who recently started and is a programmer.
While his vba is rusty, he brought up a point that you've raised, and other posts have stated the same-- use a worksheet to source the names, and call them from that location. As I've been wanting a list of data to source a few things from, I'll be doing that, and make a combobox off that list. Thanks again for your help. Have a great week. "dan dungan" wrote: Hi Steve, I'm still not clear about your needs, but maybe this from the archives in 2004 will give you some ideas: 3. Vic Eldridge Newsgroups: microsoft.public.excel.programming From: (Vic Eldridge) Date: 16 May 2004 21:14:50 -0700 Local: Sun, May 16 2004 8:14 pm Hi Abhinav, Try running the InsertCombos macro I've made for you. As it creates the comboboxes, it uses the contents of cells A1:A5 (on Sheet2) to define the list for the comboboxes. If you needed a different list for each Combobox, post back with more details. Make sure the ShowWebPage macro is in a standard module, it will be run whenever you make a selection from one of the ComboBoxes. Regards, Vic Eldridge Sub InsertCombos() Dim cel As Range For Each cel In Range("A1", Range("A65536").End(xlUp)) If cel.Value < "" Then With ActiveSheet.DropDowns.Add( _ Left:=cel.Offset(0, 1).Left, _ Top:=cel.Top, _ Height:=cel.Height, _ Width:=80) .ListFillRange = "Sheet2!$A$1:$A$5" .OnAction = "ShowWebPage" End With End If Next cel End Sub Sub ShowWebPage() With ActiveSheet.DropDowns(Application.Caller) ActiveWorkbook.FollowHyperlink Address:= _ "http://www.example.com/" & .List(.ListIndex) End With End Sub . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
drop down list for IF equation
You too, Good luck!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
drop down list based on other drop down list pick | Excel Discussion (Misc queries) | |||
Drop down list dependant on previous drop down list | Excel Discussion (Misc queries) | |||
Drop down lists that auto create and then filter the next drop down list | Excel Worksheet Functions | |||
Drop Down List choice selecting another drop down list | Excel Worksheet Functions | |||
multiple select from the drop down list in excel. list in one sheet and drop down in | Excel Discussion (Misc queries) |