Oct 12 2020 08:24 AM
Hi
during covid19, as a yoga coach I'm having different online classes
and the tuitions are different for this classes; also there's not a specific start time for all the ladies (it's different from one to another)
I need to have a smart functional excel so that I can enter some basic data, and it tells me when ever someone must pay again
every class has 12 session in a month (30,31 days) and it would be great if I can understand the payment status of every single participant at the 10th session (the name become red when some one used class for 10 session)
if you know how it comes, please help me cause I'm not familiar with the formulas like a pro
I just know very simple basic things
thank you so much in advance <3
Oct 14 2020 08:02 AM
@Silver_Mary This is REALLY hard without having an example sheet from you (please no personal info) to know what format you are using and what data you are collecting and such. That said, I would like to say a couple things:
You can use conditional formatting to highlight cells/lines that meet certain conditions
Instead of highlighting based on 10th visit you might want track and highlight based on amount owed. I say this because at some point you may need to have someone on a payment plan or maybe would prefer to pay as they go. So I would track how much they owe and then use conditional formatting to highlight if it is >=10 x cost per session
For a good flexible system you may want
1 sheet purely be session records (i.e. date, client ID, duration or units used so you could track double sessions and such)
1 sheet be payments (i.e. date, client ID, payments)
Then you can have 1 or more reporting sheets that tabulate the data. You can have 1 showing how much each client owes, you could have others to do all kinds of things like create individual summaries for clients to show how many sessions they attend per month, how much business you are doing, etc..
I don't know if that is helpful.
Oct 15 2020 08:50 AM
Hi Dear @mtarler
Here I tried to give you all the specific data, so that it be easier for you to help me. I hope that I can show you my need clearly. And please notice that these are absolute raw data and explanation and I didn’t know the best way for making different tables and maintenance. To be really honest : I don't know anything at all...
I’m really grateful in advance because of your kind help and your time and knowledge
These are our data :
Now, What I NEED :
I Need 2 Things
Important : everyone start the class in a different day and they pay tuition before they start the class, It’s not like they come and use the class and pay at the end of month for the sessions they've been used
Thank you so much, you have no idea that how much I'm in need of good formulas and different tables for maintaining y data in a clean way...
I'll be happy ever after you respond me :))
Wish you the bests in the world
Oct 16 2020 06:55 AM - edited Oct 16 2020 07:20 AM
@Silver_Mary Hi. So I started playing with that file last night and noticed a few things and had a few questions:
You are locking member to class to coach and only show/track the last payment. Here are some concerns about that:
So in the ideal case you would have multiple tables to track class - teacher, class - attendance, member - payment, teacher - info, member - info, member - class registration, etc...
But this is excel and not a relational database so I was thinking how to keep it most simple but keep needed functionality and flexibility. My thoughts below would keep it close to the format you have and are comfortable with but add some flexibility. That does mean it lacks additional flexibility/function but I am also not looking to recreate a relational accounting database in excel for your company (I don't have that much volunteer time and besides then you would have something much more complicated that YOU would have to maintain). So for example, if Mary takes both Swing and Yoga in my suggestion below she would be treated like 2 separate people, a Mary in Swing and a Mary in Yoga (so her $590 payment would need to be split and entered separately). Also, there is some inefficiencies like having to repeat information that normally in a relational database you would set once in a table that has a 1 to many relationship with another table. That said, my thought is to keep your main table but:
Then: