Auto Format Spreadsheet With Various Rows Month To Month

Feb 4, 2009

I have a database that I export to excel every month. The export process is built in the database software (ACT!2009). The export opens Excel with the standard Book1.xls file name. All the field columns will be the same every month.

Goal:
I need to format the spreadsheet to make it more readable and have been assigned the task of:
1 - Inserting a blank row between each row that contains data and filling in with color.
2 - Resizing the blank row to make it look like a "thick" border.
3 - Auto adjusting the columns to correct size.
4 - The last column contains comments and needs to be wrapped text.
5 - All of this needs to fit on 1 sheet (landscape).

Issues:
1 - Each month there will be a different number of rows.
2 - I know I can create a macro to do this but the macro that I would be creating will be in a saved template or spreadsheet. How could I use a that recorded macro in a spreadsheet that is called Book1.xls?

I have attached 2 spreadsheets. One called Book1.xls which is the raw data after exported and the 2nd spreadsheet called Formatted which is the end result that I am looking for.

View 2 Replies


ADVERTISEMENT

Dates - Show Month Only, And Actually Be The Month Only (not Just Format The Date)

Jul 28, 2008

I have a range of dates from 2003 to 2012. I formatted them to the 'Mar-01' option, but when I want to pivot on the month, Excel still reads them as the date - example 3/25/2008, 3/28/2008...and so my pivot table has multiple columns for all of the dates present in that month.

How do I truly format my dates so that excel reads them as the month only so that I can then pivot and show 12 columns (months) per year?

View 9 Replies View Related

Financial Model (formula To Equally Distribute Revenue Either Over The Next 1 Month, 2 Month Or 3 Month Period Depending On Size Of The Deal)

Dec 23, 2008

I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.

Details:
Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.

- if under $25K, recognize in next month (month N+ 1)
- $25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over $100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...

View 4 Replies View Related

Last Ocurance Of The Last Date Used For Each Month And Then Use The Cell Number To Calculate The Column Totals For That Month

Jan 28, 2010

I have a spreadsheet that is now a yeare old with 5000 rows and is now going into the 2nd year

Column A is for date input and the same date can be repeated several tumes :-

1 Jan 09
1 Jan 09
1 Jan 09
1 Jan 09
2 Jan 09
2 Jan 09
3 Jan 09
3 Jan 09
3 Jan 09

Sometimes there are all 30 /31 days but normally not .

I need to find the last ocurance of the last date used for each month and then use the cell number to calculate the column totals for that month.

View 9 Replies View Related

Create Year To Date Sales Comparing 4 Years Month By Month?

Dec 25, 2013

Need to create year to date sales comparing 4 years month by month. Stacked chart (Excel 2010) works OK for the first three months but adding the fourth month changes the chart to 4 series with a monthly axis. To put it another way I need a vertical axis of years and a horizontal axis of $$$ with each months sales of each year stacked on its year.

View 10 Replies View Related

Insert All Dates For A Month Weekly When User Inputs Month And Year?

Jul 10, 2014

I have created a time sheet in excel (see attached) that will be part of the larger workbook that will be linked with other sheets to auto fill in most fields. I am wondering if there is a way for an user to enter a Month and a Year at the top of the page and that in turn automatically fills in the days of the month by week.

So in attached sheet there are 5 boxes representing 5 weeks in a month. So if we used May 2014 as an example I would like to know if there is a way that once May 2014 is entered in up to top that. Excel fills in the dates in Week #1 with under Thursday showing 1st, under Friday showing 2nd as on for the entire month...

So as the month go by all user has to do is state the month and year and excel fills in the weekly dates for each day in month.

Attached File : Time and Attendance.xlsx‚Äé

View 2 Replies View Related

Auto-calculate Last Day For A Month

Nov 28, 2006

I'm working on a Calendar. One where all the user does is input the year, and the rest of the Calendar fills itself out, as to the days.

Leap year is causing a small problem. There may be an easier way to do this (actually, I'm sure there is, but anyway), is there a way for a cell to automatically figure the last day of the month?

IE: I put "2007" in a field. Another cell auto matically reads as "28" (last day of Feb for this year). Subsequently, when I enter "2008", the same field reads "29".

The rest I think I got ok, but everytime I get a leap year, it shoves all my formulas down a cell, thanks to the extra day, and they're all off by one (February calendar showing last day as "01" from March, and "29" as the first day for March, from Feb).

View 9 Replies View Related

Lock Spreadsheet After A Month From Creation?

Feb 12, 2014

For each new case they have a spread sheet that they edit - I would like for this spreadsheet to lock a month after it is first edited/updated.

View 2 Replies View Related

Using Month Function To Obtain Month And Copy It To Next Empty Column

Apr 4, 2013

I am working in a spreadsheet that contains a bunch of data, it is not limited, and varies. It has some fiels such as: name, date of birth, address, and others. I am interested in creating a column with only the month digit of the date of birth for each row.

I have been working with some code, I am not quite sure how to continue. The following table would be a example that I have of the data, it has only to entry in the column of Date of Birth, but my data range will always vary. I want to get the month and past it in the next cell that is available in this case would be column c or 3... The worksheet name is REP.

VB:

Dim Cell As Range
' 1st cell with the posting date
Set Cell = Range("A2")

Do While Not IsEmpty(Cell)
If Cell = "Date" Then

[Code] .....

View 5 Replies View Related

Formula To Distinguish Month Year From Prior Month Years

Feb 13, 2010

This is for a report and on "Summary Worksheet" I want to post "Current Payment" totals IF the invoices from "Tab 3" equal the "month" in G6. Say the report is for January - if there are invoices on Tab 3 -worksheet with a January date I want to post all invoice amounts on Summary worksheet under current payment.

View 4 Replies View Related

Automatically Get All The Dates Of A Month Entering Specific Year And Month

Dec 1, 2012

In a sheet I enter the following:

... in A1 a year (say 2012)
... in A2 a month, formatting as "MMM" (JAN, FEB, MAR etc.)

How to automatically get in column A (say from A3) all the dates of the month entered, formatting as "D/M/YYYY" (e.g. 1/1/2012, 2/1/2012/ 3/1/2012, etc.)?

View 3 Replies View Related

Function To Fill All Days Of Month To End Of Month Based On Workdays

May 1, 2006

I would like to create a monthly inventory, based on workdays (Monday - Friday)Myrna Larson has a formula that I would like to use with the workday function, but I don't know how to combine them.

=IF(A1="",A1,IF(MONTH(A1+1)=MONTH(A1),A1+1,""))+ = workday

to fit on the page, I need the dates to be from the 1st to the 15th, and 16th to the 31st. I am not sure how to write this either.

View 11 Replies View Related

Date Range Formula: Beginning Of Month To End Of Month (which Is In The Current Row)

Mar 20, 2009

I have log data in two columns:
Column A: Date/time (at 30 minute intervals)
Column B: Numeric data

On the last row of each month, Iím trying to perform a SumProduct on the two columns and display that result in column C.

The end of the range is determined by the month in the current row.

Iím having difficulty finding the beginning of the range, though. I need to account for both the normal dynamic calendar days & the fact that I may get data starting mid-day and mid-month.

I have this formula, but Iím not sure how to make the first array dynamic or if this is even correct approach.

Manual
=IF(OR(MONTH(A1009)=A4)*(A$4:A$65536

View 9 Replies View Related

Using Offset From Latest Month To Calculate 3-month Average Within A Range

Jul 1, 2009

I have a spreadsheet that has columns of monthly values for three years of financial data and where the values for the latest month are added to the last column. Months that have not been completed will have a zero value (e.g. Jul-09).

Jan-09

Feb-09

Mar-09

Apr-09........

View 9 Replies View Related

Automatically Bold And Highlight The Current Monthís Total And Month Name

Jan 21, 2010

I have a spreadsheet for monthly supplies. In row 1 is Jan Ė Dec and in the row 2 below are empty cells where there will be a total for that monthís purchases. I want a conditional format formula to automatically bold and highlight the current monthís total and month name.

Also, when I enter February totals next month and that number is input into Februaryís total, I want that month and total to bold and highlight BUT I also want the previous monthís bold and highlight to vanish at the same time. Is this possible?

View 9 Replies View Related

Auto Populate Calendar Days Based On Month / Year And Auto Insert Work Based On Dates / Name

Jul 31, 2013

I am trying to auto generate a calendar based on two drop down menus - Month and Year.

Once the month and year is selected I want to import all work orders onto the calendar based first on the "Labor Name" found in the list of work tab, then assign each work order for that labor name to the respective date on the calendar for the month.

August PM Schedule Demo.xlsx

View 2 Replies View Related

Attached Worksheet Automatically Shade Out All The Saturdays & Sundays In Any Given Month Everytime You Change The Month/Year Cell

Jul 19, 2006

Is there a way to make the attached worksheet automatically shade out all the Saturdays & Sundays in any given month everytime you change the Month/Year cell at the top of the worksheet, as example? I've tried using the weekday/Weekend formula, but can't quite get it right.

View 2 Replies View Related

Show Date In Spreadsheet As Month And Year

May 15, 2012

I am trying to show the date in a spreadsheet as the month and the year.

The date is in B2 30/04/2012 and i want it to show in C2 as Month/Year.

I am currently using =month(B2) which shows it as a no ( 4 ). Can i make it show as month / year?

View 4 Replies View Related

Days Fill Auto According To Given Month?

Aug 5, 2014

I am facing problem in "Month & days" formula

In my current salary sheet "Days fill auto according to given months" from 1-31 as you can see in my attached salary sheet

Salary.xls

the Big problem is i want my salary sheet give dates from 23-22 for example if i select "January" sheet should display date from "Jan-Feb" starting from 23rd Jan to 22 Feb only.... and if I select February than sheet should display date from 23rd Feb to 22 March.... and so on

and if there any blank cell left in the dates area it will highlight it with RED auto as u can see in attached sheet.

View 5 Replies View Related

Auto Calculate The Sart And End Of Each Month

Dec 9, 2008

I have a workbook where I would like to enter the date of the starting month when the workbook is being used, and I want it to calculate in the next cell across the ending of that month.

View 4 Replies View Related

Auto Fill Days Of The Month

Nov 30, 2009

Having a problem trying to figure out how to create a auto list of days of the month.

Currently I have an input cell with the format mmmm/yy

I then have a list using Weekdays($A$1)... +1....+2.... etc etc

While this works for this current month ( novemeber ) as soon as I chnage the input cell to decemember the list messes up.

The list is in the format of ddd dd

View 5 Replies View Related

Auto Lookup And Sum Month Totals

Feb 23, 2010

I have a table which holds a grid of data (see attached spreasheet)

On the next sheet I have a table which I polulate with the sum of the month for each column.

I need a way to auto populate these based on the data present.

There will never be more than 365 days worth of data however this data could fall between any dates.

I need the second sheet to firstly populate itself witht the months which fall within the date range, then to sum the relevant column for each given month.

View 7 Replies View Related

Auto Cell Color For Each Month

Aug 6, 2009

I have a spreadsheet that shows which month a vendor pays. I need each of these months to have a different color, for ease of reading. I have tried the CF, but I can only get three colors out of it.

I got this to work, but not how I want it. I would like to be able to auto color the cell when I leave that cell. The way it is now I have to go back and click the cell again. I have tried putting this code in 'SelectionChange' section of sheet1. I have put it everywhere I could think of, but it still does the same thing, I have to click back into the cell to give it color. Is there any way to have it so that when I push enter, or tab out of the cell it will give color to the cell I just left.

View 2 Replies View Related

Auto Select New Data By Month

Aug 9, 2006

I am trying to write a code that runs on a worksheet/workbook activation that will copy data from one sheet to another based on which month it currently is. I can easily do this with a Command Button. Here is an example of what I'm looking to copy from sheet to sheet.

Sub Jan_Button()
' UnProtect
ActiveWorkbook.Unprotect
' Deactivate Screenupdating
Application.ScreenUpdating = False
'January

'Rolling Month From Pool to PVT Calc
With Sheets("Pool")
.Visible = True
. Range("V2:W11").Copy
.Visible = False
End With

I will be pasting to the same location each month but I will be copying from another range of cells as my targets change every month. This is where my autoupdate idea is coming in. I hoped that for example this month is August so it would automatically paste over Augusts Data and when September came around it would automatically update to the new data set.

View 9 Replies View Related

Excel 2007 :: How To Convert Month Number To Month Name

Feb 16, 2012

I am trying to add a dynamic date rang to the heading of a table. I can find the earliest and latest dates, and want to concatenate them in the heading...

January 1 2012 to January 31 2012

The day and year (and the other bits) are easy, but is there an easy way to convert 1 to January, without going through a vlookup.

View 14 Replies View Related

Converting Month Numbers To Month Names For Pivots

Oct 24, 2011

My data sheet has a Month column, and those months are simply numbers one to twelve. They are formatted as plain old numbers. The data is coming from somewhere outside Excel.

How the numbers look in the data sheet doesn't matter. But I use this data to generate pivots, wherein the months are the columns. I want it to say Jan, Feb, etc. across the top of the pivot.

I realize that I could just loop through the raw data and convert the numbers to text. However, I want the pivot table to recognize this data as dates, so that I can do date-related stuff, such as the date grouping described on p. 85 of Jelen's Pivot Table Data Crunching book.

If I go into the raw data and try to convert the cells to the mmm format, they all convert to January.

I'm aware that formatting doesn't translate from raw data to pivot table anyhow. But it's not clear to me how I can use the date field grouping functions without somehow showing Excel that these are dates. Or is the pivot table smart enough to realize that just by the names, even if they are formatted as text?

View 6 Replies View Related

Total Liters Sold Per Month In 24 Month Period

Aug 14, 2012

I have a range of dates in c1:c285. I have a range of liters sold on those days in e1:e285. I want to total the liters sold per month for the 2 year period.

I was trying to use =SUM(IF(MONTH(c1:c285)=1,e1:e18,0))

However this just returns a "value" comment. I think even if this worked it would add the months together for the 2 years so both June figures would be returned as one figure.

View 7 Replies View Related

Day Of Week -day Of Month List Auto Setup

Feb 18, 2009

What I'm after is a sheet that self generates the day of the week in column A and the day of the month in column B. I have a month long sheet where daily entries get made in the DOW row, the day of the month is a reference. I have a macro to generate a new sheet for the next month and would like to auto populate the DOW and DOM. This typically gets done on the second day of the month (data from the first day is entered on the second)

View 4 Replies View Related

Auto Calculate End Date Of Previous Month

Jan 28, 2009

I have a cell in which I will input the last day of a month (e.g., 1/31/09, 2/28/09, etc.) In another cell, I want Excel to show the last day of the previous month. Is there a formula to calculate this automatically?

I enter in Cell 1: 1/31/09
Excel calculates in Cell 2: 12/28/08

I enter in Cell 1: 2/28/09
Excel calculates in Cell 2: 1/31/09

View 2 Replies View Related

Auto Moving Cell Formula Every Month

Jul 22, 2009

I have a row C1 - AN1 that has percentages in, these are named per month, ie C1 = Jan, C2 = feb etc.

In another cell M88 I take the average of the next 9 months so that in feb I need from Feb - Oct, in Mar in need Mar - Nov etc, can I get this to change automatically on the 1st of ervery month?

At the moment I have to do this manually but I have a lot of sheets that this is for.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved