I have created a workbook in Excel 2013 that has a master data sheet and other sheets for groups A - G. I want to be able to pick name of students (first and last) from data sheet in relation to group they belong and if they are active.
I have tried IF, VLOOKUP, INDEX and MATCH functions but can not get any combination to work correctly.
Please help.
I'm not getting a clear picture of how your data is organized and what you need. If you could provide a sample workbook, that would help.
Acts 4:12
Salvation is found in no one else, for there is no other name under heaven given to mankind by which we must be saved.
Hi, Appreciate your offer. I am attaching the file with test data. As you will see the sheet1 is data and other sheets are tables for each group from A - G. From the master table I want to be able to grab First and Last name of students that meet criteria for a group, ie A - G and are 'Active'. The records in the data sheet may add up to 200 so the function formula should be able to check the whole data and pull names for each one that meet above criteria.
It sounds simple but I have not been able to achieve the results as described above. Furthermore could you also advise on what best way I should go about copying the formula in A - G tables without any discrepencies. Thanks for your help.
I don't know of a way with formulas. Probably needs VBA. Are you wanting a one-time load of student data onto the group sheets, or will it be on a recurring basis. One-time would be easy. Recurring would need to append new data to existing group data so as not to wipe out any Monthly Receipts info. Below is the code for a one-time load:
Please Login or Register to view this content.
Hi, natefarm:
You are correct the Group tables must update automatically when a new student is added or existing student become inactive. As all student info will be added to the S_Data sheet only the Group tables should not be affected in anyway by above activity. If an existing student becomes inactive mid year then his info should not change or delete in the Group table.
Please also advise me how to incorporate the VBA code in the workbook without damaging it by mistake.
i thank you for your time and help.
I think we have a different interpretation of what I meant by "one time load". I meant that you already have an S_Data sheet full of student records, and then run the above code one time to load the group sheets. That might still work as a starting point, but it sounds like you need another way of getting subsequent new S_Data additions added to the respective group. Correct?
If so, start by copying the above code to a new code module in your workbook and saving the workbook as a .xlsm. Then run the code. You'll always have your original .xlsx to go back to if needed. See how you get along and if that works as expected, and then we'll go from there.
Hi, natefarm:
I have tested the code you created and it worked fine for new and first time load. However, for day to day use of this workbook I still need your help with the following capabilities:
1. When an active student becomes inactive as will be indicated in column F of S-Data sheet by "YES", and the column E of S_Data sheet is either "YES" or blank then the respective Group sheet should show the name of that student in Red. But this change should not in anyway change the year to date receipt record of that student. The receipt record should only be changed by the keeper of the workbook.
2. If an inactive student become active before December 31st within an fiscal year then that student's name should be changed back from Red to Black, still without any change to receipts recrod of tha student.
I hope you can still help me with this project. Thank you so much for your help.
Below is the code that will hopefully be what you want. To determine the fiscal year, on S_Data in A3 put "Fiscal Year:", and in B3 put 2013. Then in the VBA window, Project Explorer, double-click S_Data and paste the following code there:
Please Login or Register to view this content.
Hi, natefarm:
According to your instructions I loaded the new code and tested it. For most part the code worked but when I tested for a student to go inactive from active (his name in black) in the group list the name changed color to red. Howeveer, it would not turn black from red when changed from inactive to active status. Infact I even deleted the group code to blank, active to blank and even inactive to blank. I know this might be a minor code issue but I wanted to let you know so you can propose correction. Thanks for your continued help.
Based on step 2 of your instructions above, it should only turn black if he becomes inactive before 12/31 in the current fiscal year. Assuming you have 2013 as the FY, the current month is January of 2014, so it would not turn black. If you want to test it by changing the FY to 2014 and then changing to Active, it will turn black. If I misread the instructions, feel free to tinker with the code to get it to work as needed.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks