Connecting Tracking Sheet with Case Questionnaire

[alandry1330]

Dear Excel Experts and Support Team,

I am reaching out to the Excel community for technical support on a Case Tracking Sheet I am currently developing. The purpose of this case tracking sheet is to monitor ongoing case development. The tracking sheet is generated from a case questionnaire that a user has inputted information such as start date, received by, assigned officer, company name, and location.

What I have so far:
Tab 1 is the case tracking sheet, designed to be an active status of developed cases submitted under tab 2. The listed columns the tracking sheet has are start date, current status, priority, deadline, company/location, description, and assigned to. The design is simple enough that ideally, someone could very easily add new developing information on active cases. Tab 2 is the Case Questionnaire. The questionnaire has 12 questions ranging from the start date, received by, company name, company location, and summary.

What I am currently stuck on:
I would like it if a user enters new information on Tab 2, Case Questionnaire, and after doing completion, Excel creates a new tab specifically for that newly enter case. This new tab created from new case information would then be added to tab 1, the tracking sheet. My hopes are after a newly generated case has been created the tracking sheet would copy the following information... Start Date, Company/Location, Description, and Assigned case officer.

Any help with guidance is much appreciated. Thank you all for giving me this opportunity. 

[#2]

@alandry1330  What you are asking for would require VBA code.  Not that the code would be that hard, but I would recommend against that design.  You will quickly have an unwieldy number of tabs.  I would recommend the following:

3 Tabs: Input, Summary, Case Info

The Input tab would be something like:

Date, Case, current status, priority, deadline, company/location, description, and assigned to

Then the Summary and Case tabs could be created based on the Input tab

For example the summary tab would filter and sort the Input tab for all the unique cases and then pull the start date, latest status entry etc..

On the Case tab you would have a drop down to select which case you want to view (data validation list based on the summary tab) and then pull all the data from the input tab accordingly.

Just my 2 cents ... 

[#3]

@alandry1330 

I agree fully with the comments from @mtarler suggesting you re-think your design.

Either he or I (or somebody else here on this site) might be better able to offer even more specific suggestions if you could post a copy of your current workbook, just making sure it contains no confidential or private information.

[#4]

@mathetes 

First, thank you for your response. I have attached my current workbook, as suggested. Please note, I do not want to appear as if I want someone to build me a tracking sheet. I am more than willing to follow simple video guides or tutorials. However, based on the responses, I am worried I won't be able to create such a big change. Having multiple tabs is a real concern, but not a major one. The tracking sheet will only have no more than 15 cases active.

Thank you again for the reply and continue support.

[#5]

@mtarler 

Would you be able to share a video or lesson on how to create the VBA code I need? I understand your recommendation, but the concern over the unwieldy number of tabs is not an issue. I will only have at most 15 cases active. 

Thank you for your time on this matter. 

[#6]

@alandry1330 

Too many questions come  to mind.

My guess is that some of these cells designated for entries, either on the occasion of initialization of the case, or as the case proceeds, will in fact contain comments, some extensive text, etc. That is to say, we're not looking at simple numbers, or names, or short entries. And there will be more comments during the life time of a given case.

It brings to mind for me a workbook I have in which I track something else--stock options positions. I have a single sheet for each position, and there I record the history of that position, which can consist of a minimum of four dates plus transaction descriptipons, but can go up to an unlimited number of transactions. Some last for years; others for only months. Typically, like you, I have no more than about fifteen at any one time, and would never have more than, say, twenty.

I've developed a "summary sheet" that shows summary high level data on each of the 15-20 positions, and it automatically updates with any new transaction on the individual sheet. When a position closes altogether, I export the sheet to a "closed" or archive file.

Now, I don't use a macro--I prefer to use the many powerful functions Excel offers to do things--but this does mean that I manually set up those individual detail sheets. I've created that summary sheet such that all I need to do is give it the name of the new sheet and all the desired summary data comes in. But there IS that "manual" component each time a new position (new case) gets added.

If you are unsuccessful in coming up with a macro, I'd be happy to work a bit more with you on how the high level summary sheet could bring in data from the detailed case sheets. What I'd want to have to work with would be some samples of sheets that have been filled in, just with fictitious data.

[#7]

@alandry1330  unfortunately I don't have a good reference for learning VBA/macros.  I'm sure there are many good videos online.

I imagine you would add a button to the template that when clicked would:

1. copy the template to a new sheet

2. name that new sheet

3. remove button from new sheet

4. clear template data

5. call routine to add line to tracking sheet (pass the new sheet)

the add line routine would:

1. find next open line

2. copy corresponding cells 

as for deleting sheets is that going to be a manual operation?

while you're at it you could also add hyperlink to the tracking so you can click on an item there to take you to that item's sheet.

You can also add a hyperlink on the template (and hence all copies of it) back to the Tracking sheet

but then deleting sheets would cause problems with those links

sorry I don't have time to do this right now