List Box Columns
Mark
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
Bob Bridges
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
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
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.
>
Mark
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
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
multiple selections, prior to clicking on an 'Add Students' command button on
the form and then the students are added to the roster (name only with this
code).
Private Sub CommandButton1_Click()
Dim LastRow As Object
' Loop through the items in the ListBox.
For x = 0 To ListBox1.ListCount - 1
' If the item is selected...
If ListBox1.Selected(x) = True Then
Set LastRow = Sheet1.Range("a65536").End(xlUp)
LastRow.Offset(1, 0).Value = ListBox1.List(x)
Else
Unload Me
End If
Next x
End Sub
So, the two issues/questions that I have are, first, how can I populate or
display the list box with only the names of the students or is that even
possible? Secondly, how do I retrieve the record of the test scores
associated with the selected name, if I don't retrieve the entire record
initially? Right now, I can make it work, but it's convoluted and appears to
be very poor coding on my part. Am I making any sense or just confusing the
issue?
Mark
Bob Bridges
is showing more than just the student's name? ...Ok, I see (now that I look
more closely) that you're populating the list box with an intersection of
.UsedRange and columns A through Z. But why? I must repeat that I've never
used a list box in Excel, but it seems to me that if the student's name is in
column B, then you should use Intersect(.Range("B"), .UsedRange) instead.
Again, that looks too obvious so it's possible you just overlooked the
obvious (we all do that) but it's also possible I'm missing something.
As for the second question ("how do I retrieve the record of the test scores
associated with the selected name, if I don't retrieve the entire record
initially?"), that's why I keep talking about VLOOKUP. If the teacher fills
in a couple of students' names in the roster, like this:
A B C D
Bentley, Robert
Branning, Elizabeth B
Harking, Stephen
Homir, Nemo
Lemore, Louis
...then all you need to put in cols B, C, D etc is a VLOOKUP function that
looks up the value in column A in the repository table in that other
workbook. See below for some explanation of VLOOKUP, and of course it's in
the Excel documentation too.
Mark
last explanation. However, if I select A30 on a blank spreadsheet, put the
VLOOKUP (=VLOOKUP(A30,'[Repository.xls]Grade 4'!$A$1:$X$23,2,0) in each
remaining cell of the row, it only picks up the first column (B) and nothing
in the remaining columns. Thus, I can get the student and the first test
score, but that's it.
Bob Bridges
B from the repository, but that's easy to control from the third VLOOKUP
argument. In our example so far it's been a 2, but all that means is that
VLOOKUP will return column B from the table.
(Note: That's column B RELATIVE TO THE TABLE. If the students' names were
in column C, then the 2 in the third argument would pull column D; it just
means, you see, that it pulls the 2nd column from the left of the range
specified in the second argument.)
So one way to get every grade is to use VLOOKUP(A30,'[Repository.xls]Grade
4'!$A$1:$X$23,2,0) in column B, but VLOOKUP(A30,...,3,0) in column C,
=VLOOKUP(A30,...,4,0) in column D and so on. That'll get you every grade.
But for me that's too much work; I would probably require it to vary the
reference automatically, like this:
VLOOKUP(A30,'[Repository.xls]Grade 4'!$A$1:$X$23,COLUMN(),0)
Now the third argument is a 2 in column B, a 3 in column C and so on. That
formula you can copy throughout the whole range and it'll pull all the right
grades from the repository.
You can stop there and it'll work, but for future reference I'll warn you
that this technique, of using the column number to determine the column
reference in VLOOKUP, has a weakness: The next time you insert or delete a
column, anything to the right of it that depends on this technique will be
out of kilter, and you'll have to change every reference. Worse, it won't
necessarily be immediately obvious that it happened; after all, if your
column E shows the grade from column F, would you notice right away? I
maintain there must be a way around this weakness so I can get the best of
both, but I haven't sat down and figured it out yet.
--- "Mark" wrote:
> OK, I get it - I think - I just wasn't able to put it all together until your
> last explanation. However, if I select A30 on a blank spreadsheet, put the
> VLOOKUP (=VLOOKUP(A30,'[Repository.xls]Grade 4'!$A$1:$X$23,2,0) in each
> remaining cell of the row, it only picks up the first column (B) and nothing
> in the remaining columns. Thus, I can get the student and the first test
> score, but that's it.
>
> --- "Bob Bridges" wrote:
> > That's why I keep talking about VLOOKUP. If the teacher fills
> > in a couple of students' names in the roster, like this:
> >
> > A B C D
> > Bentley, Robert
> > Branning, Elizabeth B
> > Harking, Stephen
> > Homir, Nemo
> > Lemore, Louis
> >
> > ...then all you need to put in cols B, C, D etc is a VLOOKUP function that
> > looks up the value in column A in the repository table in that other
> > workbook. See below for some explanation of VLOOKUP, and of course it's in
> > the Excel documentation too.
> >
> > --- "Mark" wrote:
> > > ...how do I retrieve the record of the test scores associated with the selected
> > > name, if I don't retrieve the entire record initially?
> > > >
> > > > --- "Mark" wrote:
> > > > > 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
> > > > >
> > > > > --- "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.
> > > > > >
> > > > > > ....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
> > > > > > studentnames and their scores. Notice that in the formula bar, the
Mark
Your solution works great! Excellent piece of work! I really appreciate your
help and you're a real life saver. Thank you.
Mark
Mark
I have a (quick, I hope) question on the VLOOKUP function. Apparently, I'm
supposed to maintain conditional formatting in the columns that hold the test
scores. Therefore, I attempted to convert everything to VBA which has been
causing me problems all day. In the code below, I get Error 2015 on the
designated line, which I think is tied to the VLOOKUP function. The columns
on the spreadsheet, aside from column A, fill with #VALUE!. Any ideas on this
one?
Mark
Private Sub CommandButton1_Click()
Dim RowNdx As Long
Dim ColNdx As Integer
Dim SaveColNdx As Integer
Dim LastRow As Integer
Application.ScreenUpdating = False
ColNdx = 1
RowNdx = 20
' Loop through the items in the ListBox.
For x = 0 To ListBox1.ListCount - 1
' If the item is selected...
If ListBox1.Selected(x) = True Then
Cells(RowNdx + 1, 1).EntireRow.Insert
Rows(RowNdx).Copy
Rows(RowNdx + 1).PasteSpecial Paste:=xlFormats
Application.CutCopyMode = False
' display the Selected item.
Cells(RowNdx, 1).Value = ListBox1.List(x)
For t = 2 To 23
ERROR 2015 ==> Cells(RowNdx, t).Value = Application.VLookup(Cells(RowNdx,
ColNdx).Value, "C:\Documents and Settings\HP_Owner\My
Documents\Repository.xls Grade 4!$A$1:$X$23", t, 0)
Next t
RowNdx = RowNdx + 1
End If
Next x
Unload Me
Application.ScreenUpdating = True
End Sub
smartin
[snippens]
> VLOOKUP(A30,'[Repository.xls]Grade 4'!$A$1:$X$23,COLUMN(),0)
>
> Now the third argument is a 2 in column B, a 3 in column C and so on. That
> formula you can copy throughout the whole range and it'll pull all the right
> grades from the repository.
>
> You can stop there and it'll work, but for future reference I'll warn you
> that this technique, of using the column number to determine the column
> reference in VLOOKUP, has a weakness: The next time you insert or delete a
> column, anything to the right of it that depends on this technique will be
> out of kilter, and you'll have to change every reference. Worse, it won't
> necessarily be immediately obvious that it happened; after all, if your
> column E shows the grade from column F, would you notice right away? I
> maintain there must be a way around this weakness so I can get the best of
> both, but I haven't sat down and figured it out yet.
One alternative is to place column identifiers in both worksheets, and
use INDEX/MATCH. This gets around using a relative column reference so
the formula will not break if a column is inserted somewhere.
It might look like this in cell Z30 (this is an array formula, complete
with Ctrl+Shift+Enter):
=INDEX('[Repository.xls]Grade 4'!$A$1:$X$23,
MATCH(1,--($A30='[Repository.xls]Grade 4'!$A$1:$A$23),0),
MATCH(1,--(Z$1='[Repository.xls]Grade 4'!$A$1:$X$1),0))
(line breaks entered for clarity)
This assumes row $1 has the column identifiers you want to coordinate.
Dave Peterson
the table needs to be open.
Maybe you could plop the formula into the cell and then convert it to a value.
But you'll want to create the formula so that it looks like the formula you
would build by hand:
=VLOOKUP(A1,'C:\My Documents\Excel\[book1.xls]Sheet1'!$A:$B,2,FALSE)
with Cells(RowNdx, t)
.formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _
& ",'C:\Documents and Settings\HP_Owner\My Documents\'" _
& "[Repository.xls]Grade 4!$A$1:$X$23," & t & ", 0)"
.value = .value
end with
Untested--watch for typos.
--
Dave Peterson
Mark
smartin
Sorry I was not being totally clear.
In '[Repository.xls]Grade 4' suppose row 1 has labels (column
identifiers) like "Assignment 1", "Assignment 2", etc. This kind of
layout is typical.
For this example, in the worksheet looking up the values, place the
labels you want to look up in row 1. E.g., put "Assignment 1" in Z1.
Notice the 3rd parameter of the INDEX formula is
MATCH(1,--(Z$1='[Repository.xls]Grade 4'!$A$1:$X$1),0)
This basically says, "Get the value in row one and find it in the first
row of '[Repository.xls]Grade 4'. Use the column where we matched to
return the result."
You could also use VLOOKUP in this way as well. In the col_index_num
parameter, substitute the same MATCH.
Hope this helps!
Mark
"Run time error 1004 Applicatio defined or object defined error".
Dave Peterson
=VLOOKUP(A1,'C:\My Documents\Excel\[book1.xls]Sheet1'!$A:$B,2,FALSE)
with Cells(RowNdx, t)
.formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _
& ",'C:\Documents and Settings\HP_Owner\My Documents\" _
& "[Repository.xls]Grade 4'!$A$1:$X$23," & t & ", 0)"
.value = .value
end With
If that doesn't work, then change the formula so that it looks like a constant:
.formula = "$=vlookup(" & ....
Then the cell won't contain a formula--just that text. But you can manually
edit the formula (remove that leading $) and see what you can do to make it
right.
Post back with the formula that works.
--
Dave Peterson
Mark
That worked absolutely perfectly! Thank you so much for your help - I really
appreciate it.
Mark
"Dave Peterson" wrote:
> I had the closing apostrophe in the wrong spot:
>
> =VLOOKUP(A1,'C:\My Documents\Excel\[book1.xls]Sheet1'!$A:$B,2,FALSE)
>
> with Cells(RowNdx, t)
> .formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _
> & ",'C:\Documents and Settings\HP_Owner\My Documents\" _
> & "[Repository.xls]Grade 4'!$A$1:$X$23," & t & ", 0)"
> .value = .value
> end With
>
> If that doesn't work, then change the formula so that it looks like a constant:
>
> ..formula = "$=vlookup(" & ....
Mark
the VLOOKUP formula:
.formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _
& ",'C:\Documents and Settings\HP_Owner\My Documents\" _
& "[Repository.xls]Grade 4'!$A$1:$X$23," & t & ", 0)"
Specifically, the document path, the sheet name, and the range. I have the
document path declared as:
Public Const studentFile As String = "C:\Documents and Settings\HP_Owner\My
Documents\Repository.xls"
The sheet name as:
Public grade As String
And as far as the range, I thought:
Sheets(grade).UsedRange.address
I've tried to substitute these into the formula in various combinations, but
I can't seem to get anything to work. Does anyone have any ideas on this one?
Mark
"Dave Peterson" wrote:
> I had the closing apostrophe in the wrong spot:
>
> =VLOOKUP(A1,'C:\My Documents\Excel\[book1.xls]Sheet1'!$A:$B,2,FALSE)
>
> with Cells(RowNdx, t)
> .formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _
> & ",'C:\Documents and Settings\HP_Owner\My Documents\" _
> & "[Repository.xls]Grade 4'!$A$1:$X$23," & t & ", 0)"
> .value = .value
> end With
>
> If that doesn't work, then change the formula so that it looks like a constant:
>
> ..formula = "$=vlookup(" & ....
Dave Peterson
You'll want to split that studentfile string into two pieces--on with the path
and one with the filename. You could use code or just make a couple of
constants:
'include the trailing backslash
Constant myPath as string = "C:\documents and settings\HP_Owner\my documents\"
Constant myWkbkName as string = "Repository.xls"
constant myWorksheetName as string = "Grade 4"
constant myAddress as string = "$a$1:$x$23"
dim StudentFile as string
studentfile = mypath & mywkbkname
.formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _
& ",'" & myPath _
& "[" & myWkbkName & "]" _
& myworksheetname & "'!" _
& myAddress & "," & t & ", 0)"
=========
If that repository.xls workbook is open, then you could get the usedrange. But
if that workbook is closed, then you won't be able to.
--
Dave Peterson
Mark
within the VLOOKUP formula to keep me out of trouble? I know I can apparently
use $A$1:$X$500 without causing any problems, but I would like have it work
like populating the combo box. I've tried setting the score range the same,
but it fills the remaining cells with #NAME. Below is the Userform code as it
stands now and it does work correctly:
Private Sub UserForm_Activate()
Dim wkbk As Workbook
Dim Rng As Range
Dim scores As Range
Application.ScreenUpdating = False
Set wkbk = Workbooks.Open(FileName:=studentFile & myWkbkName)
With wkbk.Worksheets(grade)
Set Rng = Intersect(.Range("A:A").EntireColumn, .UsedRange)
'Can I set the "scores" range here?
End With
RosterForm.ListBox1.ColumnCount = Rng.Rows.Count
RosterForm.ListBox1.List = Rng.Value
wkbk.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub
Private Sub CommandButton1_Click()
Dim RowNdx As Long
Dim ColNdx As Integer
Dim SaveColNdx As Integer
Dim wkbk As Workbook
Application.ScreenUpdating = False
ColNdx = 1
RowNdx = 13
For X = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(X) = True Then
Cells(RowNdx + 1, 1).EntireRow.Insert
Rows(RowNdx).Copy
Rows(RowNdx + 1).PasteSpecial Paste:=xlFormats
Application.CutCopyMode = False
Cells(RowNdx, 1).Value = ListBox1.List(X)
For t = 2 To ActiveSheet.UsedRange.Columns.Count
With Cells(RowNdx, t)
.Formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _
& ",'" & studentPath & "[" & myWkbkName & "]" & grade &
"'!$A$1:$X$500," & t & ", 0)"
.Value = .Value
End With
Next t
RowNdx = RowNdx + 1
End If
Next X
Rows(RowNdx).Delete Shift:=xlUp
Unload Me
Application.ScreenUpdating = True
End Sub
The string "grade" is set based upon the User's selection prior to
populating the combo box.
"Dave Peterson" wrote:
> Untested!
>
> You'll want to split that studentfile string into two pieces--on with the path
> and one with the filename. You could use code or just make a couple of
> constants:
>
> 'include the trailing backslash
> Constant myPath as string = "C:\documents and settings\HP_Owner\my documents\"
> Constant myWkbkName as string = "Repository.xls"
> constant myWorksheetName as string = "Grade 4"
> constant myAddress as string = "$a$1:$x$23"
>
> dim StudentFile as string
> studentfile = mypath & mywkbkname
>
> ..formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _
Dave Peterson
not sure that it's a good idea to be so specific.
If you make changes to the Repository.xls workbook when the workbook that gets
the formula is closed, then you could have trouble.
Say your lookup range when you initially did the formula was A1:x23, but then
you added a few more rows of info to that table -- A1:X35 (say).
Your =vlookup() formulas won't adjust when you open the workbook with the old
formulas.
If I know that there's nothing under that table (and I like dedicating a single
table per worksheet), I could use the whole column: A:X
Not too dissimilar from your A1:X500, but just a bit more robust <bg>.
But if you want to open it and live dangerously:
With wkbk.Worksheets(grade)
Set Rng = Intersect(.Range("A:A").EntireColumn, .UsedRange)
'Can I set the "scores" range here?
'I think you want this:
set ScoresRng = rng.resize(, 24)
'resize with no rows specified means don't change that count of rows.
End With
I'm confused about what causes your #name error and even where the scoresrng is
used.
--
Dave Peterson
Mark
project. However, the number of students can change from the beginning to end
of the school year and I didn't want to short the list . I like your idea of
just using A:X, because the number of columns will always be fixed per grade
- 1st graders only have 11 columns, so I'll use that solution. I haven't
played with your solution, yet, but can the column be set to a variable
depending upon the grade selected? Say if the User selects 3rd grade on the
userform and there are 16 columns on sheet "3rd Grade" in the repository xls
can the range be adjusted accordingly through a variable ............
If optBtn3 then
grade ="3rd Grade"
column = P
end if
Then in the vlookup formula set the range to 'A:column' Does that make any
sense?
Dave Peterson
value right after inserting the formula.
Dim myAddress as string
myAddress = "A:"
....
if optbtn3 then
grade = "3rd grade"
myaddress = myaddress & "P"
end if
(I think???)
--
Dave Peterson
Mark
bother with it. Thanks for all of your help, I really appreciate it.
Mark
"Dave Peterson" wrote:
> You could, but I'm not sure I'd bother. Especially since you're converting to a
> value right after inserting the formula.
>
> Dim myAddress as string
> myAddress = "A:"
> .....
Dave Peterson
Good luck with the rest of your project.
--
Dave Peterson