• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic update of class list overwrites info and messes with my data

Hello all,

Must say this site is awesome!!!Haven't had a problem yet that there was not someone with a solution. Was even just able to use some of what I learned to help someone else.

Here is another problem that is bending my mind. I am using excel to track student assignments - missing, late and on time, using a time stamp formula.

I have also recently synced the spreadsheets to a CSV file created by the mark grading program we use, so I can import student mark, absences, and last and first name. I use this information, particularly the student names to populate all areas of my worksheets that require names.

So now the problem....as the semester starts, there is a bit of variability in the first few months with students coming and going from the class.

If I allow the names to be dynamically updated and overwrite the positions of the current names, all of the data would be skewed to the wrong person.

If someone could even point me in the right direction in terms of logic and formulas, I would try to work out how it fits my situation.

Thanks for any help,
 

Attachments

  • dynamicupdateproblem.xlsx
    14.9 KB · Views: 7
I am not sure how you are populating your data, from what I have understood from your query, I would suggest you to use look-up formulas , this way the change in the names won't affect the other data points. Hope I made sense...
 
The names are populated by the CSV export from the mark program. The in-sheet data is populated using checkboxes. Student names populate the left most column and then there are 24 columns of checkboxes.(one for each potential assignment) When a teacher clicks on the checkbox, it timestamps the date an assignment was submitted.

I really don't understand how look-up formulas would work to solve the problem.
 
You mean to say that the marks and absence column is manually entered based on the name generated from the CSV files??, If possible can you upload a sample file with some dummy data...
 
Ok, let me get this correct the sheet1 in the file you have uploaded is the raw data you import from a CSV file using a macro.


You are using this data as the base data to generate lots of other calculations in various tabs
I use this information, particularly the student names to populate all areas of my worksheets that require names.
if that is the case without knowing what calculations you re performing in other tabs it would be difficult to give a reasonable solution for your problem...
 
Not sure the other tabs really affect the problem.

1) I will initially populate the checkbox data entry sheet and my data calculations sheet with the student names generated by the CSV. What I do is have a macro copy and paste the initial values in the file above so that the names are static to begin with. This begins my setup but does not allow me to deal with the inevitable changes that will happen.
2) Now that I have names, when an assignment is submitted, I check a box and this then gives a static time stamp. This happens for each assignment.
3) The CSV file will update and change as students are removed or added.
4) If I allow the names to dynamically update with the CSV, the names and checkbox references/data will be out of order. Attached is a rough version of the checkbox structure. Ex/ #2 could be gone or there could be 5 more names interspersed with the current ones, therefore pushing the names up or down - meaning the checkbox data no longer corresponds to the right name.
 

Attachments

  • Copy of checkbox layout.xlsm
    282.2 KB · Views: 4
Last edited:
From what I have seen, you want to delete the names of the students who have dropped out of the semester and add the name of those students who are new enrolment, this can be done using a macro, but you should have tables to reconcile the existing student name in your worksheet, the new additions and the deletions needed. How are you going to achieve this, if there is a method to identify this then we can think of a macro.
 
Hi ,

From what I can understand , you should have a master sheet , which could be the one which has the checkboxes.

You should use a separate sheet to receive the data from the CSV file ; once the incoming data is in this tab , you run a macro to consolidate the data from this incoming data tab with the data on the master sheet ; this macro will append the new students' data on the master tab.

At a lesser frequency , you could remove data for students who have left , using a different macro ; what this would do is to transfer data of students who have left , to a history tab , so that data would not be lost , and then delete the entire row of data from the master tab.

Narayan
 
Ok, here is where my conceptualization is failing me. I can't figure out how to consolidate it in the specific way I need. The new data will still come in alpha order.

1)So how do I get excel to recognize what is new?
2)How do I add what is new to the bottom of the list?

Because of the way I have pieced together the checkbox sheet, I wouldn't have a clue how I would delete an entire row of them. I do, however, like your idea of having a macro that can be run to back up the info in a history file. I think I should be able to record this macro using tools. I'll try that and see.
 
Hi ,

To recognize an item is new , we look it up in the MASTER ; if it is present , it is not new.

To add to the bottom of the list is not an issue.

Narayan
 
Ok,

My macro is set up to paste the value of the initial list. Can I compare the two lists with formula or am I going to have to use VBA? I guess I could use a vlookup with and iferror but any way I think of that seems very clunky.
 
Hi ,

Since this is to be done only as and when you receive data from your CSV file , I think a macro would be the easier option ; if you have a macro to do the loading of data into your workbook from the CSV file , this additional processing can be incorporated into the same macro. In fact , if you ask me , all 3 steps should be done within the same macro , so that everything is done in one go.

1. Open the CSV file and read the data into the appropriate worksheet tab

2. Compare the loaded data with the data in the MASTER tab and identify the new data

3. Append the new data to the data in the MASTER tab

4. Transfer the new data to a Log tab so that there will be a permanent record of all CSV file data in the order in which it was opened and read.

5. Clear the data from the tab so that it is ready for the next CSV file to be opened and read from.

Narayan
 
In addition to the discussions above if you can bring in student ID to the excel file life will be much easier. I would assume that it would be easier to identify additions and deletions using student ID than a student name, since student ID will be always unique...
 
Not sure that would be necessary as the updates would be totally class based and the odds that someone has the exact same name are very limited. I have figured out how to identify the differences in lists using match and iferror. Now I need some assistance in copying the new names to the bottom of the list. I am posting this problem in the VBA board.
 
Back
Top