Listbox linking

excelmacro

Board Regular
Joined
Apr 8, 2002
Messages
50
I have a listbox but i want to use a rowsource that isn't in the same file. how do i do this?
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

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

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top