CODEWITHSUNDEEP
excelcomboboxworksheet-function
user3448858
user3448858

Reputation: 1

combobox values and linking them to another cell within same worksheet without using VBA

I have a combobox on one page in worksheet it is linked to data in another tab same worksheet, once user picks their selection from the combobox I would like that selection (value) to be auomatically copied to another cell on a different tab. I do not want to use VBA and of course a simple =sheet1:A12 does not work because the combobox does not actually sit in any particular cell. Example user goes to combobox selects the word "terminal", now I would like the word "terminal" to end up in another cell on a different tab. can this be done without coding? if yes how would I go about doing that

Upvotes: 0

Views: 16182

Answers (2)

Wickedragon
Wickedragon

Reputation: 11

What I would do first is go to the table and add a series of numbers to the left I am now assuming that the values you want to use in your combobox is one of the columns of the table. The first row where one of the values you want to use appears in the target column should be "1". Continue numbering cells with an increment of one.

Now, note down the address of one cell that you are not going to look at, ever("Sheet2!A1" for example). Note down the range of the values you want to use, ie F5:F12 or something like that.

Now, insert a Form Control Combo Box with "insert" in the developer tab. Make sure you're in Design Mode.

Now, right click the Combo Box and select "Format Control". In the Format Control window click the "Control" tab.

Here, enter the range of values you want to use (F5:F12 or similar) as your "Input range:". Enter the cell address of the cell you're never going to look at ("Sheet2!A1") as the "Cell link:"

Now, if someone clicks the box the drop down menu should show the values you want to use, and clicking one of them yields a numerical value in "Sheet2!A1" corresponding to whatever was clicked in the drop down menu.

In the cell that you want to use you're going to use "VLOOKUP" to yield the value that was selected in the drop down menu.

If the table you are using is B5:F12 and you entered numbers in column A the VLOOKUP should look like this:

VLOOKUP("Sheet2!A1";A5:F12;6;FALSE)

Vlookup then picks the number from the cell you're never looking on ("Sheet2!A1"), and checks to see if any of the rows in the leftmost column in the table A5:F12 has that number. If one of them does it returns the sixthmost cell to the right's value which should in the above example be the value you want.

This allows for you to in a very simple manner change what values you want to appear in the drop down menu, and allows for various formulas to change the output to whatever you want.

Upvotes: 1

user2873219
user2873219

Reputation:

I will always reccommend learning VBA as it is amazingly customizable.

But the answer to your question is simple. Do not use a combobox object / activex object if you do not want to code.

If you want a drop down box on a worksheet that will work with =Sheet1!A1 and other WorksheetFunctions,

1.) Create a little table on your worksheet with the options you want in your dropdown/combobox

2.) Now Highlight/Select the Cell you want your combobox/dropdown to be in

2.) Go to the Data Tab(ALT + A), Click "Data Validation"(ALT + V) and on the "Settings" tab of the menu that pops up click in the "Allow:" DropDown, then select "List"

3.) Now select the list of options for your combobox in the "Source" box. optional: Once you select your source, click on "Input Message" tab and create a sticky note that will appear everytime the combobox/dropdown is selected. optional: Once you select your source, click on the "Error Alert" tab and create a custom error alert to tell user's they cant enter invalid text OR de-select the "Show error alert after data invalid data is entered" check box

4.) Your In-Cell drop down can now be incorporated with =Sheet1!A1 mirror cells. Be sure to go to the "Formula's" Tab on the Ribbon and click "Calculation options" and on the drop down Select "Automatic" in order to have your sheet update all formulas in real time

PROTIP: combine data validation with Conditional Formatting to make visually interactive simple sheets.

Upvotes: 0

Related Questions