Use Combo box to replace a variable across entire spreadsheet

[Helen842000]

Hi all,

I have a dropdown box containing text strings. How can I use the selection from a combo box as part of a formula?

So for example I have a spreadsheet that is set up using a search term 'media', I want to select something else from the list e.g 'arts' and I want all of the references to 'media' to turn into 'arts' in the formulas. Kind of like a mass find and replace.

Any suggestions would be great! Thanks

 

[Snakehips]

Helen842000,

The folowing assumes that you are using a form ComboBox and are not using vba.

Right click the Combo and go to the Format Control tab where I assume you have defined your Input Range for your search terms.
Determine a cell reference eg A17, that you can use as a 'helper' and input that as the Cell Link for the combo.

Then when you select from the combo the linked cell will take the index of the selected item from your list.

To keep your formulas simple you then need another 'helper' cell eg A18, with the formula that will return the slected search term.

eg c = item 3 from the list a,b,c

Excel 2007

	A
17	3
18	c

<COLGROUP><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Sheet1​

Worksheet Formulas Cell Formula A18 =INDEX(M1:M3,A17) <THEAD> </THEAD><TBODY> </TBODY>

<TBODY>
</TBODY>

Your various formulas can then reference the text in A18.


Hope that helps.