drop down list for IF equation

[Steve]

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.

 

[dan dungan]

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

 

[Steve]

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]

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

 

[Steve]

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]

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: (e-mail address removed) (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

 

[Steve]

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.

 

[Steve]

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]

You too, Good luck!