Vlook up 2 sheets latest date selection from other sheet

[krandhawa5]

Hi,

Please email me at [removed] to send you the data on excel 2016.

Regards

Karan

 

[bellman101]

Hi there,

Can you at least give a brief summary of what you need. It sounds like it would be quite straightforward?

Is it just the highest date in a certain column, or range of columns on each sheet?

Regards,
JB

 

[krandhawa5]

Hi there,

Can you at least give a brief summary of what you need. It sounds like it would be quite straightforward?

Is it just the highest date in a certain column, or range of columns on each sheet?

Regards,
JB

Hi JB,

Thanks for replying me. I have not used this forum for long so struggling a bit to pass info over to you. I try my best here below:

Sheet 1 has list of all students in column B.
Sheet 2 has marks obtained in 5 different subjects (MATHS , PHY, CHEM, COMP, HISTORY)in separate columns next to each students, but on different test dates and centres.
Q1: How can I get the latest test date marks here on sheet 1 automatically from "sheet 2" sheet, along with their test centre in that recent test.
Q2 : How can I highlight the marks range between 1 to 10 (RED) 11 to 14 (AMBER) 15-25 GREEN in sheet 1.


I hope I am able to explain.

Regards
Karan

 

[bellman101]

Well let's start with question 2. as that is by far the easiest to tackle. Just select the whole marks range, On the Home tab in the ribbon, Conditional Formatting => Highlight cell rules. Set 3 new rules for the same selection. One for less than 11, one between 11 and 14 and one greater than 14.

As for Q1, are the columns on Sheet2 like this?

Student | MathsDate | MathsCentre | MathsScore | PhyDate | PhyCentre | PhyScore | etc

 

[krandhawa5]

Well let's start with question 2. as that is by far the easiest to tackle. Just select the whole marks range, On the Home tab in the ribbon, Conditional Formatting => Highlight cell rules. Set 3 new rules for the same selection. One for less than 11, one between 11 and 14 and one greater than 14.

As for Q1, are the columns on Sheet2 like this?

Student | MathsDate | MathsCentre | MathsScore | PhyDate | PhyCentre | PhyScore | etc[/QUOTE
Sheet 2 is A1 =TEST DATES, B1= NAMES , C1 = MATHS SCORE, D1E1F1G1 OTHER SUBJECTS, I1 =TEST CENTRE
MY QUESTION: I want this information on sheet 1 where I have A1 = NAMES, B1TO F1= SUBJECTS , G1=TEST CENTRE, H1 =RECENT TEST DATE.

 

[bellman101]

Ok, so only 1 date column. Does that mean that scores/marks are only populated if that subject's test was on that displayed date?

So for example:

Maths: 1st March 17
Physics: 1st May 17
Chemistry: 1st March 17
History: 1st May 17
Computer Studies: 1st May 17

In this case you would want the date as 1st May 17. The test centre as wherever they were on that date, and then in the scores, only Physics, History and Computer Studies would have a score, as the other 2 were on a prior date.

Have I got that right?

Cheers
JB

 

[krandhawa5]

I am happy that you are helping me to get there. I try to simplify it. Sheet 2 just added up by the marks of each candidate on weekly basis, along with data of previous weeks as well. Now on sheet one I don't bother about past weeks marks. All I want is latest, fresh, current week's marks to appear on sheet one. And all students appear for all subjects on a test day.

 

[bellman101]

Ok, so sheet2 is essentially just a data tab, like a small database table would look. This could be made a lot easier by adding one more column into your data, say column J, with a sequential number starting at 1 with the header "ID".

Then you can rely on this ID field in the final lookup as follows:

A2 down: Names - you fill manually, correct? Let's say A2 contains "John Jones" for the formulae below.
B2: =SUMIFS(Sheet2!C:C,Sheet2!A:A,H2,Sheet2!B:B,A2) - This is the score John achieved in Maths on the date displayed in H2.
C2: =SUMIFS(Sheet2!D:D,Sheet2!A:A,H2,Sheet2!B:B,A2) - This is the score John achieved in Physics on the date displayed in H2.
D2: =SUMIFS(Sheet2!E:E,Sheet2!A:A,H2,Sheet2!B:B,A2)
E2: =SUMIFS(Sheet2!F:F,Sheet2!A:A,H2,Sheet2!B:B,A2)
F2: =SUMIFS(Sheet2!G:G,Sheet2!A:A,H2,Sheet2!B:B,A2)
G2: =INDEX(Sheet2!I:I,MATCH(SUMIFS(Sheet2!J:J,Sheet2!A:A,H2,Sheet2!B:B,A2),Sheet2!J:J,0)) - Because you can't use aggregate functions like SUM() on text ranges, this uses the sumif to find the ID instead, and looks up off that.
H2: =MAX(Sheet2!A:A)

Please note, this is fully untested, as it seemed a lot of effort to recreate your table! but hopefully this will work for you.

Cheers

 

[krandhawa5]

Ok, so sheet2 is essentially just a data tab, like a small database table would look. This could be made a lot easier by adding one more column into your data, say column J, with a sequential number starting at 1 with the header "ID".

Then you can rely on this ID field in the final lookup as follows:

A2 down: Names - you fill manually, correct? Let's say A2 contains "John Jones" for the formulae below.
B2: =SUMIFS(Sheet2!C:C,Sheet2!A:A,H2,Sheet2!B:B,A2) - This is the score John achieved in Maths on the date displayed in H2.
C2: =SUMIFS(Sheet2!D:D,Sheet2!A:A,H2,Sheet2!B:B,A2) - This is the score John achieved in Physics on the date displayed in H2.
D2: =SUMIFS(Sheet2!E:E,Sheet2!A:A,H2,Sheet2!B:B,A2)
E2: =SUMIFS(Sheet2!F:F,Sheet2!A:A,H2,Sheet2!B:B,A2)
F2: =SUMIFS(Sheet2!G:G,Sheet2!A:A,H2,Sheet2!B:B,A2)
G2: =INDEX(Sheet2!I:I,MATCH(SUMIFS(Sheet2!J:J,Sheet2!A:A,H2,Sheet2!B:B,A2),Sheet2!J:J,0)) - Because you can't use aggregate functions like SUM() on text ranges, this uses the sumif to find the ID instead, and looks up off that.
H2: =MAX(Sheet2!A:A)

Please note, this is fully untested, as it seemed a lot of effort to recreate your table! but hopefully this will work for you.

Cheers

Thanks you very much BOSS. You were indeed helpful in automating my database. Thanks a tonne once again.

Cheers

Karan

 

[bellman101]

You're very welcome sir