I have lots of monthly reports to prepare. So what i do is to copy previous month report file and paste and rename it as current month report. Once i open it, i need to change the linking to the next colume of the source files. Eg Source files July is in Column H, then now i have to change it to Column I in my current month reports.
Furthermore, one file may have a few source files so the column may differ also.
How do you create a macro to copy the information from my weekly reports to a monthly report and be able to update automatically. If you had 4 worksheets (for each week of the month) and 1 mastersheet for the whole month in a workbook. All titles are the same and If you needed to copy all the data that is in the columns, say, A through I, starting with row 4 to however many rows are in a given week. The reports can be made up of numicerial values, text and dates. Let me know if more information is needed or an example worksheet.
View 4 Replies View RelatedI have an Excel report that I'm wanting to know if it can be automated. Basically it starts off in Access, I have a few queries that run via an Access Macro, once the queries are done there is one query via the macro that exports a file of data to excel. I then have a 2nd excel file that is a pivot table that I refresh from the exported Excel file of data from Access. Once refershed, save it, upload it to a Sharepoint Site and send out an email via Outlook. Basically I'm wanting to know if this can be automated to were it runs at like 6am or something before I even get on my computer. I'm not a vba expert, I can do some basic stuff but what takes most people an hour or so to do would take me a day or so. Also would this be easier to just have the pivot update directly to the Access DB?
View 1 Replies View Relatedwhere to start with automating a monthly report that I put together.
My report is generated from two or three tables of data - roughly 25,000 records.
Currently, I copy and paste this table of data into one worksheet, and then have pivot tables that are written to take data from this table (this is a typical marketing sales funnel - calls, raw leads, qualified leads, customers, revenue). Then I have another worksheet that is the display to the client, with the proper formatting, mathematical calculations, totals etc. On this client facing worksheet, I pull data from the pivot table (using =GETPIVOTDATA formulas).
It seems that I have constructed this report in a very inefficient way because I have a ton of manual work to do every month to make the client view presentable as changes come about in the raw data. For instance I segregate the client facing report by lead sources, but when new lead sources get added in month by month, I have to edit the client facing report at length.
where to start, or what steps to make this more automated
I have this table that I put together everyday describing the activity of my department. There is the date displayed as "Num-Date", the day of the week and multiple columns of data. Every day I have to add a new row and update the date, and the data, etc. I have a macro that inserts a new row and auto-updates the date and weekday, I also have a web query set up to fetch the data I want and display it right next to the table, but what I want to do is make the table more intuitive and automated.
View 1 Replies View RelatedI have table and need to take out montly total for each worker...
Now...
Each hours in day have own factor. (I need total hours per day but for illustration)...
So when worker works day shift from 8:00 to 16:00 it's easy... 8 hours
When works from 8:00 to 20:00 it's 8 hours + 4 afternoon hours
When works from 20:00 to 8:00 it's 2 afternnoon hours + 8 night hours + 2 day hours
Aditional problem is when day intercept holliday or sunday when that factors need to be included (if holliday is at sunday then it's like holliday).
Here is some attachment:
Book1.xls
I've also added last day of previous month and first day of next month because of night shifts than need to be calulcated. Therefore correct number of hours is 168 and not 188.
Below I calculated manually those numbers wich I want to be automated...
Also.. This is table I get.. If it's easier to make it somehow else, OK by me. And any number of aditional columns is not problem...
I am new at excel and have very limited experience. I have a employee training database, with employees (in column A2:A54) and the certification names (in row A1:AS1) that I must generate a monthly report on employees that are expiring within the current month. The cells are already conditionally formatted to change colors for one month prior to expiration date, current month of expiration date, and past the expiration date.
I am manually gathering the data then adding it to another sheet. In the mist of trying to gather data, I am missing and/or overlooking information. I wanted to know if there is a way to automatically generate a report based on the expiration dates that would pull the employees name and which certification is expiring or expired, preferable both. I have tried reading threads and applying advanced filters but due to the number of certifications it is not functional to do it this way.
I have attached a sample of the spreadsheet : ETD.xlsx‎
I have a workbook with 12 sheets for monthly results and a Year to Date sheet. For presentation reasons, the monthly results are listed as seen in the first three worksheets, but I want the YTD results to be structured as seen in the fourth worksheet. I have shown how I am currently achieving this in the "Call" section of the YTD sheet, but as there are 12 months and many more variables in the original workbook, I'm wondering if there is any easier way than the one I'm using.
View 4 Replies View RelatedWe have a weekly "On-Time" report that shows early, late, on time, or no data. Each week has a total percentage of early, late, on time, or no data deliveries. I now need a monthly total on a seperate worksheet, but for some reason I can not get the percentages to total correctly. I am attaching the spreadsheet.
View 3 Replies View RelatedI would like to add avalue of 100 to cell E1 on th 19th of every month.
I currently have this code which I realize will just update every new month instead of the 19th but does not seem to work right.
It only works if I leave cell A1 blank and then it inserts 1/19/1900 into cell.
Private Sub Workbook_Open()
'Compare today's month against value in A1
If Month(Now) > Sheets(1).Range("A1") Then
Sheets(1).Range("E1") = Sheets(1).Range("E1") + 100
Sheets(1).Range("A1") = Month(Now)
End If
End Sub
I have a workseeht that consist of 13 tabs. This is what i am trying to do:
Each tab is for a month in 2007. The main tab is formated to show a recap of each month. So when the Janruary tab has been completed the main recap page should pull data from its tab. Then once the february tab has been completed the main recap tab should now show the data from this tab, and so on and so on.
I am currently updating the data manually by refrencing the new tab in the main tab formulas. I hope there is an easier way for me to get this data without manually inputing formulas every month.
My workbook contains 12 monthly spreadsheets (one for each month of the year). It will also contain an additional spreadsheet, I’ll call “Update”.
At the beginning of each month I will copy and paste new account information into the monthly spreadsheet (for whatever particular month we are currently in). The columns (J-N) will be blank. I will then copy and paste account information into my “Update” spreadsheet (this is done twice a week per month, from 8 to 10 times per month). This spreadsheet will contain data that needs to update my monthly spreadsheet (for whatever particular month we are currently in). The “Update” spreadsheet column B needs to populate columns J-N on my monthly spreadsheet and this will depend on column C of the “Update” spreadsheet.
Sorry to be confusing . . . .
Update Spreadsheet
Column B – Payment Amount (dollar amount)
Column C – Commission Type (Low, Medium, High, Super, Duper)
Monthly Spreadsheet
these columns need to be populated with the dollar amount
Column J – Low
Column K – Medium
Column L – High
Column M – Super
Column N – Duper
I forgot to mention that all data is compared via the account number!
However, to make it even more confusing if I am updating my monthly spreadsheet and during the 8 to 10 times I update it during the month I find that I already have a dollar amount posted for a particular account for a particular commission type (Low, Medium, High, etc.) I do not want it to keep adding up the dollar amounts. So, if I have an account that has $25 for “Low” and then next time I update my monthly spreadsheet I have an additional $75 for “Low”, for the same account, I do not want it to calculate it to $100. I need it to either color code that entry on the “Update” spreadsheet or toss it onto another spreadsheet . . . just so it stands out and I know that I have to handle it differently.
What I've got is 2 worksheets. One is updated every month with new data and therefore the style of the new worksheet is likely to change.
The other one is static and needs to take the data from the updated one. I can't just use a basic =sheet1!A1 because the data will get moved around.
I need to be able to tell it to: look in the new sheet, based off the column heading and row heading find the cell and copy it to the same place on the static sheet. e.g.
In G14, I need it to find on the second sheet "Apr 08" as the column heading and "Yell.com" as the row heading and put it in G14.
It would be easy to do it manually, but as the spreadsheet grows, it'll be harder and harder.
I have a 2 sheets in a workbook by the name Entry and DataStore. I am entering the daily data in Entry sheet and then manually updating the data in DataStore sheet. Is there a macro to automate this.
I have uploaded the sample file with the expected output comments to the below link:
Free large file exchange service without size limits.
i have facing a big problem nowadays.problem is that, i have to regularly update manually(copy & paste) "oil filling", " stock" & "meter reading", coming from every day by the supervisors of our company for verious sites spreading accross the our state, nearly 1305 site. i have attached the master file(which should be updated) with the reports coming from the supervisors(Rosan & Jhon) in another sheets. the master file is same form as i given. is their any way of automatic update by any macro.
View 2 Replies View RelatedI have a table of yearly totals for the amount spent by x. I also have a growth rate for each month so for example in 2001 in jan the growth rate might have been 0.3% and feb 0.5% What I want to do is for each month based on the growth rate and the total produce a value for each month which sum to the total amount. It's also important to note that it restarts each year.
Link for excel file is here: [URL] ...........
I’m currently pulling data into two columns labeled “Monthly” & “Non-Monthly” respectively. They indicate work orders with a frequency of “Monthly” or “Non-Monthly”
The Monthly data is obtained using the following formula:....
Find the attached excel sheet.
I have, every month data like for one Year, every day in a month we will update the sales information in to excel sheets like jan, feb, march, ...December
In the Main sheet i want total, i have bunch of data and wide range of items, how to get monthly consumption into main sheet.
excel.xls.xlsx‎
I need to create a running monthly total formula for a worksheet. This is something i need in the data, so i can manipulate it without using a pivot table.
I think I would use the SUM, MONTH, and IF formulas, and maybe EOMONTH.
I guess where I'm stuck is figuring out how to make conditions for my SUM formula. So as the dates go down the page, I would like a running total in a column to the right, that will also restart with every new month.
Attached is my example.
I am looking for a formula to sort out a budget problem I am currently working on... I am guessing that the formula is pretty simple but my mind is blank...
What I am trying to do is:
Cell A1 is my cash budget
Cell A3 is actual cash used
Cell A5 is The Difference between A1 & A3 (sum A1-A3)
What I want in Cell A7 is a formula to tell me if my Budget is over 50% either way..
For instance if A1 = 100 and A3 = 155 then I am 55% over and would like a note to appear saying "Explanation required".
If A1 = 100 and A3 = 45 then I am 55% under my Budget and would like a note to appear saying "Explanation required".
I believe there is a way to summarize monthly data with the month in rows and the heading across columns. The goal is to have a formula summarize the data with Q1 through Q4 in rows and headings across columns and vice versa. Sample data attached.
Sample.qrt.data.jpg
Basically what i need to do is put a formula in each cell of "Monthly Overview" that references the correct property and monthly total from the "Monthly Billing"
Example...."Monthly Overview" C2 needs to reference "Monthly Billing" for what was billed to Apex Broadcasting (B column) in Jan (C Column)
How can I add data to the monthly sales section in the workbook? I can't seem to figure out the code.
mockup.xlsx
I'm trying to figure out a formula that will give me monthly average (per person) on the following report. The problem is that people start at different times and sometimes they don't produce in a given month after they have started. Basically I'm trying to get a monthly average from starting month to the last full month, in this case January.
View 8 Replies View RelatedIf i borrow Rs.50000/- at 7.25% interest compounding monthly, repayable in 4 quarterly instalments, when i put it in excel using pmt function for emi quarterly payment and ipmt for interest calculation at the end of the 4th quarter (i.e last instalment) the balance will not become zero it shows a balance of Rs.14.35 its due to interest compounding monthly, is there any formula in excel to overcome it?
View 2 Replies View RelatedI'm trying to make a worksheet that calculates monthly residuals. For example, if I have income of $275 in the first month and then the next month I have the first $275 and an additional $275, and so on. I've created the following formula
=RC[-1]+(RC[-1]*2)+(RC[-1]*3)+(RC[-1]*4)+(RC[-1]*5)+(RC[-1]*6)+(RC[-1]*7)+(RC[-1]*8)+(RC[-1]*9)+(RC[-1]*10)+(RC[-1]*11)+(RC[-1]*12)
where RC[-1] is the base amount that adds on every month. This gives me the number I am looking for. However, I am wondering if there is an easier way to do this.
I have a column line chart to which I add data monthly and then have to manually update the "source data" to reflect the added data on chart. This is a rolling graph, which mean that I have to remove data for one month(from last year) and then include the new month's data. Is there any way on automating this process...like a macro or something, so once I add the data excel automatically removes one month of old data and make changes to include fresh data. Eg Currently chart is based on data from A2:F2 and I add new data to cell G2. I need something which automatically update the source data to cell B2:G2.
View 4 Replies View RelatedI'm looking for a function that calculates a fee deduction based on the 28th of each month.
I'm paying back £200 on 28th of every month starting 28th Feb and was hoping that a formula could keep track of this...
A
B
C
D
1
£1,300.00
=TODAY ()
2
-£200.00
28/02/13
FUNCTION
How to do it but basically I'm trying to put a formula in D2 as follows:
If Today's date (C1) equals C2 I need B1 to reduce by the amount in B2
How to continue it calculating reductions per month by duplicating the formula...
I have a worksheet with monthly sales figures by associate and by store. The store has a monthly goal as do the associates. If the store hits it's goal then the overall sales total is multiplied by 1% and then divided by the percentage of each associates involvement to reach that goal. (ie...150,000*1%=1,500, John sold 35,000=23%, so John gets $345 extra commission). If Johns goal was $25,000 and sold $35,000 he gets 1% or $350 commission. In turn, if he meets 1 or both sets of criteria those will be added together. If he doesn't meet either one then the result is Zero.
I have the store goal and Johns goal in separate cells to reference against. The actual sales cell is formula based.
This is basically what i'm trying to do:
If criteria 1 is met then % of 1% of store goal, if criteria 2 is met then 1% of individual goal, if both are met result1+result2. if neither is met then zero. I think?
Need the formula to multiply monthly values with a maximum value in any one period? The sample file attached explains it better.
View 2 Replies View Related