Help with creating a separate spreadsheet for multiple user's that will update main spreadsheet

[Superfuz]

Hi all,

I apologize in advance for the long post but I've hit the limit of my Excel knowledge and it may be easier to give the whole background before the problem.

I also must state that I am aware that what is detailed below is asking a lot for one post. What I'm really looking for is advice on where to start (i.e different excel terminology for what I am trying to achieve) and from there I can search for guides (or forums) that break down the different aspects of what I need to do.

However if there is someone out there that want's to give me a walk through from start to finish I wont complain.

NOTE: I deal with highly sensitive information so will be unable to attach any pictures of the excel document itself

I have created an excel document for my workplace that requires a large number of people to update with information. Most of the columns have a drop down with the remaining columns to be updated manually with dates and customer names.

There are two categories for updating this sheet:

1). People updating the sheet with new case work (This information takes up the first half of the columns) and is also added to a new row.
2). People updating the sheet with the various outcomes of that specific case (2nd half of the columns) and will only be updating existing rows.

Currently (to prevent any corruption of the data) we only allow one person in the sheet at a time. Among 30 people this can become a huge inconvenience, especially if somebody goes on their dinner without leaving the sheet.

I also have the problem of members of staff finding filling in the many columns a chore and thus not completing the sheet in correctly, which can create issues with the data we gather from the sheet as it contains 1000+ rows.

What I am wanting to create is 2 separate sheets that individual's can complete hit a send button (at the bottom of the sheet) which will update the master sheet in the relevant column and row, eliminating any need for any members of staff to even touch the master sheet.

The first sheet would be for updating new case work. Anything completed on this sheet and sent to the master sheet would start a new row on the sheet.

The second sheet would be more intense. Anything sent from this sheet would update columns on an existing row on the master copy. On top of that I also want to add a 'Get next case' button which will pull data over from the master worksheet, whilst updating the master worksheet that this case has now been taken up (there is currently a 'case status' column that has a drop down list of; logged, open, closed).

I would greatly appreciate any help. As mentioned I am not looking for a walk through but just some guidance on where to start and some terminology to help with internet searches would be great.

Thanks

Superfuz

 

[TonyUK72]

This is probably going to sound like a strange question, how long does a typical user take to carryout the task on the spreadsheet?

 

[Superfuz]

1-2 minutes. Not a great deal of time.

However if your waiting on the sheet and someone is in it, then when you try and get in again someone else is in, it does start to grind on you.

We are also target based, so any lost time (no matter how menial) can be infuriating

 

[Roxxien]

Superfuz, would it be acceptable from your point of view that the macro open the master sheet at the start then close it at the end?

In that kind of scenario, if the master sheet is already open (during the view microsecond of another person macro) we have a message asking to retry in a few seconds?

 

[Superfuz]

Hi Roxxien,

This would not be such an issue.

The individual has filled in the sheet and waiting a minute or so to click send is the lesser of 2 evils.

Currently you could be waiting up to 10 minutes to access the sheet, then having to find the case and run your finger along the screen to make sure you haven't put anything in the wrong line.

Some people are not remotely Excel savey and wouldn't know how to apply filters, it's also not their job requirement to be so.

Aswell as saving time the main reason for this is to keep people out of the main sheet. A lot of data is gained from this sheet which goes up high in the company, I do not want anyone accidently breaking it.

 

[Roxxien]

I'm visualizing your problem and this is what I think would represent what you want.

If you could comment this and maybe imagine 2 or 3 row of false information (About food or car if you want). It will clearly help.

'Macro 1 : Creating a new row
'Open the masterdata workbook
'If problem like can't open, a message box saying you need to retry
'Search the next empty row
'Write the new information at the right place in masterdata
'Update "case status" correctly
'Close the masterdata workbook
'Clear array used to enter new information


'Macro 2 : See an existing row
'Open the masterdata workbook
'If problem like can't open, a message box saying you need to retry
'Search the next row that need to be updated
    'How do you know it?
'Copy all columns of that row
'Save the number of that row under the button
'Update "case status" as currently updating
'Close the masterdata workbook


'Macro 3 : Modify an existing row
'Open the masterdata workbook
'If problem like can't open, a message box saying you need to retry
'With the saved number from macro 2, modify the good row in masterdata
'Update "case status" correctly
'Close the masterdata workbook
'Clear array used to modify information