**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.*