Solved

Spreedsheet in Excel

Posted on 2014-11-05
12
271 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
 

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Lately there has been a variety of news related to U.S. employment.  Stories about worker productivity, automobile and airline unions, low employment and foreign laborers have frequented the news.  Each story has good and bad attributes we might arg…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

911 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

20 Experts available now in Live!

Get 1:1 Help Now