+ Reply to Thread
Results 1 to 7 of 7

how to insert column from Sheet2 to Sheet1

  1. #1
    Registered User
    Join Date
    04-22-2007
    Posts
    11

    how to insert column from Sheet2 to Sheet1

    Hello,

    This is the problem:
    In Sheet1 I made drop down menu with some text data from Sheet2.
    For example five colors: blue, yellow, black, white and red.
    How:
    In Sheet2 those colors are in one column, each color in it’s own cell, one below the other, so I selected those cells, named that range (if that selection of cells is called “range” in Excel?) “Colors” and then in Sheet1 in a specific cell I did: Data/Validation/Settings/Validation Criteria-Allow: List-Source: =Colors.
    So now I have a drop down menu in Sheet1 from which I can choose any of those five colors that are in one column in Sheet2.

    Among other things in that Sheet2 I have also things that are of some specific color.
    Those things are also in columns of their own.

    For example I have a column with six things that are of the same color, each thing in a single cell, one below the other.

    So I want when I pick a single color from a drop down menu in Sheet1, those things that are of that color (data from a column in a Sheet2) to appear below that cell in Sheet1, in six cells to be specific, each thing in it’s own cell, just like the way that they are in that column in a Sheet2.
    Cells are exactly the same in size and in Format.

    Please help, my English is not so good, neither is a knowledge of Excel for that matter, I hope that I explained well enough what is my problem.

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474
    Hi,
    This can be done with VBA ,



    if you place this code in a regular vba module
    Please Login or Register  to view this content.

    then place this code in a worksheet module

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    04-22-2007
    Posts
    11

    does not help

    Thank you very much Sir,

    But this does not resolve my problem; I thought that knowledge of English language, or lack of, could be a barrier in this thing.
    It’s not that I need six cells of the same color below the drop down menu; I used colors only for example.

    What I need is that some text data appear below the drop down menu cell, depending from what I chose in the drop down menu.

    Example No. 2:

    Car manufactures! So if I have five car manufacturers in a drop down: Audi, BMW, Mercedes, Toyota, Honda.
    I want for example if I chose BMW from a drop down that six models of BMW appear below, each in its own cell!

    Like this:

    BMW
    320i
    330d
    520i
    530d
    540i
    X5

    I would make these columns of data (car models) in a Sheet2, just like data for a drop down menu (car manufacturers) that I would insert in a cell in Sheet1 with “Validation” command (or whatever if not Command).
    After that I would “Hide” Sheet2 and protect Workbook.
    Do you understand now?

    Please help!
    Last edited by IvicaOS; 04-22-2007 at 02:08 PM.

  4. #4
    Forum Contributor Tarball's Avatar
    Join Date
    04-21-2007
    Location
    Atlanta, GA
    Posts
    166
    I really wish I could take the credit for this example but I cannot. This example was taken from the book "Excel Hacks" from O'Reilly. See HACK #24 in the book. This book has a lot of cool little tips and tricks.

    The example builds the lists using named ranges. To see these lists select Insert-->Name-->Define. There is one length formula in the CorrespondingList Name.

    See example as attached. Well I cannot seem to post my spreadsheet even though it is a valid file (Zip file). Since I cannot attach the file and it appears that I cannot post any written code!!! Not sure what is going on? In any event I will notify the admins. I keep getting an error: "You followed an invalid link"
    Last edited by Tarball; 04-22-2007 at 04:43 PM.

  5. #5
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474
    Hi,
    Replace the one code with this changed code


    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    04-22-2007
    Posts
    11

    Tnx guys!

    I will try this,
    just tell me please where should I put this lines that you wrote Dave, and do they have to look exactly the same way as you wrote them (spacing’s and everything, capital letters)?
    I understand off course that I should change cell labels and ranges (I bet that you are laughing now), according to their position in my Sheet2.
    What can I say, I really do not know where to put those lines of yours, and how, I do not know anything about VBA, is there not an easier way?

    Please guys, like I’m six year old!

  7. #7
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474
    Quote Originally Posted by IvicaOS
    I will try this,
    just tell me please where should I put this lines that you wrote Dave, and do they have to look exactly the same way as you wrote them (spacing’s and everything, capital letters)?
    I understand off course that I should change cell labels and ranges (I bet that you are laughing now), according to their position in my Sheet2.
    What can I say, I really do not know where to put those lines of yours, and how, I do not know anything about VBA, is there not an easier way?

    Please guys, like I’m six year old!
    Hi IvicaOS,

    you don't have to rewrite the code just copy it and paste into a module the one code I just sent you goes into a regular module
    when in your workbook hit alt F11, this takes you to VBA window,
    at the top of the screen select insert then module paste the code into that screen

    the worksheet module goes into a different place,

    right click on the sheet tab at the bottom and select view codes,

    paste the worksheet_change code there


    here's a site that explains where to paste the codes

    http://contextures.com/xlvba01.html

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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