Solved

Spreedsheet in Excel

Posted on 2014-11-05
12
308 Views
Last Modified: 2014-11-06
Hello,

I need some help creating a spreadsheet in Excel that give me automatically the total sum as soon as I add money to it.

I would like to create 4 different columns.

1) Days of the week
2) AREA 3
3) VIC
4) PCs

I would need this spread sheet created per week first then per month. On topo there should be a row to type the month of the year.

I am attaching a drawing of what I am trying to accomplish.

Thank you everyone.
0
Comment
Question by:Italiabella
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40424099
No drawing attached.
0
 
LVL 23

Expert Comment

by:Danny Child
ID: 40424446
Cobbled something together.  

It only does by Week now, but could be expanded to do months - would need to probably use the EOMONTH formula there though.  

Is this close to what you want?
M--ee---SUMIFS.xlsx
0
 
LVL 23

Expert Comment

by:Danny Child
ID: 40424453
note, dates have to be entered in a proper format that Excel recognises - ie dd/mm (for the UK)
0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

Author Comment

by:Italiabella
ID: 40424685
I am sorry, here it is ...
IMG-3343.JPG
0
 

Author Comment

by:Italiabella
ID: 40424700
Very Very well Dan, you understood perfectly my broken English.

Can we add a 4th colums please, I don't have a name for it yet. AND the Month on the year on top of it like it shows in my dwawing...

In one spreadsheet I would like to have the 5 weeks. Therefore, one month.

thanks,
0
 

Author Comment

by:Italiabella
ID: 40424986
Phillip can you see it now ?
0
 

Author Comment

by:Italiabella
ID: 40425520
Hello Everyone,

I have been doing some work on the spreadsheet that Dan provided today. I organized by week.

When I showed to a friend he told me that the look is very poor and basic. I said that it works and it will help me to monitor each week how I do.

Does anyone can improving the look or the organization maintaining the meat of the spreadsheet. I mean, I need to have still the 5 weeks, the 3 income coming form Area 3, VEC and PC and the total of each week and the total of the month.

I am attaching what I did so far..

thank you everyone
November--2014.xlsx
0
 
LVL 23

Expert Comment

by:Danny Child
ID: 40425825
Will try and look at this a bit later... Is your Week 1 the first Mon-Fri of the month?  

And, would you then over-run into the next month eg:

Mon 29/12
Tue 30/12
Wed 31/12
Thu 1/1
Fri 2/1
0
 
LVL 23

Expert Comment

by:Danny Child
ID: 40425881
updated, but there's not much cleverness happening here any more, except that the dates roll forward when you enter the Month date.

Haven't thought of a nice way to have it STOP putting dates in when there's only a 4-week month, but there you go... typical month would be July '14
M--ee---calendar.xlsx
0
 
LVL 23

Expert Comment

by:Danny Child
ID: 40425883
Cell B7 does the calc for the first Sunday of the Month.
0
 

Author Comment

by:Italiabella
ID: 40426446
Hello Dan,

Thank you for adding the 4th colum, it will be for "Extra" revenues.
I work 7 days a week. The first week as a mistake as I forgot to start the week with Saturday November 1st, instead I started the week from Sunday the 2nd.

Yes,  I would love to have a spreadsheet setup so that every month just entering the month of the year it automatically generate the dates from the 1 to the moth to the 30 of the month or the 31 of the month depending on which month we are talking about.

Now I think I understand when you say type the << Enter the date here, in full format, for the first day of the month that you want.            But it doesn't work. I tried to type December 2014 and nothing change. :) please don't laugh I am not familiar with Excel.. Can you show me what I have to type for the month of December in order to come out just fine please
What you did is great !

thank you so much.
0
 
LVL 23

Accepted Solution

by:
Danny Child earned 500 total points
ID: 40427396
just enter 1/12/14, and it should go from there, for 1st December.  Even just 1/12 should work.

If you click on the "November" cell, and then look at the address bar, you should see the format there as well (it may be different for your location, as different countries show the dates in different ways).  All I've done here is to have a normal date, but to change the format of it to mmmm yy - ie Full name of month, then 2 digit year.

To change to a Saturday start, change the formula in B7 to be
=DATE(YEAR(B2),MONTH(B2),1+7*1)-WEEKDAY(DATE(YEAR(B2),MONTH(B2),8-0))

The final 0 changes the start date.
0=Sat, 1=Sun, etc.

Here's more info on how Excel works with dates:
http://www.cpearson.com/excel/datetime.htm
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question