Using a dynamic name to point at the current list box cell

robrobby

New Member
Joined
Sep 14, 2012
Messages
22
I have a sheet with a single list box which with many items listed in it, I have a seperate formula which refers to that list box and does its calculations based on what is selected and displays the results in the list box in the cell to the right of it


What I'm looking for is a way to have several list boxes going down the rows and for the formula to act on the current selected list box.
So if I change a value on row 1 the formula runs depending on what I selected. If i then change a value in another list box, say on row 2, the formula would calculate based on what I selected in that cell.

The formula basically builds a dynamic list of items depending on what was selected and then I use another list box to show those results, So by selecting something in the first listbox like "blue" it shows me all the cars in my table that has the blue colour assigned to them.
Similar to the code here

Use a drop down list to search and return multiple values | Get Digital Help - Microsoft Excel resource

If i just had the one list box then fine, But I want many rows of list boxes that do the same thing. If there was a way of using a dynamic name to have the current active cells data in it then I could incorporate this into my formula instead of having a unique cell reference....

How would I go about this?

(if you understand what I mean :))

Example: here

Any help is appreciated.
 
Last edited:

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Here is a possible solution:

In H2, in data validation use =OFFSET(M3,MATCH(G2,M3:M13,0),1,COUNTIF(M3:M13,G2),1) (the cell references correspond to you example sheet)

In I2, in data validation use =OFFSET(S2,MATCH(H2,S3:S8,0),1,COUNTIF(S3:S8,H2),1)

HTH
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,733
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top