Hi,
I'm populating a list box from an Excel workbook with student's names and
each student can have as many as 25 columns of test scores stored on a
spreadsheet, within the workbook. The list box displays each student's name
only which the teacher will select the appropriate names and then populate a
new classroom roster in a new spreadsheet. My question is, how do I get the
available columns of test scores to populate in the adjoining columns of the
new classroom roster? The teacher needs to be able to see the test scores of
the incoming class to adjust the curriculum in preparation for the State
assessment test. Any help would be greatly appreciated. Thanks.
Mark

Well, the list box ends up populating a cell somewhere in the Workbook,
right? I mean, once the teacher has selected a student from the drop-down
list, some cell like A5 now has the student's name in it? And you want B5:Z5
to contain the 25 test scores from elsewhere in the workbook? And those test
scores are listed across from the student's name? So why isn't it just a
matter of a VLOOKUP in the worksheet itself? What am I missing?

--- "Mark" wrote:
I'm populating a list box from an Excel workbook with student's names and
each student can have as many as 25 columns of test scores stored on a
spreadsheet, within the workbook. The list box displays each student's name
only which the teacher will select the appropriate names and then populate a
new classroom roster in a new spreadsheet. My question is, how do I get the
available columns of test scores to populate in the adjoining columns of the
new classroom roster? The teacher needs to be able to see the test scores of
the incoming class to adjust the curriculum in preparation for the State
assessment test.

Thanks, Bob.
They are actually two different workbooks. Let's say a 5th grade teacher
wants to populate her current roster with students that were 4th graders,
last year. All 4th grade test scores from last year are kept in a workbook
called "4th grade repository" and the list box is populated with all of last
years 4th graders across the district (approximately 1100). The teacher opens
a seperate document based on a template that displays the list box and she
makes her selections accordingly. I didn't know if it were possible to
populate the list box with all 1100 students via an array and suppress the
remaining columns or not, thus my question. I may be making this harder than
it really is, so I'm open to any suggestions.
Mark

"Bob Bridges" wrote:

Well, the list box ends up populating a cell somewhere in the Workbook,
right? I mean, once the teacher has selected a student from the drop-down
list, some cell like A5 now has the student's name in it? And you want B5:Z5
to contain the 25 test scores from elsewhere in the workbook? And those test
scores are listed across from the student's name? So why isn't it just a
matter of a VLOOKUP in the worksheet itself? What am I missing?

--- "Mark" wrote:
I'm populating a list box from an Excel workbook with student's names and
each student can have as many as 25 columns of test scores stored on a
spreadsheet, within the workbook. The list box displays each student's name
only which the teacher will select the appropriate names and then populate a
new classroom roster in a new spreadsheet. My question is, how do I get the
available columns of test scores to populate in the adjoining columns of the
new classroom roster? The teacher needs to be able to see the test scores of
the incoming class to adjust the curriculum in preparation for the State
assessment test.

The fact that the test scores are in a separate workbook is a minor
complication only; VLOOKUP can still look them up for you, it just requires a
little additional information in the lookup-table argument to tell Excel
where to look for the table data. But it sounds like you've changed
questions: In the original post, I understood you to have already populated
the list box and "My question is, how do I get the available columns of test
scores to populate in the adjoining columns of the new classroom roster?".

But in this round it sounds like you want to know whether "it were possible
to populate the list box with all 1100 students via an array and suppress the
remaining columns". So increasingly I think I don't know what you really
want. I don't use list boxes much, so I can't tell you what the population
limit might be. But I should think you've already tried it and know the
answer, so I'm not sure what you were saying here. And I don't know what you
meant by "suppress the remaining columns", either. You'll have to rephrase
it, I'm afraid, or explain more.

As for the VLOOKUP, you can look it up to get more information if you've
never used it but basically the call is VLOOKUP(<Text,<Table,<RelCol,0).
The <Text is the student's name that you're looking up, the <Table is the
table of student names and grades you're looking it up in, <RelCol is the
column you want to retrieve once VLOOKUP has found the student's name and 0
means the table is not necessarily sorted on student name and you want an
exact match on the name, not just something close. (Of course I realize you
may not be doing the lookup on the student's name; you could be using student
number or something, but I figure you can adapt.) What we're looking at here
is how to specify the table:

If the table is somewhere in the same worksheet, you just have to specify
the rows and columns:
VLOOKUP(<Text,N10:P43,<RelCol,0)

If the table is in the same workbook but a different worksheet, you have to
specify the worksheet's name like this:
VLOOKUP(<Text,WorksheetNam!N10:P43,<RelCol,0)

....or, if there's a space in the worksheet name,
VLOOKUP(<Text,'Worksheet name'!N10:P43,<RelCol,0)

If it's in a different workbook entirely, you have to specify the path and
filename:
VLOOKUP(<Text,'S:\Path\More Path\[4th grade repository.xls]Worksheet
name'!N10:P43,<RelCol,0)

Easier than trying to spell out all that and make sure you get all the
string delimiters in the right positions is to point Excel to the other
workbook and let it fill in the proper syntax. Like this:

1) Open the roster workbook.
2) Open the repository.
3) In a blank cell in the roster, type "=VLOOKUP(A1," and stop there. DON'T
HIT <Enter, just continue to the next step.
4) Using mouse or <Ctl keys, whichever you prefer, switch to the repository
workbook and select the cells that compose the table of student names and
their scores. Notice that in the formula bar, the partially-built formula is
still there and Excel is filling in the reference to these cells. Again, DO
NOT HIT <Enter.
5) Finish the formula by typing ",2,0)". Now you may hit <Enter.

While the repository workbook is open in Excel, the reference to it in your
VLOOKUP formula shows just the file name; after you close it, it'll probably
show the drive and path too. I have occasionally found that this reference
won't work unless the target table (the repository in this case) is actually
open in Excel; other times this is not the case. I'm not sure why, but I
think it has to do with the inaccessible workbooks being on a shared drive,
but I haven't pinned it down any closer than that.

Now you have a working VLOOKUP formula, but you may not have all the right
arguments in there. For example, if you filled in "A1" as I told you to, now
you may have to fill in the right reference for the student's name in this
row. And I said to use 2 for the third argument, but that's right only if
the value you want to look up is in the next column to the right of the
student's name in the table. And of course you'll have to copy the formula
to all the other rows in the roster, too.

--- "Mark" wrote:
Thanks, Bob.
They are actually two different workbooks. Let's say a 5th grade teacher
wants to populate her current roster with students that were 4th graders,
last year. All 4th grade test scores from last year are kept in a workbook
called "4th grade repository" and the list box is populated with all of last
years 4th graders across the district (approximately 1100). The teacher opens
a separate document based on a template that displays the list box and she
makes her selections accordingly. I didn't know if it were possible to
populate the list box with all 1100 students via an array and suppress the
remaining columns or not, thus my question. I may be making this harder than
it really is, so I'm open to any suggestions.

"Bob Bridges" wrote:
Well, the list box ends up populating a cell somewhere in the Workbook,
right? I mean, once the teacher has selected a student from the drop-down
list, some cell like A5 now has the student's name in it? And you want B5:Z5
to contain the 25 test scores from elsewhere in the workbook? And those test
scores are listed across from the student's name? So why isn't it just a
matter of a VLOOKUP in the worksheet itself? What am I missing?

--- "Mark" wrote:
I'm populating a list box from an Excel workbook with student's names and
each student can have as many as 25 columns of test scores stored on a
spreadsheet, within the workbook. The list box displays each student's name
only which the teacher will select the appropriate names and then populate a
new classroom roster in a new spreadsheet. My question is, how do I get
the available columns of test scores to populate in the adjoining columns of
the new classroom roster? The teacher needs to be able to see the test
scores of the incoming class to adjust the curriculum in preparation for the
State assessment test.

Bob,
I think we're close, but I'm a real idiot when it comes to Excel VBA, so
I'll attach the code (I have changed some of the names since my original
post) that I'm using to populate the list box. This is just preliminary and
on my home machine only:

Private Sub UserForm_Activate()
Dim wkbk As Workbook
Dim rng As Range
Application.ScreenUpdating = False
Set wkbk = Workbooks.Open(FileName:="C:\Documents and Settings\HP_Owner\My
Documents\Repository.xls")
With wkbk.Worksheets("Grade 4")
Set rng = Intersect(.Range("A:Z").EntireColumn, .UsedRange)
End With
UserForm1.ListBox1.ColumnCount = 26
UserForm1.ListBox1.List = rng.Value
wkbk.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub

What I'm trying to do is simply populate the list box with the names and
reduce the clutter in it, which I can do by changing the range to "A:A", yet
still retrieve the data associated with the name (from the repository
workbook) and populate the new spreadsheet. I'll have to play with the
VLOOKUP function to see if I understand it and I do appreciate your help.


"Bob Bridges" wrote:

The fact that the test scores are in a separate workbook is a minor
complication only; VLOOKUP can still look them up for you, it just requires a
little additional information in the lookup-table argument to tell Excel
where to look for the table data. But it sounds like you've changed
questions: In the original post, I understood you to have already populated
the list box and "My question is, how do I get the available columns of test
scores to populate in the adjoining columns of the new classroom roster?".

But in this round it sounds like you want to know whether "it were possible
to populate the list box with all 1100 students via an array and suppress the
remaining columns". So increasingly I think I don't know what you really
want. I don't use list boxes much, so I can't tell you what the population
limit might be. But I should think you've already tried it and know the
answer, so I'm not sure what you were saying here. And I don't know what you
meant by "suppress the remaining columns", either. You'll have to rephrase
it, I'm afraid, or explain more.

As for the VLOOKUP, you can look it up to get more information if you've
never used it but basically the call is VLOOKUP(<Text,<Table,<RelCol,0).
The <Text is the student's name that you're looking up, the <Table is the
table of student names and grades you're looking it up in, <RelCol is the
column you want to retrieve once VLOOKUP has found the student's name and 0
means the table is not necessarily sorted on student name and you want an
exact match on the name, not just something close. (Of course I realize you
may not be doing the lookup on the student's name; you could be using student
number or something, but I figure you can adapt.) What we're looking at here
is how to specify the table:

If the table is somewhere in the same worksheet, you just have to specify
the rows and columns:
VLOOKUP(<Text,N10:P43,<RelCol,0)

If the table is in the same workbook but a different worksheet, you have to
specify the worksheet's name like this:
VLOOKUP(<Text,WorksheetNam!N10:P43,<RelCol,0)

...or, if there's a space in the worksheet name,
VLOOKUP(<Text,'Worksheet name'!N10:P43,<RelCol,0)

If it's in a different workbook entirely, you have to specify the path and
filename:
VLOOKUP(<Text,'S:\Path\More Path\[4th grade repository.xls]Worksheet
name'!N10:P43,<RelCol,0)

Easier than trying to spell out all that and make sure you get all the
string delimiters in the right positions is to point Excel to the other
workbook and let it fill in the proper syntax. Like this:

1) Open the roster workbook.
2) Open the repository.
3) In a blank cell in the roster, type "=VLOOKUP(A1," and stop there. DON'T
HIT <Enter, just continue to the next step.
4) Using mouse or <Ctl keys, whichever you prefer, switch to the repository
workbook and select the cells that compose the table of student names and
their scores. Notice that in the formula bar, the partially-built formula is
still there and Excel is filling in the reference to these cells. Again, DO
NOT HIT <Enter.
5) Finish the formula by typing ",2,0)". Now you may hit <Enter.

While the repository workbook is open in Excel, the reference to it in your
VLOOKUP formula shows just the file name; after you close it, it'll probably
show the drive and path too. I have occasionally found that this reference
won't work unless the target table (the repository in this case) is actually
open in Excel; other times this is not the case. I'm not sure why, but I
think it has to do with the inaccessible workbooks being on a shared drive,
but I haven't pinned it down any closer than that.

Now you have a working VLOOKUP formula, but you may not have all the right
arguments in there. For example, if you filled in "A1" as I told you to, now
you may have to fill in the right reference for the student's name in this
row. And I said to use 2 for the third argument, but that's right only if
the value you want to look up is in the next column to the right of the
student's name in the table. And of course you'll have to copy the formula
to all the other rows in the roster, too.

--- "Mark" wrote:
Thanks, Bob.
They are actually two different workbooks. Let's say a 5th grade teacher
wants to populate her current roster with students that were 4th graders,
last year. All 4th grade test scores from last year are kept in a workbook
called "4th grade repository" and the list box is populated with all of last
years 4th graders across the district (approximately 1100). The teacher opens
a separate document based on a template that displays the list box and she
makes her selections accordingly. I didn't know if it were possible to
populate the list box with all 1100 students via an array and suppress the
remaining columns or not, thus my question. I may be making this harder than
it really is, so I'm open to any suggestions.

"Bob Bridges" wrote:
Well, the list box ends up populating a cell somewhere in the Workbook,
right? I mean, once the teacher has selected a student from the drop-down
list, some cell like A5 now has the student's name in it? And you want B5:Z5
to contain the 25 test scores from elsewhere in the workbook? And those test
scores are listed across from the student's name? So why isn't it just a
matter of a VLOOKUP in the worksheet itself? What am I missing?

--- "Mark" wrote:
I'm populating a list box from an Excel workbook with student's names and
each student can have as many as 25 columns of test scores stored on a
spreadsheet, within the workbook. The list box displays each student's name
only which the teacher will select the appropriate names and then populate a
new classroom roster in a new spreadsheet. My question is, how do I get
the available columns of test scores to populate in the adjoining columns of
the new classroom roster? The teacher needs to be able to see the test
scores of the incoming class to adjust the curriculum in preparation for the
State assessment test.

Ah, you're using a form! You say it's preliminary so I guess you've at least
tried it and it's populating the ListBox, right? So that much is right.
Then what? You get your code to put the selected name in column A of the
current row, and then display the form again, 'round and 'round until the
teacher clicks on the No-More-Students button meaning that all the names are
now in place. Right?

Or what? See, you've told me here what you intend, but so far I don't hear
a question....?

--- "Mark" wrote:
I think we're close, but I'm a real idiot when it comes to Excel VBA, so
I'll attach the code (I have changed some of the names since my original
post) that I'm using to populate the list box. This is just preliminary and
on my home machine only:

Private Sub UserForm_Activate()
Dim wkbk As Workbook
Dim rng As Range
Application.ScreenUpdating = False
Set wkbk = Workbooks.Open(FileName:="C:\Documents and Settings\HP_Owner\My Documents\Repository.xls")
With wkbk.Worksheets("Grade 4")
Set rng = Intersect(.Range("A:Z").EntireColumn, .UsedRange)
End With
UserForm1.ListBox1.ColumnCount = 26
UserForm1.ListBox1.List = rng.Value
wkbk.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub

What I'm trying to do is simply populate the list box with the names and
reduce the clutter in it, which I can do by changing the range to "A:A", yet
still retrieve the data associated with the name (from the repository
workbook) and populate the new spreadsheet. I'll have to play with the
VLOOKUP function to see if I understand it and I do appreciate your help.

"Bob Bridges" wrote:
The fact that the test scores are in a separate workbook is a minor
complication only; VLOOKUP can still look them up for you, it just requires a
little additional information in the lookup-table argument to tell Excel
where to look for the table data. But it sounds like you've changed
questions: In the original post, I understood you to have already populated
the list box and "My question is, how do I get the available columns of test
scores to populate in the adjoining columns of the new classroom roster?".

But in this round it sounds like you want to know whether "it were possible
to populate the list box with all 1100 students via an array and suppress the
remaining columns". So increasingly I think I don't know what you really
want. I don't use list boxes much, so I can't tell you what the population
limit might be. But I should think you've already tried it and know the
answer, so I'm not sure what you were saying here. And I don't know what
you meant by "suppress the remaining columns", either. You'll have to
rephrase it, I'm afraid, or explain more.

As for the VLOOKUP, you can look it up to get more information if you've
never used it but basically the call is VLOOKUP(<Text,<Table,<RelCol,0).
The <Text is the student's name that you're looking up, the <Table is the
table of student names and grades you're looking it up in, <RelCol is the
column you want to retrieve once VLOOKUP has found the student's name and
0 means the table is not necessarily sorted on student name and you want an
exact match on the name, not just something close. (Of course I realize you
may not be doing the lookup on the student's name; you could be using student
number or something, but I figure you can adapt.) What we're looking at here
is how to specify the table:

If the table is somewhere in the same worksheet, you just have to specify
the rows and columns:
VLOOKUP(<Text,N10:P43,<RelCol,0)

If the table is in the same workbook but a different worksheet, you have to
specify the worksheet's name like this:
VLOOKUP(<Text,WorksheetNam!N10:P43,<RelCol,0)

...or, if there's a space in the worksheet name,
VLOOKUP(<Text,'Worksheet name'!N10:P43,<RelCol,0)

If it's in a different workbook entirely, you have to specify the path and
filename: VLOOKUP(<Text,'S:\Path\More Path\[4th grade repository.xls]
Worksheet name'!N10:P43,<RelCol,0)

Easier than trying to spell out all that and make sure you get all the
string delimiters in the right positions is to point Excel to the other
workbook and let it fill in the proper syntax. Like this:

1) Open the roster workbook.
2) Open the repository.
3) In a blank cell in the roster, type "=VLOOKUP(A1," and stop there. DON'T
HIT <Enter, just continue to the next step.
4) Using mouse or <Ctl keys, whichever you prefer, switch to the repository
workbook and select the cells that compose the table of student names and
their scores. Notice that in the formula bar, the partially-built formula is
still there and Excel is filling in the reference to these cells. Again, DO
NOT HIT <Enter.
5) Finish the formula by typing ",2,0)". Now you may hit <Enter.

While the repository workbook is open in Excel, the reference to it in your
VLOOKUP formula shows just the file name; after you close it, it'll probably
show the drive and path too. I have occasionally found that this reference
won't work unless the target table (the repository in this case) is actually
open in Excel; other times this is not the case. I'm not sure why, but I
think it has to do with the inaccessible workbooks being on a shared drive,
but I haven't pinned it down any closer than that.

Now you have a working VLOOKUP formula, but you may not have all the right
arguments in there. For example, if you filled in "A1" as I told you to, now
you may have to fill in the right reference for the student's name in this
row. And I said to use 2 for the third argument, but that's right only if
the value you want to look up is in the next column to the right of the
student's name in the table. And of course you'll have to copy the formula
to all the other rows in the roster, too.

--- "Mark" wrote:
They are actually two different workbooks. Let's say a 5th grade teacher
wants to populate her current roster with students that were 4th graders,
last year. All 4th grade test scores from last year are kept in a workbook
called "4th grade repository" and the list box is populated with all of last
years 4th graders across the district (approximately 1100). The teacher
opens a separate document based on a template that displays the list box
and she makes her selections accordingly. I didn't know if it were possible to
populate the list box with all 1100 students via an array and suppress the
remaining columns or not, thus my question. I may be making this harder
than it really is, so I'm open to any suggestions.

"Bob Bridges" wrote:
Well, the list box ends up populating a cell somewhere in the Workbook,
right? I mean, once the teacher has selected a student from the drop-
down list, some cell like A5 now has the student's name in it? And you
want B5:Z5 to contain the 25 test scores from elsewhere in the
workbook? And those test scores are listed across from the student's
name? So why isn't it just a matter of a VLOOKUP in the worksheet itself?

--- "Mark" wrote:
I'm populating a list box from an Excel workbook with student's names
and each student can have as many as 25 columns of test scores stored
on a spreadsheet, within the workbook. The list box displays each
student's name only which the teacher will select the appropriate names
and then populate a new classroom roster in a new spreadsheet. My
question is, how do I get the available columns of test scores to
populate in the adjoining columns of the new classroom roster? The
teacher needs to be able to see the test scores of the incoming class to
adjust the curriculum in preparation for the State assessment test.