HELP IS VERY MUCH NEEDED!

Question

So I am a social worker and have been trying to find an easier way to organize our client list without having to type it in individually twice. I created a MASTER sheet with a table all of the clients names on it as well as a filter field for specific case managers. In addition to the master sheet I have 5-6 separate sheets designated to each case manager for their clients. Now the issue I am having is updating it. I want to be able to add new clients in the MASTER and after being assigned a case manager they update to the designated sheet. As well as allowing multiple people to update it as needed. Is that even possible? Also another issue I had was when I sent it out for some reason I was the owner even though I wasn't and it wouldn't allow me to give access. Please this would save me loads of time if it is possible to do.

Answer 1

Hi,

You can use the FILTER() function to bring over specific rows from the master sheet.

Answer 2

As well as allowing multiple people to update it as neede

Do you mean co-work?

You can user Excel online co-work or build your own system like https://club.excelhome.net/forum.php?mod=viewthread&tid=1520437&mobile=

Answer 3

Let me show how I have the sheet set up for more insight. I have added A, B, C % case manager as options ( Sorry I am using two screens so it is very wide)

On step #4, after I input the formula in cell A2, a pop up for opening a file shows then when I click cancel this appears:

What am I doing wrong?

Answer 4

Hi Charmaine C!

Sure, it is possible to automate the process of updating client information in Excel and distributing it to the designated sheets for each case manager. Here's a step-by-step guide on how to achieve this:

1. Prepare your Excel workbook as you have stated above:

• Create a sheet named "MASTER" with a table containing all client names and relevant information.
• Create separate sheets for each case manager, designated for their clients.

2.Set up data validation:

• In the "MASTER" sheet, create a dropdown list in the "Case Manager" column. This list should include the names of all case managers.
• Select the range of cells in the "Case Manager" column, go to the "Data" tab, click on "Data Validation," and set up the dropdown list using the "List" option.

3. Use filters to sort the name alphabetically for orderliness:

• In the "MASTER" sheet, use filters to enable sorting and filtering based on case manager names.
• Select the table range, go to the "Data" tab, and click on "Filter." This will add filter arrows to each column header.

4. Auto-populate client data:

• In each case manager's sheet, use formulas or functions to automatically populate client data based on the case manager's name.
• For example, you can use the following formula in cell A2 of a case manager's sheet to pull the client names: =IF('MASTER'!$B$2:$B$100=A$1, 'MASTER'!$A$2:$A$100, "") This formula checks if the case manager's name in cell A1 matches the "Case Manager" column in the "MASTER" sheet and pulls the corresponding client names.

5. Sharing and collaboration with others:

• Save your Excel workbook on a shared drive or cloud storage service like Microsoft OneDrive or Google Drive.
• Share the file with the relevant case managers, granting them editing access.
• Each case manager can open the file, make changes, and the data will update dynamically in the designated sheets.

Regarding the ownership issue you mentioned, make sure to double-check the sharing settings of your Excel file. If you're having issue, you may need to reach out to the IT personnel if there may be an issue with the security settings

Kindly let me know, if you require additional assistance, I will be glad to help further.

Best Regards, Shakiru