Is hyperlink the best answer to this or can it be done with formulas?

[Cambosity100]

Hello to all in this wonderful community.

     I have developed a Kms tracker that "banks" allocated weekly Kms to our NDIS participants. It is about to be rolled out to our Coordinators to input their Participant details. Is there a way that the Coordinators could put the names of the Participant into the KmsData sheet and have it appear in the Directory sheet as a link. Or even vica versa place the name in the directory as a link and have the Participant name appear in the relevant reference in Kmsdata sheet.  The coordinators have a very limited knowledge of excel and I would like to automate as much as I can and then protect the sheets except for data entry points rather than teaching ctrl/k or hyperlink formulas. 

     Open to all suggestions and ideas :)

        With thanks in advance

[#2]

@Cambosity100 

Somebody--you?--has put an awful lot of effort into this already, making it look really pretty. Sadly, that aesthetic emphasis may have gotten in the way of effectively using Excel to its best advantage.

How open are you to radically re-thinking the whole approach?

Excel is really good with taking a well designed table of data (e.g., data on all those kms participants and their coordinators) and extracting that to a dashboard summary by Coordinator, say, or Week, or Location. But a well designed table is just that, a coherent and contiguous set of rows and columns. https://exceljet.net/glossary/excel-table 

What, by the way, is kms? NDIS?  The definitions that come up in Google are interesting but, I assume, not in fact what you're working with....    And what ultimately is the outcome (output) you desire from whatever Excel tracking system you come up with? Can you describe completely but concisely what the tracking system is for?

[#3]

Hi Mathetes. Yes I designed this and have put a lot of time into it. NDIS is the National Disability Insurance Scheme (Australia) supplying services to people with disability to enhance their independence and reintegration into mainstream society. The company I work for is one of many service providers funded by the NDIS. A participant (person with disability) is referred to us by a 3rd party (to reduce conflict of interest) and a contract ( an SA or Service Agreement )is developed in conjunction with this 3rd party between us and the participant for us to provide services. This may include going shopping, attending social events, domestic cleaning, attending medical appointments etc... Here is the crux of the matter. On most SA's the allocated Kilometers (Kms) per week for staff reimbursement of travel are usually grossly inadequate. As things stand at the moment if the SA says 32Kms per week then that is all that is available for the week. If the participant uses 10 Kms that week then next week everything resets and they have 32kms the next week and the unused 22Kms become unclaimable (really untrackable) If a participant needs to travel beyond 32 Kms things get complicated. I have fought this system as ridiculous for over a tear until about 8 weeks ago I got permission from our CEO and the NDIS commission to track and bank Kms so that the participant got a fuller and fairer use of their entire SA. With my system a participant can always know how many Kms they have from week to week. This is great for saving Kms to go to the beach or even a specialist Appointment etc. It gives the Participant more choice and control over their SA which is the supposed to be the lynchpin of the NDIS. Choice and control for the participant... Hope this clears it up a bit... :) Yes I am totally open to ideas, restructuring or starting from scratch if it makes ease of use for people with limited excel capacity... :) Thank you for your interest. This simple spreadsheet may enhance the quality of life for thousands of people with SA's across Australia. Kindest regards Pete

[#4]

@Cambosity100 

OK, Pete. Thanks for that description. I've slapped together a very rough first draft to illustrate how a table might be used. This is patterned on a financial accounting system, where one might track income and expenses (since essentially that's what you wanted to do, just accounting for kilometers allowed and kilometers used).

Add some rows to the database, add realistic numbers. Add columns if you want to track such things as purpose of trip.....

Be aware, this is a first pass. It would be fairly easy to add start and end dates to the reporting section; this is just to show how readily data can be summarized for a variety of people if you start with a simple table.