Solved

Spreedsheet in Excel

Posted on 2014-11-05
12
259 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
  • 6
  • 5
12 Comments
 
LVL 24

Expert Comment

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

Expert Comment

by:DanCh99
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:DanCh99
ID: 40424453
note, dates have to be entered in a proper format that Excel recognises - ie dd/mm (for the UK)
0
 

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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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:DanCh99
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:DanCh99
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:DanCh99
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:
DanCh99 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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

With the internet and the ease of information transference, many professional jobs can be done anywhere today.  Why should it make a difference whether an x-ray is read in India or the United States as long as the radiologist is qualified?   Outso…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
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…

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now