Listbox linking

excelmacro · Apr 24th, 2002, 12:48 PM

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

Aladin Akyurek · Apr 24th, 2002, 01:04 PM

Quote:

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

excelmacro · Apr 24th, 2002, 01:19 PM

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?

Aladin Akyurek · Apr 24th, 2002, 01:25 PM

Quote:

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 ]

excelmacro · Apr 24th, 2002, 01:40 PM

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?

Aladin Akyurek · Apr 24th, 2002, 01:53 PM

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 ]

excelmacro · Apr 24th, 2002, 01:57 PM

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.

Aladin Akyurek · Apr 24th, 2002, 02:43 PM

Quote:

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

excelmacro · Apr 24th, 2002, 03:12 PM

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.

Aladin Akyurek · Apr 24th, 2002, 04:10 PM

Quote:

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 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 24th, 2002, 12:48 PM	#1
excelmacro Board Regular Join Date: Apr 2002 Location: Alberta, Canada Posts: 50	I have a listbox but i want to use a rowsource that isn't in the same file. how do i do this?

Apr 24th, 2002, 01:19 PM	#3
excelmacro Board Regular Join Date: Apr 2002 Location: Alberta, Canada Posts: 50	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?

Apr 24th, 2002, 01:40 PM	#5
excelmacro Board Regular Join Date: Apr 2002 Location: Alberta, Canada Posts: 50	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?

Apr 24th, 2002, 01:53 PM	#6
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	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 ]

Apr 24th, 2002, 01:57 PM	#7
excelmacro Board Regular Join Date: Apr 2002 Location: Alberta, Canada Posts: 50	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.

Apr 24th, 2002, 03:12 PM	#9
excelmacro Board Regular Join Date: Apr 2002 Location: Alberta, Canada Posts: 50	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.