0

I have a workbook for controlling attendance of students on a course, modelled on many worksheets. The first worksheet have the cadastral data for all students. The other ones have attedence data of each month. I need to dinamically update the first column of the attendance worksheets according with students column on cadastral data worksheet.

VLOOKUP or MATCH/INDEX seems not to be a good solution because data can be added on cadastral data worksheet, and I need to always re-organize students to alphabetical order.

For example:

Cadastral Data:

Student | (many data ccolumns)

Ann | data

Charlie | data

Jack | data

February Attendence:

Student | Day 01 | Day 02 | Day 03 ...

Ann | ok ok nok

Charlie | ok ok ok

Jack | ok nok ok

If I use VLOOKUP or INDEX/MATCH and add Daniel on cadastral data and then sort alphabetically, I'll have this issue:

Cadastral Data:

Student | (many data ccolumns)

Ann | data

Charlie | data

Daniel | data

Jack | data

February Attendence:

Student | Day 01 | Day 02 | Day 03 ...

Ann | ok ok nok

Charlie | ok ok ok

Daniel | ok nok ok

Jack |

Jack Data changed to Daniel.

Is there a simple solution for this?

Rafael Leite
  • 75
  • 7

1 Answers1

0

I am not too sure how you are using INDEX/MATCH... but this is how I would do it.

I would add an ID column to each table. That way the data, whether it is Cadastral data (including the students name) or attendance data, is associated with that unique ID and you can then link the two tables using this ID.

In this way, both tables can be sorted and filtered entirely independently of each other.

enter image description here

Gravitate
  • 2,885
  • 2
  • 21
  • 37
  • I tried to use VLOOKUP by using a column similar to this ID, with a numerical sequence. – Rafael Leite Feb 22 '19 at 14:27
  • Sorry, submited last comment before finishing text by pressing enter trying do add a linebreak. I tried to use VLOOKUP by using a column similar to this ID, with a numerical sequence. But I had put this column apart of sorting filter, to keep it always on numerical order . As I really don't need to keep it on numerical orer, this is a good workaround. But I was searching for a solution that I don't need to update all tables order everytime I add a new student. I googled some PowerPivot features, but not found a solution. – Rafael Leite Feb 22 '19 at 14:49
  • Ah, I see. No. It is a bit tricky to make tables automatically refresh the sort order (or filter) when the base data is changed. You can do it with VBA bit its a bit hit and miss and just seems like over kill for such a minor thing. Sorry. – Gravitate Feb 22 '19 at 14:54