How to get data from ComboBox Multiple Columns to excel sheet

Hi,

I have a ComboBox, which shows multiple column data.
I am getting issues with this functionality, please help me on below points:

1.) When we click on the ComboBox drop-down, see that column data is not coming under the right column name.

2.) How to fill range (C3:F3) data by using this ComboBox trick. I mean that how to get data from ComboBox Multiple Columns to excel sheet.
I tried to change "BoundColumn" property of this ComboBox, but it is taking only one column reference at a time.

3.) If I go by "Rating", so there is so many duplicates or we can say more than one movie name on the same rating, and those movies have different Ranks. I think that if I choose the "Rank", then I can get the correct movie name with its votes/score (in the green color cells). I don't know how to apply the 2nd filter based on the first filter value from this ComboBox.

4.) I am using a Name range as "MovieRanking" (plz see in the Names Manager), how can I set it dynamically?, which can be as below lines or something else:

Please Login or Register  to view this content.

I am using excel 2010, and looking for a good practical knowledge.

Regards,
SunOffice

Thank you millz! you are too fast!

It's working fine partially as it is always showing the 1st record.
For example: If you choose any other movie name or Rank with the same Rating (8.9), the result is showing the top record as below data.
Rating Rank Title Votes
8.9 4 Pulp Fiction (1994) 800,961.000

Please help me with above 3 queries.

Yes! Now it's working good... I just removed the Linked cell from the ComboBox's property.
Thank a ton!

Now I just have to make the "MovieRanking" Name range as a dynamic range. Can you please help me with this too... I just felt that it is important to use.
I am using a Name range as "MovieRanking" (plz see in the Names Manager), how can we set it dynamically? Actually I still know it by VBA, which is something like below line:

Please Login or Register  to view this content.

Sorry, it's not working...

Hi SunOffice,

if you dont need to have the named range executed by macro and you´re fine with it as a function, use offset in the name manager (it will be dynamic according to the arguments you set there:

=offset("start cell of the range", "step up&down", "side steps", "# of rows", "# of columns")

first argument set the first cell of a range, u dont need to do any steps so go with 0 and as for the last two arguments, those gonna make it dynamic so use counta function.

Best regards

Soul

PS - just hope i didnt switch the steps and the row&column thingy cause i tend to....:-D....but u´ll fine when you try and see yourself

EDIT: here, example of working dynamic range to keep header as named range nomatter how many new columns have been added:

=OFFSET(Input!$A$1;0;0;1;COUNTA(Input!$1:$1))

PS -- input is a sheet name

Thank you SoulPrisoner!
I tried with below formula in the Names Manager, which is not working fine...:
=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),1)

and I still see so many blank as extra record lines in the ComboBox with this formula =OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1)).

Thank a ton to millz & SoulPrisoner!

millz - Your tricks are working good, plz see if we can also set the display data columnwidth (while looking at the ComboBox drop-down), so all the movies name can show clearly (as Column Auto-Fit mode).

SoulPrisoner - I tired your methods, and am still interested to learn your methods. Please let me know how can we get the desired result in the attached file.

...oh really? You don't know it....??
haha..., man you KNOW it very well!! Working fine in all the ways!

Thanks a ton for all the awesome tricks for ComboBox combinations!! I very appreciate your quick response in a short time to solve this thread.

Yes! My queries are solved!

I would like to still wait for SoulPrisoner tricks. If I don't get any reply then I will close this thread by the next day or earliest.

Hi guys,

I can see you´ve solved what you needed, that´s good. I was off for that day already, sorry I didnt respond earlier. But now I´m here and here goes the file edited the way i ment. It seems to work aswell, I´ve disabled that change macro on the named range and made it in the name manager i can see no empty fields, check pls if that´s it:

millz.xlsm

Best regards

Soul

PS - this way of setting dynamic list is ment to be used for the dropdown lists etc. (something you can do with validate data)

Hi Soul,

Thanks for replying! I just checked the your file and the methods.
but it is working partially... the new name easily gets updated to the ComboBox dropdown, but if more than one movie name is available at the same rank, then it shows only the 1st (top) movie name of that ranking. It doesn't allow me to select any other movie name on the same ranks.
Please look at this...