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