Solved

Spreedsheet in Excel

Posted on 2014-11-05
12
285 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: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
Flexible connectivity for any environment

The KE6900 series can extend and deploy computers with high definition displays across multiple stations in a variety of applications that suit any environment. Expand computer use to stations across multiple rooms with dynamic access.

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will shed light on the latest trends when it comes to your resume building needs. For far too long, the traditional CV format has monopolized the recruitment market.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

828 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