Get last n row data from Columns in another sheet based on cell value using drop-down list

[analystbank]

Hello Seniors, and Talents,

Attached is sample workbook, wherein;

Sheet 2 is Input (RawData) from where I want to retrieve data in Sheet1 as follows:

Sheet2 looks like -
Student.jpg

1) In Sheet1, Cell B11 currently I am using Data validation list (drop down list), from where I can select student name from range Sheet2!B2:K2. This could extend to column L or upto z or so (possibly sometime more than 200 students!), so flexibility is required.

2) Based on Cell B11 cell value, where I select student name from Sheet2, I want to retrieve corresponding student name, LAST 6 TRANSACTIONS as shown in attached workbook, i.e. in range Sheet 1 ! C12:D17.
StudentScore.jpg
3) Please do note that in Sheet2, such dates, and marks will keep adding at bottom in rows, so I want to retrieve only last six record from bottom.

4) Just to make it visually clear, I have manually shown last 6 row data corresponding to Student named Julie from Sheet2 in Sheet1! C12:D17

5) Seek Help, how to avoid two appearance of same student name. If I put common name across two columns (deleting data from Sheet2 in cells C2, E2, G2 etc I get blank space under dropdown list in between two student name.

What combination of OFFSET or MATCH/INDEX I should use to get above desired result. I tried several ways, but somewhere I am going wrong on concepts.

How to keep formula flexible, such that, if student names expands in columns , and data gets added in rows, it gives the desired result.

I don’t want VBA Macro code for above. Thanks.

I keep switching between MS-Excel 1997-2003, so sometime using Combo Box made in higher version gets deleted, so am not using that but preferring data validation approach in cell to retrieve student name. So, please help, that it should work across possibly all version.

[analystbank]

There will not be duplicate student name in Sheet2 ColumnB2 onwards, no need to think on that line, Thanks

[TMS]

Cell C12:

Please Login or Register  to view this content.

Cell D12:

Please Login or Register  to view this content.

both copied down.

[analystbank]

@TMS, thanks sir, It works well in current range of data, but fails, when

1) i add new student in Column L, M in same pattern , Date and Score, and also, (this can be taken care of adjusting -

Please Login or Register  to view this content.

but then how to adjust automatically each time for new added row of data...that is..
2) if i add, new row data in Sheet2 from Row12 onwards, it returns error, and does not retrieve, last 6 records counted from bottom.

Possible to make use of Counta () function?

[TMS]

This:

Please Login or Register  to view this content.

finds the last row, and this:

Please Login or Register  to view this content.

adjusts the row to be returned.

I can't see any reason why that shouldn't return the last 6 rows regardless of how many rows you add.

Please upload a sample workbook that demonstrates the problem.

[analystbank]

thanks for your quick feedback, i missed to put data in Sheet2!ColumnA:A, and simply tested data with date and score.

That answers all queries, with nice solution. Thank you, Sir. It works, and thanks for providing that insight to column A, which acts like handler...

P.S.:Workbook is same as was posted in #1 above, and since spotted the mistake that i was doing, not posting it again.

[TMS]

You're welcome.

[analystbank]

You have answered well.

Just one clarification, (i know this is against forum rules, to ask extra then original question), on one issue, which i could not envisage earlier, In the above (#1) sample book, in Sheet1 ! Column E, how to retrieve / get corresponding value from Sheet2!ColumnA:A, which only has value, HMark or LMark. We dont have to consider Column Label given in cell A2 as "Student" in Sheet2.

something like

StudentScore1.jpg
This would only facilitate high or low mark tag, please if you can help here, and dont find it offending.

Thanks

[TMS]

Try:

Please Login or Register  to view this content.

[analystbank]

Thanks for all, Done, very nicely.

[TMS]

You're welcome. Thanks for the rep.