Sorting Duplicate Information on Multiple Spreadsheets

[slindfors]

This is a reading file for tracking information dissemination. It will contain approximately 100-150 names and track the status of each individual to ensure they have read the required information. There is a tab that represents Permanent information along with four tabs for information posted quarterly. I would like a single point of entry for names since the list is relatively fluid with people coming and going. Currently, entering a name on the Permanent tab enters the name on the other four tabs with a simple cell=cell function. This does allow me to enter a name in the first open cell in the associated column on the Permanent tab and then sort that column to keep the names in alphabetical order with the proper results showing up on the rest of the sheets but it does not allow me to sort the information in the columns to the right that track the completion of the required reading on the quarterly tabs. IE, I can hit the SORT button on the top left of the Permanent tab and it will sort everything fine on that tab but the others will not sort correctly because of the aforementioned cell=cell function.

An easy fix is to forget sectioning the information into quarterly reading and put it all on one sheet as the sort works fine that way but there is value to tracking these requirements quarterly. SO, how do I create a simple way to add and remove names to/from the workbook in alphabetical order that will maintain the name/reading status relationship?

A new reading requirement is posted in row 1/2 with a hyperlink to the reading. A person who has not read the requirement will have a blank cell at the intersection of their name and the new post in row 1/2 and there name block will be red. Once they read the post, they just put their initials in that cell and, assuming all other reading is complete, their name turns green and they get a "GO" in the corresponding quarter next to their name. It's an easy way to check to see if the requirement has been done.

Hope I have explained that well. Questions welcome!

Thank you for your assistance.

[dflak]

I did this one since it is an application for a user form and I need practice on those. I took out all the old code and I put in modules to Add, Change and Delete names. I attached this to the sort button.

Name 1 is a drop down list of current names. Name 2 is a text box in which you can enter anything you want.

To add, Name 2 must be filled out. To delete, you must make a selection in the dropdown box. To change a name you need data in both boxes.

[slindfors]

dflak,

Fantastic! That's what I needed.

One issue I found is that the data in columns G and subsequent sort to the bottom of the list when a name is deleted.

If you delete, for example, GoodName 01 with the Manage Names, the 1's (representing his initials after reading the entries) sort to the bottom of the stack instead of disappearing. Is there a solution for that? Maybe I just need highlight and delete after Managing?

Thanks!

[dflak]

Let me look into that. I think I know what might be causing it: I have to clear the contents of the whole row and not just the name. I should be able to make this my lunch break project .

[dflak]

See if this does it for you.

[slindfors]

Close!

It now deletes the formulas in columns A-E which need to remain unchanged.

Any chance you could provide me with the information needed to modify the ranges that the code effects? I hate to keep bothering you with this! I would also like to add a row around row 5 and that will change things as well.

[slindfors]

Okay,

Here is the final draft. Differences...

1. This has the most recent update to the code that you did so the info in columns A-E related to the name being deleted goes away and we need it to stay.
2. It contains an additional row (5) for Applicability. That, I suspect, will change the range of the code.
3. I expanded it to row 160 to get all personnel on the sheet, also changing the range.
4. I was able to figure out some of the basics of the design on the Form you created and have been experimenting with it so the format is a little different but the functionality is unchanged.
5. I would LOVE for the sheets to auto unprotect and protect as necessary when the Modify button is selected to prevent any of the users from messing up the file. Can that be written into the code with a password required to unprotect?
6. As I stated earlier, I don't want to burden you with all these changes but you are echelons above my in your understanding! If you can provide any direction for me to accomplish these things I will give it a shot and stop loading you up!! Your help is greatly appreciated!

[dflak]

This version addresses a couple of things:

I added a row for Applicability to my existing copy - I just added it in cell F5. I didn't do anything with formulas - you can add any and it should not affect the code. The code now accounts for the new line. Yes, that did shift the start of my checks down by a row from 6 to 7 and also the subroutine that did the sort.

I changed the way I delete names. First I change the name to "ZZZZ" - this puts it on the last row of data. Then I go to that row and delete just the data, but leave the formulas in place. This solves two problems: the formulas are not clobbered, and I don't "loose" a row of data with each delete.

It's relatively easy to protect the sheets. I do need to know what you want protected. I assume that you will need to allow the users to do data entry in the cells from Column G on. Is this correct? Or do you want me to lock down the entire sheet so it is essentially "read only?"

[slindfors]

Looks like that did the trick.

In terms of protection, the cells are set up on each sheet so I just need the routine to unprotect the sheets, execute the command and then protect the sheets again. You are correct, rows G and subsequent are not formatted to be locked when the sheets are protected. Everything else is. The routines won't run if the sheets are protected and my guys will screw up the formulas if they're not!

[dflak]

Here is a version that unprotects the sheet, takes action and then re-protects the sheet.

The Password is "Password" and it is set in the code in the UF_Name code module. This is where you would go to change the password. A word of caution. Since the password is in clear text in the code you may want to put password protection on the VBA module so people can't see the code.

Further instructions are in the word document.

Please Login or Register  to view this content.