Large Range Autosum

%3CLINGO-SUB%20id%3D%22lingo-sub-2254460%22%20slang%3D%22en-US%22%3ELarge%20Range%20Autosum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2254460%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3EHopefully%20this%20makes%20sense%20to%20you%2C%20as%20I'm%20trying%20to%20explain%20this%20from%20a%20novice%20view.%20I'm%20currently%20trying%20to%20build%20a%20very%20basic%20timesheet%20submission%20system%20for%20my%20work%20as%20everything%20is%20very%20manual%20and%20I%20want%20to%20try%20and%20automate%20it%20a%20bit.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20the%20workbook%20I%20have%2C%20I%20have%20two%20worksheets%3A%20one%20that%20pulls%20in%20data%20from%20the%20form%20submissions%20and%20one%20that%20does%20all%20of%20the%20attendance%20calculations.%20I%20wanted%20to%20keep%20them%20separate%20just%20for%20simplicity.%20I%20have%20broken%20down%20the%20attendance%20sheet%20into%20the%20following%3A%3C%2FP%3E%3CP%3E-%20Name%3C%2FP%3E%3CP%3E-%20College%3C%2FP%3E%3CP%3E-%20Hours%20submitted%20to%20date%3C%2FP%3E%3CP%3E-%20Expected%20hours%3C%2FP%3E%3CP%3E-%20Attendance%20%25%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20basically%20want%20to%20do%20is%20every%20time%20a%20student%20submits%20their%20hours%2C%20I%20want%20to%20auto-calculate%20the%20total%20hours%20to%20date%2C%20the%20total%20expected%20hours%2C%20and%20also%20the%20total%20attendance%20so%20far%20by%20taking%20the%20data%20from%20the%20submission%20worksheet%20and%20putting%20it%20into%20the%20calculation%20worksheet.%20Obviously%2C%20I%20put%20the%20formula%20in%20manually%2C%20but%20I%20have%20to%20do%20this%20over%20200%20times%2C%20so%20I'm%20trying%20to%20find%20a%20way%20around%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20anyone%20has%20any%20ideas%20on%20how%20to%20do%20this%2C%20please%20do%20let%20me%20know.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%2C%3C%2FP%3E%3CP%3ER%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2254460%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2254477%22%20slang%3D%22en-US%22%3ERe%3A%20Large%20Range%20Autosum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2254477%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1017414%22%20target%3D%22_blank%22%3E%40ryancshar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20I%20am%20not%20the%20most%20suitable%20to%20give%20you%20a%20solution%20to%20this%20problem%2C%20but%20with%20your%20permission%2C%20if%20I%20can%20recommend%20you%2C%20add%20a%20MS%20Excel%20file%20(without%20sensitive%20data)%20to%20your%20project.%3C%2FP%3E%3CP%3EExplain%20your%20plans%20in%20relation%20to%20this%20file.%20So%20you%20can%20get%20a%20solution%20that%20is%20tailored%20to%20your%20needs%20much%20faster.%3C%2FP%3E%3CP%3EAt%20the%20same%20time%2C%20it%20is%20much%20easier%20for%20someone%20who%20wants%20to%20help%20to%20understand%20the%20subject.%3C%2FP%3E%3CP%3EA%20win-win%20situation%20for%20everyone.%3C%2FP%3E%3CP%3EPlease%20no%20picture%2C%20even%20if%20it%20is%20said%20that%20a%20picture%20can%20say%20a%20thousand%20words%2C%20it%20is%20certainly%20not%20in%20the%20case%20of%20Excel%2C%20on%20the%20contrary%20in%20some%20cases.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20Knowing%20the%20Excel%20version%20and%20operating%20system%20would%20also%20be%20an%20advantage.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi all,

Hopefully this makes sense to you, as I'm trying to explain this from a novice view. I'm currently trying to build a very basic timesheet submission system for my work as everything is very manual and I want to try and automate it a bit.

 

On the workbook I have, I have two worksheets: one that pulls in data from the form submissions and one that does all of the attendance calculations. I wanted to keep them separate just for simplicity. I have broken down the attendance sheet into the following:

- Name

- College

- Hours submitted to date

- Expected hours

- Attendance %

 

What I basically want to do is every time a student submits their hours, I want to auto-calculate the total hours to date, the total expected hours, and also the total attendance so far by taking the data from the submission worksheet and putting it into the calculation worksheet. Obviously, I put the formula in manually, but I have to do this over 200 times, so I'm trying to find a way around this.

 

If anyone has any ideas on how to do this, please do let me know. 

 

Many thanks,

R  

 

4 Replies

@ryancshar 

Maybe I am not the most suitable to give you a solution to this problem, but with your permission, if I can recommend you, add a MS Excel file (without sensitive data) to your project.

Explain your plans in relation to this file. So you can get a solution that is tailored to your needs much faster.

At the same time, it is much easier for someone who wants to help to understand the subject.

A win-win situation for everyone.

Please no picture, even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases.

 

* Knowing the Excel version and operating system would also be an advantage.

 

 

Thank you for your understanding and patience

 

Nikolino

I know I don't know anything (Socrates)

Hi,
Of course! I'll upload an example file, but obviously I cannot upload the file with all of my student's names in, as you can understand.

In response to your questions:
My plans - So, I'm trying to build a more functional attendance system, whereby students would submit their completed hours through a Microsoft form and then the excel sheet would auto-calculate the hours that they have completed, their expected hours to date and their attendance. This would then have to be inputted in to the cell next to their name. Whilst I understand I would use the sumif formula, I have more than 100 students, something which is more encompassing is something that I'm looking at more.

Due to the fact that my students would get academic credit for the hours that they have completed, we need to make sure that the sums are as accurate as possible. My further plans are to make a more visual system through VBA, but that's a while off yet!

My Excel version is Version 2102 (Office 365 for Business) and I'm running on Windows 10, so pretty up to date stuff.

I'll attach the example file below. Granted it doesn't have all the details, but it should hopefully be enough to show what I'm working with!

Thanks,
R

@ryancshar 

Could you please clarify your sample

image.png

- how week starts on Apr 26 and ends on Apr 14 and why there are 12 days in the week?

- Hours expected for today - if we are in the middle of the week, e.g on Wed, shall we take all hours expected for the week or take them till Wed? If the latest, what is the logic is used to calculate?

- if student reported few weeks and we are in the middle of the week, shall we take hours reported for the current week or we calculate only finished weeks?