+ Reply to Thread
Results 1 to 12 of 12

AutoFill list with values that are set elsewhere in the sheet

  1. #1
    Registered User
    Join Date
    11-27-2012
    Location
    Teplice
    MS-Off Ver
    2010
    Posts
    71

    Question AutoFill list with values that are set elsewhere in the sheet

    Hi,

    is there any easy way to do this? Macro?


    1] First sheet will be filled with variables. This will be all the times changing. So for example
    text1
    text2
    text3

    2] The second sheet will be filled with parameters
    param1 ab
    param2
    param3 ad
    param4


    3] I would like to generate in list3 all products[in rows] with their attributes [columns] with the help of this macro. The null params will not be in this final list.
    variable param1 param3
    text1 ab ad
    text2 ab ad
    text3 ab ad

    i attach also the example sheet
    Please, help
    Attached Files Attached Files

  2. #2
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: AutoFill list with values that are set elsewhere in the sheet

    Maybe this

    Please Login or Register  to view this content.
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  3. #3
    Registered User
    Join Date
    11-27-2012
    Location
    Teplice
    MS-Off Ver
    2010
    Posts
    71

    Re: AutoFill list with values that are set elsewhere in the sheet

    mike7952

    - this is just great! Really, you dont know how you saved my tune of work.

    As I was testing it now, i realized one more thing to do more. I think it will make complicated work, but, maybe not. I am not really programator, even i have some basic knowledge.

    So, in my real xls sheet1 i have 5 columns and the fifth is the one i am using for 1st column of sheet3. From the first and second columns in sheet1 i would like to give this words at the end of param2 for example with some calling procedure...i reallz dont know how this manage or describe....so, example

    Example:

    1] First sheet will be filled with variables. This will be all the times changing. So for example
    A B C D E
    a0 b0 text1
    a1 b1 text2
    a3 b3 text3

    2] The second sheet will be filled with parameters
    param1 ab[call procedure to fill at the end of word " " & "A0" & "B0"] possible if function finds some symbol like # than it will give in final sheet values from a0 and b0
    param2
    param3 ad[not everywhere is the need of adding this procedure]
    param4

    and RESULT
    3] I would like to generate in list3 all products[in rows] with their attributes [columns] with the help of this macro. The null params will not be in this final list.

    variable param1 param3
    text1 ab a0 b0 ad
    text2 ab a0 b0 ad
    text3 ab a0 b0 ad

    Hope it is easier to read and understand than describe what i mean. ))
    Thank you in all the ways again.

  4. #4
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519
    Might be better to upload an example workbook. Showing expected results

  5. #5
    Registered User
    Join Date
    11-27-2012
    Location
    Teplice
    MS-Off Ver
    2010
    Posts
    71

    Question Re: AutoFill list with values that are set elsewhere in the sheet

    Here it is...
    i am not sure what can be indicator for concatenating the sheet1 value to result. For me, whatever and even can be in second column of sheet2 - like i putted there concatenate function...
    Attached Files Attached Files
    Last edited by Odeen; 01-09-2013 at 07:57 AM.

  6. #6
    Registered User
    Join Date
    11-27-2012
    Location
    Teplice
    MS-Off Ver
    2010
    Posts
    71

    Re: AutoFill list with values that are set elsewhere in the sheet

    so? You think it will be possible somehow? Please answer at least this information.

    Thanks

  7. #7
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: AutoFill list with values that are set elsewhere in the sheet

    Im not sure what your doing now. I see Text1,Text2 and so on are now in column E on sheet1. Where they were in Column A before.

  8. #8
    Registered User
    Join Date
    11-27-2012
    Location
    Teplice
    MS-Off Ver
    2010
    Posts
    71

    Re: AutoFill list with values that are set elsewhere in the sheet

    Quote Originally Posted by mike7952 View Post
    Im not sure what your doing now. I see Text1,Text2 and so on are now in column E on sheet1. Where they were in Column A before.
    mmm...

    it was basic view of my question.

    The real document is like it is in the attachenment. So instead of A column, it is E column. This i edited your code just here......and it worked.

    [code] aArr = .Range("e1", .Cells(Rows.Count, "e").End(xlUp)) [code]

    But

    In this real document with real data in there, the E column is done from the previous one columns. But sometimes, i need to concatenate values with params in the final sheet3. Like it is shown in example.
    Last edited by Odeen; 01-09-2013 at 12:52 PM.

  9. #9
    Registered User
    Join Date
    11-27-2012
    Location
    Teplice
    MS-Off Ver
    2010
    Posts
    71

    Question Re: AutoFill list with values that are set elsewhere in the sheet

    I have found out solution, which i can see, that can be functional, but i dont know how to implement it into your code.
    I think it is easier to understand and also probably to do it.
    Please see the attachenment.
    Attached Files Attached Files

  10. #10
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: AutoFill list with values that are set elsewhere in the sheet

    Give this a try

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    11-27-2012
    Location
    Teplice
    MS-Off Ver
    2010
    Posts
    71

    Lightbulb Re: AutoFill list with values that are set elsewhere in the sheet

    Great, this is just great for me.

    thank you very much.

  12. #12
    Registered User
    Join Date
    11-27-2012
    Location
    Teplice
    MS-Off Ver
    2010
    Posts
    71

    Re: AutoFill list with values that are set elsewhere in the sheet

    Hello Mike,

    your code workes for me fantasticaly. I have just one more and i think small task for you in it.

    The summary is, that yet, the code transpone all rows from sheet 2 to sheet 3 and copy there the Values from sheet 1 (if i put there number of column i want to copy to it).

    I d like you to help me with one condition to integrate
    - can the script check if in sheet 2 columns B and C are null (no symbol in them), than it will not transpone this to sheet 3 as column?
    in other words, in sheet 3 will not be empty columns, only those, that can be filled with some data.

    Thank you very much

    Quote Originally Posted by mike7952 View Post
    Give this a try

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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