+ Reply to Thread
Results 1 to 18 of 18

How to get data from ComboBox Multiple Columns to excel sheet

  1. #1
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    210

    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
    Attached Files Attached Files
    Last edited by SunOffice; 09-27-2013 at 11:51 PM. Reason: Just added another query in the point# 4
    Excelforum is Completely Awesome! True learning with Live Examples & Best Techniques!!

  2. #2
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: How to get data from ComboBox Multiple Columns to excel sheet

    Attached is with some code behind the combo box, you can see/learn how you can start making it work the way you want it.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    210

    Re: How to get data from ComboBox Multiple Columns to excel sheet

    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.
    Last edited by SunOffice; 09-11-2013 at 07:38 AM.

  4. #4
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: How to get data from ComboBox Multiple Columns to excel sheet

    Sorry did not really notice this problem. Remove the linked cell from the ComboBox's property and it should work.

  5. #5
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    210

    Re: How to get data from ComboBox Multiple Columns to excel sheet

    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.
    Last edited by Fotis1991; 09-11-2013 at 08:11 AM. Reason: Moderator Note: Pls use code tags around your code. Thank you.

  6. #6
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: How to get data from ComboBox Multiple Columns to excel sheet

    Try placing this in Data worksheet's code:
    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    210

    Re: How to get data from ComboBox Multiple Columns to excel sheet

    Sorry, it's not working...

  8. #8
    Forum Contributor
    Join Date
    01-18-2013
    Location
    Prague, Czech rep.
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    138

    Re: How to get data from ComboBox Multiple Columns to excel sheet

    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
    Last edited by SoulPrisoner; 09-11-2013 at 08:48 AM.

  9. #9
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    210

    Re: How to get data from ComboBox Multiple Columns to excel sheet

    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)).

  10. #10
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: How to get data from ComboBox Multiple Columns to excel sheet

    Quote Originally Posted by SunOffice View Post
    Sorry, it's not working...
    If you mean it's not working because the combo box values did not change, that's because it needs a "refresh". Also, make sure it's in the code of the worksheet named "Data", not "Sheet1". Then, whenever you add or change something in the "Data" worksheet, the named range will refresh, and as well as the combo box.
    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    01-18-2013
    Location
    Prague, Czech rep.
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    138

    Re: How to get data from ComboBox Multiple Columns to excel sheet

    go to the name manager and click in the offset formula and see if the range is selected properly if not, there might be blank cells which are not blank at all (might be containing spaces and such things) you need to clear em properly to use this function for good)

  12. #12
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    210

    Re: How to get data from ComboBox Multiple Columns to excel sheet

    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.
    Attached Files Attached Files

  13. #13
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: How to get data from ComboBox Multiple Columns to excel sheet

    I don't know how to autofit the column widths in the combo box, but this is a good estimate; go to ColumnWidths property of the combo box and set to this -> 48 pt;48 pt;300 pt

  14. #14
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    210

    Re: How to get data from ComboBox Multiple Columns to excel sheet

    ...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.

  15. #15
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: How to get data from ComboBox Multiple Columns to excel sheet

    You are welcome. And thanks to SoulPrisoner I learnt something new too

    If all your problems as of the first post are solved, please click on Thread Tools at the top and mark as Solved.

  16. #16
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    210

    Re: How to get data from ComboBox Multiple Columns to excel sheet

    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.

  17. #17
    Forum Contributor
    Join Date
    01-18-2013
    Location
    Prague, Czech rep.
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    138

    Re: How to get data from ComboBox Multiple Columns to excel sheet

    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)
    Last edited by SoulPrisoner; 09-12-2013 at 03:37 AM. Reason: typos

  18. #18
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    210

    Re: How to get data from ComboBox Multiple Columns to excel sheet

    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...
    Attached Files Attached Files
    Last edited by SunOffice; 09-14-2013 at 01:22 AM. Reason: attached the sample file

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. copy data from multiple sheet to master sheet with specific columns only
    By sinha.riteshabap in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-31-2013, 06:59 AM
  2. Macro to copy 3 columns of data from multiple sheets into 3 columns on 1 sheet
    By bballdcm2007 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-15-2012, 04:37 PM
  3. Replies: 1
    Last Post: 03-01-2012, 09:44 PM
  4. Copy data to a new sheet in multiple columns
    By kishoremcp in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-20-2012, 07:09 PM
  5. Fill Combobox with Multiple Columns
    By mccrimmon in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-26-2011, 04:28 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1