[Solved] CLIENT VISIT TRACKER

Isukpong

Isukpong

New member
I am new here and I operate a medical laboratory facility, we also partner with other clinics / hospitals for occupational health related services.

I have designed an excel spreadsheet to make entries of clients visit to the facility.
But I discovered I need to have more flexibility and proper data management system via excel.

The attached file shows my spreadsheet columns and rows design I attempted with few clients from companies and private Individuals that consistently patronize us.

MY CHALLENGE
I desire to have, in addition to the DAILY records, WEEKLY, MONTHLY AND YEARLY numerical totals of clients that visit our facility. And also the financial statistics of services rendered and their Monthly invoice tracking.

Excel Workbook Structure:
  1. Daily Visits: This sheet will record each individual client visit.
  2. Weekly Visits: This sheet will record each individual client visit.
  3. Monthly Summary: This sheet will automatically summarize the daily data on a monthly basis.
  4. Annual Summary: This sheet will provide an overview of the entire year's activity.
  5. Services & Prices: This sheet will hold a list of the services you offer and their corresponding prices.

The reason is that with automated response, my staffs could easily make daily entries, while I review the weekly, monthly and yearly data as I want.

I shall be delighted if I can get help here.


Thank you and best regards.

Isukpong Sunday
 

Attachments

Hello Isukpong Sunday,

Thanks for sharing your file. It's clear you're doing meaningful work, and your Excel design already has a strong foundation. After reviewing your workbook, here’s a tailored response to help you move forward using Excel effectively without jumping straight into expensive software.

Observations from Your File
visit log sheet
  • Contains detailed daily client records with over 40 columns.
  • Great job on capturing both personal and medical data.
  • However, it lacks clean date formatting and needs consistent headers to support automation.
Summary client sheet
  • Set up to track daily, weekly, monthly, and annual totals per company.
  • But currently, it's manually filled and not dynamically linked to the visit log.
Payment and Invoice sheet
  • Properly lists services, charges, and monthly invoices.
  • This is ideal for building your financial summary using formulas.
What You Can Do Next (Without Complex Coding)

1. Create a Data Table
Turn your visit log into an official Excel Table (Ctrl + T). This enables dynamic referencing and supports functions like SUMIFS, COUNTIFS, and Pivot Tables more reliably.

2. Automate the Summary Client Sheet
You can use COUNTIFS to calculate how many clients visited per:
  • Day (filter by date)
  • Week (using WEEKNUM)
  • Month (TEXT(Date, "mmm-yyyy"))
  • Year (YEAR(Date))
Example for Monthly Count per Company:
=COUNTIFS('visit log'!C:C, "FAIRTEX LTD", 'visit log'!D:D, ">=01/01/2025", 'visit log'!D:D, "<=31/01/2025")

3. Build Pivot Tables
  • From the Payment and Invoice sheet:
  • Insert Pivot Tables to summarize revenue by month, by company, or by service.
  • Use this to track monthly invoices automatically.
4. Future Upgrade
Once this is working well, you can:
  • Add slicers for date/company filters.
  • Use Power Query for more automation.
  • You can also explore Microsoft Access or Google Forms + Sheets if your data grows large.
 
shamimarita said:
Hello Isukpong Sunday,

Thanks for sharing your file. It's clear you're doing meaningful work, and your Excel design already has a strong foundation. After reviewing your workbook, here’s a tailored response to help you move forward using Excel effectively without jumping straight into expensive software.

Observations from Your File
visit log sheet
  • Contains detailed daily client records with over 40 columns.
  • Great job on capturing both personal and medical data.
  • However, it lacks clean date formatting and needs consistent headers to support automation.
Summary client sheet
  • Set up to track daily, weekly, monthly, and annual totals per company.
  • But currently, it's manually filled and not dynamically linked to the visit log.
Payment and Invoice sheet
  • Properly lists services, charges, and monthly invoices.
  • This is ideal for building your financial summary using formulas.
What You Can Do Next (Without Complex Coding)

1. Create a Data Table
Turn your visit log into an official Excel Table (Ctrl + T). This enables dynamic referencing and supports functions like SUMIFS, COUNTIFS, and Pivot Tables more reliably.

2. Automate the Summary Client Sheet
You can use COUNTIFS to calculate how many clients visited per:
  • Day (filter by date)
  • Week (using WEEKNUM)
  • Month (TEXT(Date, "mmm-yyyy"))
  • Year (YEAR(Date))
Example for Monthly Count per Company:
=COUNTIFS('visit log'!C:C, "FAIRTEX LTD", 'visit log'!D:D, ">=01/01/2025", 'visit log'!D:D, "<=31/01/2025")

3. Build Pivot Tables
  • From the Payment and Invoice sheet:
  • Insert Pivot Tables to summarize revenue by month, by company, or by service.
  • Use this to track monthly invoices automatically.
4. Future Upgrade
Once this is working well, you can:
  • Add slicers for date/company filters.
  • Use Power Query for more automation.
  • You can also explore Microsoft Access or Google Forms + Sheets if your data grows large.
Click to expand...
Hi Shamimarita,
I really appreciate the head start you have given me.

What does this function refers 'visit log'!D ?
 
You must log in or register to reply here.

Online statistics

Members online
0
Guests online
8
Total visitors
8
Totals may include hidden visitors.

Forum statistics

Threads
403
Messages
1,783
Members
840
Latest member
Minus2003

Share this page

Back
Top