Question description:
This user has given permission to use the problem statement for this
blog.
What formula do I use to create a running balance across several worksheets as the numbers continue to change? For example. I have created a drop down list for every month of the year from Jan - Dec for people to sign up for sessions. As they sign up for sessions, I want to create a list beside their names to show how many sessions they have signed up for. So if they sign up for 6 the summary should automatically display 6, if they sign up for 1 more it would instantly show 7 and so on for each person? Make sense
Solved by C. F. in 59 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
27/07/2018 - 01:45
Hello, I hope you are having a good day. I am here to help with your question.
User
27/07/2018 - 01:45
Hi There, did you understand my question, if so please repeat?
User
27/07/2018 - 01:46
Hello
Excelchat Expert
27/07/2018 - 01:46
so, you want people to to sign in for sessions every month
Excelchat Expert
27/07/2018 - 01:47
you will provide a drop down for them to sign up
Excelchat Expert
27/07/2018 - 01:47
ou want to check how many course they sign up in the eyar
User
27/07/2018 - 01:47
No,
User
27/07/2018 - 01:48
I have created a drop down list for a number of people, so they can sign up for a number of training sessions each month for a period of 14 months.
User
27/07/2018 - 01:48
I want to track automatically how many session each person is signing up for during these 14 months
User
27/07/2018 - 01:49
So if Susie signs up for 1 session in oct, then dec then jan ect. I want a running live total each time on the summary page?
User
27/07/2018 - 01:49
Does that make sense?
Excelchat Expert
27/07/2018 - 01:49
ok just to confirlm , the nqles of people and all the sessions they sign up are in one sheet
User
27/07/2018 - 01:49
I have over a 100 people months run from Sept 2018 through to Dec 2019
User
27/07/2018 - 01:50
Yes all on one work sheet
Excelchat Expert
27/07/2018 - 01:50
do you have a rough layout as to how it looks like?
Excelchat Expert
27/07/2018 - 01:51
it is important to design the formula
Excelchat Expert
27/07/2018 - 01:51
also once one session per month?
User
27/07/2018 - 01:52
No not yet. there are several sesssions each month
Excelchat Expert
27/07/2018 - 01:54
or would it help to build the metrics with sessions and allocating month for them?
User
27/07/2018 - 01:54
Do you not understand what I need.
Excelchat Expert
27/07/2018 - 01:54
yes I do
Excelchat Expert
27/07/2018 - 01:54
Just trying to visualise how the allocation table will look like
User
27/07/2018 - 01:55
Ok so whatever works so I can see a running automatic total of how many sessions they are signing up each month over the 14 month period.
Excelchat Expert
27/07/2018 - 01:55
something like the table I am building?
User
27/07/2018 - 01:55
no
User
27/07/2018 - 01:55
no
User
27/07/2018 - 01:56
A drop down list for each day of the month
Excelchat Expert
27/07/2018 - 01:56
Ok
User
27/07/2018 - 01:57
example a drop down list for all the days in January with the people to sign up for training, then feb and so on. Each sheet is called a month inside the sheet is a calendar with a drop down list for each day of the month of peoples name to choose from/
Excelchat Expert
27/07/2018 - 01:58
so every sheet is a month
User
27/07/2018 - 01:58
yes
Excelchat Expert
27/07/2018 - 01:58
and then there is a summary
Excelchat Expert
27/07/2018 - 01:58
I got it
User
27/07/2018 - 01:58
thx
User
27/07/2018 - 01:59
yes summary is call # of session signed up for
User
27/07/2018 - 01:59
want it to be live and automatic
Excelchat Expert
27/07/2018 - 01:59
got it
Excelchat Expert
27/07/2018 - 01:59
let me think of best way of designing formulas for this
User
27/07/2018 - 01:59
thank you , my friend
Excelchat Expert
27/07/2018 - 02:02
May I request for an extension of chat I'll come to you with the solution as soon as possible
Excelchat Expert
27/07/2018 - 02:02
thank you
User
27/07/2018 - 02:02
yes
Excelchat Expert
27/07/2018 - 02:03
also one more clarifying question
Excelchat Expert
27/07/2018 - 02:03
do you want the solution for google sheets or offline excel?
User
27/07/2018 - 02:03
what is best
Excelchat Expert
27/07/2018 - 02:03
possible on both
User
27/07/2018 - 02:03
what is offline excel
Excelchat Expert
27/07/2018 - 02:04
excel on your computer
Excelchat Expert
27/07/2018 - 02:04
or google spreadsheets
User
27/07/2018 - 02:04
excel
Excelchat Expert
27/07/2018 - 02:04
perfect
User
27/07/2018 - 02:05
mayb both incase one has problems?
Excelchat Expert
27/07/2018 - 02:05
ok so let me prepare a file and send it to you
User
27/07/2018 - 02:05
Thank you
Excelchat Expert
27/07/2018 - 02:05
and then I will walk you thorugh my solution?
User
27/07/2018 - 02:06
ok
User
27/07/2018 - 02:06
Let me know when you sent it
Excelchat Expert
27/07/2018 - 02:07
i will send it here
Excelchat Expert
27/07/2018 - 02:07
preparing as fast as posssible
User
27/07/2018 - 02:07
Do you have my email
Excelchat Expert
27/07/2018 - 02:08
sorry, we are not authorised to do that
Excelchat Expert
27/07/2018 - 02:08
I will send it on this chat itself
User
27/07/2018 - 02:08
ok
User
27/07/2018 - 02:14
Are you still there
Excelchat Expert
27/07/2018 - 02:15
yes
Excelchat Expert
27/07/2018 - 02:15
working on your file formay
Excelchat Expert
27/07/2018 - 02:15
format
Excelchat Expert
27/07/2018 - 02:19
Almost done
User
27/07/2018 - 02:19
k
Excelchat Expert
27/07/2018 - 02:22
here you are
[Uploaded an Excel file]
Excelchat Expert
27/07/2018 - 02:23
so as a quick example what I did is built the sheet for 3 months
Excelchat Expert
27/07/2018 - 02:23
I created name ranges using formulas which will be dynamic
Excelchat Expert
27/07/2018 - 02:23
please let me know when you have it open I will walk you through it
User
27/07/2018 - 02:23
its is open
Excelchat Expert
27/07/2018 - 02:23
ok
Excelchat Expert
27/07/2018 - 02:24
I know that you will be providing a dropdown for every day of the month
Excelchat Expert
27/07/2018 - 02:24
but in the interest of time I have left them blank
Excelchat Expert
27/07/2018 - 02:24
so I have created a dynamic name range called Names
User
27/07/2018 - 02:24
It is NOT WORKING RIGHT
Excelchat Expert
27/07/2018 - 02:24
if you go to formulas and name manager you will see how i have created
Excelchat Expert
27/07/2018 - 02:25
lets give susie 2 sessions one in oct and one in Nov
User
27/07/2018 - 02:26
It isn't working
User
27/07/2018 - 02:26
error.. the value doesnt match the validaton restrictions defined for this cell
Excelchat Expert
27/07/2018 - 02:26
let me check that
User
27/07/2018 - 02:29
The other sessions I can put numbers in but there is nothing in the summary
Excelchat Expert
27/07/2018 - 02:29
I am retesting it
Excelchat Expert
27/07/2018 - 02:35
here you are
[Uploaded an Excel file]
Excelchat Expert
27/07/2018 - 02:35
I made it even more simpler
Excelchat Expert
27/07/2018 - 02:36
no name ranges this time
User
27/07/2018 - 02:36
ok lets try it out
Excelchat Expert
27/07/2018 - 02:36
sure
Excelchat Expert
27/07/2018 - 02:37
in the interest of time, i will leave explanation until you open as this chat will be avaialble for your refernce
Excelchat Expert
27/07/2018 - 02:37
i used offset to include the ranges that changes according to the names
Excelchat Expert
27/07/2018 - 02:37
I can make it even more dynamic
User
27/07/2018 - 02:37
ok sure
User
27/07/2018 - 02:38
I signed betty up for 3 in Oct but not showing up in Summary
User
27/07/2018 - 02:41
Looks like you made it so you can only sign up for one session per month?
Excelchat Expert
27/07/2018 - 02:41
no it should count all the sessions per month
User
27/07/2018 - 02:42
No its not working. Signed up betty for one in Oct and nothing showing in summary
User
27/07/2018 - 02:43
Why isn't the summary working?
Excelchat Expert
27/07/2018 - 02:44
is your formula calculation auto?
Excelchat Expert
27/07/2018 - 02:45
this?
[Uploaded an Excel file]
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
