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