Help needed with userform label and Index/Match.

[mrscottjr]

Hi all,
I'm looking for some help with getting userform label's caption to be the result of an index/match formula.

Here's the setup:
Two sheets, each with a table.
Sheet1 has Table1, Sheet2 has Table2.
Both tables are generally set up the same as far as column titles go.
The userform is opened by double-clicking a student's name in the Table1[Student Name] column (Column "A").
On my userform I have two columns of labels set up. The first column represents a student's current grade (LblCurrGrade) from Table1[Student Grade], the second column needs the student's previous grade (LblPrevGrade) from Table2[Student Grade].

Given that the userform is called by double clicking on the a name in Table1[Student Name], the label of LblCurrGrade is simple enough by writing the code as:

LblCurrGrade.Caption = Cells(ActiveCell.Row, "A").Value

I'm struggling on how to write the code to pull in that student's previous grade as scene on Sheet2/Table2. I'm assuming there could be a way to use Index/Match to grab this grade based on matching the Student Names from each table, but not sure if that's correct or how to write it to avoid errors.

Also, if there's a way to refer to the table field rather than "Cells(ActiveCell.Row, "A").Value", it would be ideal given that some users may alter the column placements in the tables.

Any suggestions?

 

[Norie]

Should be straightforward, what are the column headings in the second table?

For the second part can't you just use ActiveCell on its own to get the student name?

 

[mrscottjr]

Thanks for the reply. The column headings on the second table are the same as on the first table. So in this example both tables contain [Student Name] and [Grade].

I'm not certain what you mean in the second line of your response.

 

[mrscottjr]

Any thoughts gang? I've been plugging away at this today but with no success. It feels like it should be an easy solution but it's just not clicking in my head...