Excel - IF Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.