Excel - Create one to one relationship between tables
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?
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.
- Constrain a Specman list so it doesn't have identical values in consecutive elements
- How to type cast a list of uint to a list of vr_ahb_data in Specman?
- Static fields/methods in e
- What is the difference between deep_copy and gen keeping in Specman?
- Specman UVM: What is the difference between write_reg { .field == 2;}; and write_reg_fields?
- Does Specman support optional parameters to a method?
- Specman e: When colon equal sign ":=" should be used?
- Specman e: Is there a way to know how many values there is in an enumerated type?
- Specman e error: No match for file when using "for each line in file"
- How to run e file one by one? Not in parallel test
- Specman/e constraint (for each in) iteration
- Specman e: How driver's items queue can be locked from a sequence?
- Specman e: How to print variable's address?
- Specman e: "keep type .. is a" fails to refine the type of a field
- Specman soft select on variable, decimal vs. hexadecimal values
- Specman e: How to print a pointer to a struct?
- Specman e: Is there a way to print unit instance name?
- Specman e: simultaneous events error
- Specman e coverage: ignored values appear in the coverage statistics
- Specman e: How a sequence should be started when gen_and_start_main constrained to FALSE?
- Specman/e list of lists (multidimensional array)
- Does Specman e have struct constructor?
- Specman e: How the predefined sequence.item should be used?
- Specman e: define-as-computed macro error
- Specman e UVM: Why to inherit from uvm_* units?
- Specman e: A sequence drives its BFM also its MAIN was not defined in a test
- e HVL (IEEE 1647): expect expression fails unexpectedly
- Specman e subtyping: How to refer to FALSE value of conditional field in when/extend subtyping?
- e HVL (IEEE 1647): How to set 'X' value?
- Difference between declaring an event that is sensitive to a simple_port value and event_port