Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Listbox linking

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Alberta, Canada
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a listbox but i want to use a rowsource that isn't in the same file. how do i do this?

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-24 11:48, excelmacro wrote:
    I have a listbox but i want to use a rowsource that isn't in the same file. how do i do this?
    A new thread?

    I just named a column range in File1.xls List and in another file created a ListBox using View|Toolbars|Forms.

    I used

    'File1.xls'!List

    as Input range

    and

    D3

    as Cell link.

    The following allows to retrieve the selected item:

    =INDEX('File1.xls'!List,D3)

    File1.xls needs to be open if you want to make a new selection.

    Aladin

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Alberta, Canada
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks. I was trying to do it through visual basic, but didn't know the coding how to do it.

    Is there anyway I can eliminate duplicate entries?

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-24 12:19, excelmacro wrote:
    Thanks. I was trying to do it through visual basic, but didn't know the coding how to do it.

    Is there anyway I can eliminate duplicate entries?
    You can create a unique list in the source file using Advanced Filter and name List the output of filtering (excluding the label).

    [ This Message was edited by: Aladin Akyurek on 2002-04-24 12:26 ]

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Location
    Alberta, Canada
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    So your saying that i can name the filter and then just point the listbox to the filter? If i have to manually do the advancedfilter everytime i reload the information, it won't be worth it.

    Is there anyway I can do do this through VBA so that I can just make this part of a macro?

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default


    On 2002-04-24 12:40, excelmacro wrote:
    So your saying that i can name the filter and then just point the listbox to the filter?


    No.

    If i have to manually do the advancedfilter everytime i reload the information, it won't be worth it.

    I have to agree if the source range (from which you must construct a unique list of items that will populate the ListBox) changes frequently.

    Is there anyway I can do do this through VBA so that I can just make this part of a macro?

    If Macro designers do not jump in, I'll give you a system of formulas that can construct the desired list automatically.

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-04-24 13:37 ]

  7. #7
    Board Regular
    Join Date
    Apr 2002
    Location
    Alberta, Canada
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The source range does vary, making it a little more complex. Any help would be appreciated though. I've just never dealt with this sort of thing before.

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-24 12:57, excelmacro wrote:
    The source range does vary, making it a little more complex. Any help would be appreciated though. I've just never dealt with this sort of thing before.
    What is the sheet name in which the raw data is?

    In which column is the raw data and at which row does it start?

    The raw data consists of text values, right? If so, is there a numeric column in the vicinity that changes along with it (that is, these two are always of equal size)?

    I must warn you that the formulas will be expensive qua performance.

    Aladin

  9. #9
    Board Regular
    Join Date
    Apr 2002
    Location
    Alberta, Canada
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    raw data sheet: g:monthly actual.xls

    raw data is in column A, has a heading in cell A1, and in cell A2 the item names begin. they repeat several times over, but the exact # of times the name is repeated varies.

    the data is considered a text value, but also contains numbers (if this makes any difference). I will only be using the data in column A as though it is text.

    There are numberic columns in the vicinity (column D and F) that are associated with the text in column A.
    eg
    cell A2: 5 speed
    cell D2: 1 'indicates store bike sold at'
    cell F2: 425.53 'indicates price bike sold for

    cells D3 and F3 are for store 2 and has a different price in cell F3.


  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-24 14:12, excelmacro wrote:
    raw data sheet: g:monthly actual.xls

    raw data is in column A, has a heading in cell A1, and in cell A2 the item names begin. they repeat several times over, but the exact # of times the name is repeated varies.

    the data is considered a text value, but also contains numbers (if this makes any difference). I will only be using the data in column A as though it is text.

    There are numberic columns in the vicinity (column D and F) that are associated with the text in column A.
    eg
    cell A2: 5 speed
    cell D2: 1 'indicates store bike sold at'
    cell F2: 425.53 'indicates price bike sold for

    cells D3 and F3 are for store 2 and has a different price in cell F3.
    OK.

    I'll assume that the sheet in monthly actual.xls which houses the raw data of interest is called Data.

    Insert a new worksheet and name it BBoard (from Blackboard).

    Activate Inset|Name|Define.
    Enter NumRecs as name in the Names in Workbook box.
    Enter as formula in the Refers to box:

    =MATCH(9.99999999999999E+307,Data!$D:$D)

    Activate Add. (Don't leave yet the Define Name window.)

    Enter DataRecs as name in the Names in Workbook box.
    Enter as formula in the Refers to box:

    =NumRecs-(ROW(Data!$A$2)-1)

    Activate Add. (Don't leave yet the Define Name window.)

    Enter NAMES as name in the Names in Workbook box.
    Enter as formula in the Refers to box:

    =OFFSET(Data!$A$2,0,0,DataRecs,1)

    Activate Add. (Don't leave yet the Define Name window.)

    Enter List as name in the Names in Workbook box.
    Enter as formula in the Refers to box:

    =OFFSET(BBoard!$C$2,0,0,BBoard!$D$5,1)

    Activate OK.

    In BBoard:

    In A2 enter:

    =IF(ROW()-1<=DataRecs,SUMPRODUCT((Data!$A2>NAMES)+0)+1,"")

    Copy down this to a number of rows that is more than the expected number of rows in column A of Data.

    In B2 enter:

    =IF(ISNUMBER(A2),IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,INDIRECT($D$2),0)),INDEX(NAMES,MATCH(ROW()-ROW($A$2)+1,INDIRECT($D$2),0)),0),"")

    Copy down this to a number of rows that is more than the expected number of rows in column A of Data.

    In C2 array-enter:

    =IF(ROW()-ROW(INDIRECT($D$4))+1>ROWS(INDIRECT($D$3))-COUNTIF(INDIRECT($D$3),0),"",INDIRECT(ADDRESS(SMALL((IF(INDIRECT($D$3)<>0,ROW(INDIRECT($D$3)),ROW()+ROWS(INDIRECT($D$3)))),ROW()-ROW(INDIRECT($D$4))+1),COLUMN(INDIRECT($D$3)))))

    Copy down this to a number of rows that is more than the expected number of rows in column A of Data.

    In D2 enter:

    =ADDRESS(2,1)&":"&ADDRESS(NumRecs,1)

    In D3 enter:

    =ADDRESS(2,2)&":"&ADDRESS(NumRecs,2)

    In D4 enter:

    =ADDRESS(2,3)&":"&ADDRESS(NumRecs,3)

    In D5 enter:

    =SUMPRODUCT((LEN(OFFSET(C2,0,0,NumRecs,1))>0)+0)

    The rest you know from my previous reply, that is, how to use this [b]List[/i] as Input range for the ListBox in your main file.

    Addendum: To array-enter a formula you need to hit control+shift+enter at the same time, not just enter.

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-04-24 15:18 ]

    [ This Message was edited by: Aladin Akyurek on 2002-04-26 12:29 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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