Results 1 to 5 of 5

Thread: Need help with

  1. #1

    Need help with



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am a psychologist and I built an excel workbook, a template, to store all of the information I have about each client I see. I called the template "new account". Whenever I start to see a new client, I open this "new account" template, fill in all of the information about a client (i.e. contact info, demographics, etc.). This template is also where I keep my aging account history for each client. After I create one of these account workbooks for a client, I then save it in the client's name and case number, and this workbook then serves as a "record" and the information in it gets automatically saved to an "account database" that I created to track all of my clients (the information in each account workbook is stored all in one row of the "account database" and gets updated every time I save each individual "account workbook"). Each individual "account workbook" consists of an "account" page all in one sheet (this sheet tracks demographics, contact info, certain codes I use to track my practice outcomes, and service/payment history). The next tab or sheet is an "invoice" template which I use to create each invoice for each session to give to the client as receipt of payment. Each time I see a client I "move a copy" of this invoice template to the next position (essentially creating a new tab each time), assign a new invoice number to the invoice using a macro I wrote, fill in all the information for that service manually, and then manually rename the tab to correspond to the new invoice number, then print the invoice and give it to the client. I then also update their information on their "Account" page. My question is this: what I would like to be able to do is to create a list somewhere on my "account page" of the names of all of the active sheets or tabs currently contained in the workbook, with the understanding that the workbook is constantly expanding in number of sheets each time I see a particular client. Lets say I see a client for five sessions and the client pays after each session, then all of the active sheets would include the "account page" plus the generic invoice template, plus five invoices each named for its invoice number. I would like to be able to have on my account page a place where all of the active sheets are listed, and if possible the ability to click on the name of each active sheet on that initial "Account page" (which would be the invoice number) and go directly to that corresponding invoice without having to scroll over to that invoice using the controls on the left side (using the controls on the left is fine if there are only five or six active sheets but some clients that I have been seeing for long term therapy can have 50+ sheets, and growing). And I'd like to be able to create this list automatically. I'm not sure if Excel can do it, but based on the searches I run in google, it seems like there might be a possibility it can, but I really am in need of someone to help me with this one as it seems like my situation doesn't exactly fit most of the circumstances I am finding in my google searches on this topic.

  2. #2
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    I can't see an actual question.

    From what I read then your system could be greatly improved but I think it's too big a job for a free forum.
    Hope that helps

    Roy

  3. #3
    Sorry, I got a bit longwinded just trying to provide the context for the question. My question was there, but easily overlooked I guess. My question is: can I create a dynamic list of the active tab names in a workbook. The list would appear on a particular sheet in the workbook (lets just say it can be any sheet). The list needs to clickable. By dynamic I mean that the list will constantly grow as new sheets are added to the account. I did learn how to create a macro that puts a customized button on the toolbar, that shows all the active sheets but it appears that it only applies to a file whose name never changes. I tried applying it to an excel template, which works fine with the template, but of course as soon as you change the name of the file to what it will be permanently (because I'm creating hundreds of these files) it no longer works with the new file name.

  4. #4
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,336
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Have a look at this page (paragraph starting at " worksheet index") .The macro refreshes the index each time the INDEX sheet is opened

  5. #5
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    My point was that there are better ways to do this that wouldn't require having multiple sheets & the index
    Hope that helps

    Roy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •