Forum Discussion
Connecting Tracking Sheet with Case Questionnaire
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 ...
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.
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