% Calculation For Outstanding Reports?
Dec 3, 2013
calculating the percentage for outstanding reports.
My employees have 30 days to complete a report so if they complete the report within 30 days it is 100%
Each day the report is outstanding the % is reduced i.e. 32 days = 98%
I also have dates set-up to give me the actual # of days the report is outstanding i.e. (date received by me) - (date of the meeting) 12/1/13 - 11/30/13= 30, but the % is not working well
What I have tried for the % is
number of days outstanding /100 * 30 (number of days per month)
91/100*30
The % is needed for yearly evaluations
View 5 Replies
ADVERTISEMENT
Feb 17, 2014
I have an expense workbook (Data) with 4 columns (ID, Item, Cost, Date). I would like to create macros that will generate 3 different reports and write to 3 different worksheets.
The first report is sorted and sum up the cost for each item. Please see the worksheet "Item".
The second report is sorted and sum up the cost for each part ID. Please see the worksheet "ID".
The third report is the cost for each month and Year-to-Date cost right next to it. Please see worksheet "Summary".
write the macros for each of these reports assuming that we don't know the number of rows in the "Data" worksheet.
View 4 Replies
View Related
Jan 14, 2010
I'm trying to develop a formula that will take info from one tab (which would be the banks Cleared and Outstanding Checks report, and match with the checks that I have issued from the other tab. Basically this is for reconciling a bank account with the books. I want to add a column in tab #2 that shows if a check is cleared or Outstanding. So if I'm explaining this poorly, I apologize. Reply back if you have any specifics.
View 4 Replies
View Related
Jun 1, 2007
I am trying to write some VBA code for Excel that looks at a several different cells for a specific value and if that value is there returns a prompt message outlining to the user specific cell info. For instance, if some Excel cells in a column show that someone has not paid their bill yet, I would like a message to appear as soon as one opens the spreadsheet showing the bills for what customers are still unpaid.
View 4 Replies
View Related
Mar 10, 2014
i have a property and tenants have arrears in rental When i receive money from tenants i want excel to deduct that amount from arrears column first then balance from current rent.
View 4 Replies
View Related
Feb 10, 2009
I am trying to create a worksheet which can calculate the Outstanding Work Estimation based on available information.
The information available with me is presented in Rows 3, 4, 5, and 6. For these types of work orders, I have information regarding their Previous Outstanding Status, New Orders, Completed Orders, and at presented Unworked Orders (or Outstanding orders at the end of Jan 2008).
Second set of information is available in rows 9 to 23. This information provides for which type of orders were completed by Staff 1 and Staff 2.
Based on these two sets of information, I am trying to get to the point where I can calculate the following:
a) Applying the weight of orders (namely 1A, 1B, 1C and Blend) by using the drop down list function in column B, I should be able to get the results in Column C (C25 onwards till C40), Column D (D25 onwards till D40), and Column E (E25 onwards till E40). Simlarly for all other months...for February, March, and so on...till December 08.
View 2 Replies
View Related
May 26, 2007
Files :- [url]
i have upload 2 worksheets (in a zip file)
i have attached my 2 files,
1 is report of Feb 2007 & other is March 2007
on 1 Branch,
i got 200 Branches reports & i have to compare the people recored and have to identify following things
1) in which Days bukket is fall in Previous report ( compare with current)
2) if the Account # doesnot match then it should be HIGHLighted!
what i have to do is to compare the Account # with Previous & add a New Column & write the previous days (in number) plus highlight or show some remards that doesnot exisits in last report,
View 11 Replies
View Related
May 14, 2008
I am trying to create a report that takes two user inputs and looks them up on another worksheet in the workbook and returns the right value. As an example I have the user input a date and a job location and on another sheet there is also a date and a job location that has a population associated with it. Is there a way to return this information so that it changes whenever the user changes these two criteria
View 9 Replies
View Related
Nov 13, 2008
I'd like to created a 'Filtered Report' in which data changes based on my selection. For example, in this scenario I have 2 columns, 'Part Type'(you may pick Part1, Part2, Part3) and the 'Results' columns. I have displayed 100 entries(which means at this point they're all displayed, no filters).
When I apply the filter, and I choose Part Type to display Part1 and its "Results', I would like to dynamically be able to display somewhere in that Sheet something like this:
Part Type: Part1
Total: This would be a =sum(of the 2 cells below, in this case it'd be 17)
Pass: 14
Fail: 3
But I'm not sure how to dynamically count it based on what I've filtered, so when I change to Part2, it would give me:
Part Type: Part2
Total: This would be another =sum of the 2 cells below
Pass: 25
Fail: 4
View 9 Replies
View Related
Apr 18, 2006
I use excel pivot tables a lot for most of my information gathering for reports that I produce. Problem I keep running into is the 64k row limit. I work with up to 200K rows of excel data at a time and just need to pick out a summary of certain columns. I have read (mostly adverts) good things about crystal reports and have been considering giving it a try. It will be a steep learning curve for me, but I was wondering have this group any views on the program before I splash out on books, DVD courses etc.
View 6 Replies
View Related
Jul 2, 2007
I have an Excel Spreadsheet WorkSheet Analysis Work there are two reports side by side. A) Source of Truth & B) Analysis Studio.
What i'm trying to do is find out if Analysis Studio Matches off with the Source of Truth.
The columns in Analysis Studio being Column M & Column N have to match the Source of Truth Columns E "Cil account" & Column I "Code Name". Now the problem is that the names in Analysis studio Column M Example Cash is Different to the Source of Truth Coloumn I Example Cash Assets. This is what I would match as Yes being the Same as it is. The other Problem is Analysis Studio Column N has the Account & Name in one cell whilst the Source of Truth has both these in separate Columns. I’m happy if it can just match off Column E being the account to the Analysis Studio Column N. Ideally would love the results to fall either go in Column O being YES it matches or Column P it does not match the Source of Truth.
View 5 Replies
View Related
May 16, 2008
Every week I run the same report for different projects to show financials and such.
Each project will have a different number of rows based on how large they are or the scope or a combination.
The number of columns is always the same as well as the basic headings.
The system that provides the output creates a bland (well, ugly) report. Not the type of thing you want to distribute.
I'd like to create a report format that I can quickly and easily cut and paste (or use another functionality) to use on these reports. Yes, I can cut and paste the formatting from the "Master" report's format but it's not easy to do as I have to keep highlighting each report's data range before pasting. I'd like have my OWN template under autoformat.
View 10 Replies
View Related
Oct 21, 2009
I do testing of industrial equipment, collecting a lot of data along the way. Once I've calculated the performance metrics of existing systems, I do a cost analysis on proposed repairs or replacements.
I have a couple of goals in trying to streamline my spreadsheet process and make it more effective.
My worksheets usually end up with way more columns than I can view on the screen and that gets pretty cumbersome, so I want to develop input forms that will make data entry simpler. I've tried transposing the formats but they get even more difficult to handle that way. I just ran across the "Build a UserForm for Excel" pdf in this forum and I think that's going to help quite a bit.
Then for each of these types of projects I need to develop at least two report formats, one that will be comprehensive for each individual piece of equipment or subsystem that I'm analyzing, and another that's more of a summary of entire systems, such as a network of pumps all owned by the same customer. Ideally, this summary report would allow me to rank the subsystems by various criteria, such best ROI or most critical to operations. Format-wise what I'm looking for here is to strip away anything not relevant to decision makers, just reporting the important ID info and key results.
Finally, I'd like to be able to store all similar jobs in a single database so I can easily search through past results for comparative purposes when doing higher level analysis on new projects. I keep seeing the term "relational database" bandied about but don't really know if that's what I'm talking about or not.
View 10 Replies
View Related
Jan 19, 2013
i would like to create reports and queries in excel just like in access is this possible or do you import a layout,i have a sheet that has data on i would like to be able to pick just one row or mutiple rows and print them off could i save them to a menu to use over and over again,would i use a userform to search for these or can it be done otherways ,have seen pivot tables but seem bit complicated
View 2 Replies
View Related
Mar 9, 2013
I coach High School track and recorded all the kids workouts in DATAEASE - I had to upgrade my PC - (Dataease no longer works) so I am trying to record the kids workouts in EXCEL and be able to produce reports either by Code or NAME. Attached worksheet - SHEET 1 - data entered / SHEET 2 - is a report I would like to produce.
View 3 Replies
View Related
Jul 26, 2013
How to print multiple reports and have come up with the following code which does exactly what I want, except it prints the reports out individually. I need them all combined into one PDF.
Sub PrintCombined()Dim c As RangeApplication.ScreenUpdating = FalseWith Sheets("Key Metrics Report")
For Each c In .Range("S1", .Range("S1").End(xlDown)).Range("C2").Value = c.Value.PrintPreview 'PrintOutNext c
End With Application.ScreenUpdating = TrueEnd Sub
View 3 Replies
View Related
Nov 13, 2006
Some notes on my example file:
The data would get pasted into the first sheet, in the shaded area. It might be exactly those IDs, or others - possibly a longer or shorter list each week.
Sheet 2 has the lookup master lists, or checklists. I can set these up ahead of time, and don't change week to week. The input IDs will be checked against these lists somehow on the Report pages.
Sheet 3 and Sheet 4 are the Report pages.
I want a formula in column A to match the input ID values from Sheet 1 to their appropriate checklist on Sheet 2, and return the ID value on the appropriate Report. If the value matches Checklist 1, it should repoirt on Report 1. If it matches Checklist 2, it should report on Report 2. If the value isn't on the checklist, it shouldn't report, don't report it. If there are ID values on the checklist that don't appear on the input, they also should not report.
On the Report pages, once I have the correct IDs reporting, I can put in other formulas to pull Revenue, Cost, etc. info from elsewhere, VLOOKUP-ed from the IDs. (I know how to do that.)
The key is that in terms of sorting out the correct ID values to the correct reports, I don't want to have to do anything to the Report pages each week (like sorting to get rid of blanks, etc.) - I just want to paste in the Input.
View 3 Replies
View Related
May 13, 2007
I have 2 reports on one worksheet. I have put a button at the top of each report to navigate back and forth. One report is located at a1:S27 - it shows up fine. The issue is the other report located at BA1:BT52. No matter where I assign a name to it the report doesn't come up full page with cell BA1 at the top left hand corner.
View 3 Replies
View Related
Jun 2, 2007
Is there any way to stop the MS error reports
It wont say what the problem is or how to solve it
View 9 Replies
View Related
Aug 25, 2007
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.
View 5 Replies
View Related
Nov 3, 2009
i have to compare two financial reports from two different sources each and it should derive the same result.
I am still working on it and only focus on Column C & D. However, i have come across some problems and hope that these milestones can be cleared so that i can move on to another step to achieve my goal.
So, my current problem is
1) #N/A (eg Cell D9 & D32) will be resulted when hlookup function is used. Since the workbook 'CB_Consol_2009' does not have the same name appear, how can i make the answer become '-' instead of #N/A when hlookup function is being used?
2) Double counting. The same name appears more than one time (eg CURRENT ACCOUNTS-RET_ (01#0505_01) and as such, its figures also calculating more than once. Is there a way to calculate the figure only once so that i come out with the correct grand total?
As my objective is to compare two financial reports results (which should be the same), this is the table i can think of. If anyone here have any constructive comment on my current format.
View 12 Replies
View Related
Aug 21, 2012
Running reports with start and end dates, example I need dates to Start 10/01/2010 and end date of 09/30/2011, retrieved date has some dates before 10/01/2010 so I need a formula to change that date to equal 10/01/2010 and the same for the end date need to have that date change to 09/30/2011.
View 3 Replies
View Related
Nov 29, 2006
Report 1 is downloaded from SAP into Excel and Report 2 is downloaded from AS400 into Excel.
Report1 contain 3 columns as follows:
Column A - Invoice #
Column B - Invoice date
Column C - Invoice Amt
Report2 contain 5 columns as follows:
Column A - Invoice #
Column B - Ship Date
Column C - Check #
Column D - Amt Paid
Column E - Payment date
I tried to use the Vlookup function in Report 1 Column D to lookup the matching Invoice # in report 2 and bring in the corresponding Amt paid. It did not work and I assume that this is because of the formats in the downloaded files. I did try to change formats for the columns involved but this did not work. This function works fine if both files were setup in Excel, however in these downloaded files even though saved as excel workbooks, something prevents this function from working. I do know for a fact that the same invoice number does show up in both these files but the vlookup does not pull up the required Amt paid from Report 2.
View 9 Replies
View Related
Jun 20, 2008
I have the following spreadsheet Columns on master sorted by Column J:
A--------------E-------------J
SUPPLIER----CARRIER------LOC CODE
BELGIUM-----HAM-----------BP
HYDRO-------MSC-----------BP
DAINIPPON---NNR-----------BP
BELGIUM------YM-----------BR
BELGIUM------YM-----------BR
HYDRO-------MSC----------CA
BELGIUM-----HAM-----------CA
HYDRO-------MSC-----------CA
HYDRO-------MSC-----------DE
DAINIPPON---NNR-----------DE
BELGIUM------YM-----------DE
BELGIUM------YM-----------DE
What I need too do based on the Codes in Column J is create a separate workbook for each different code.
View 9 Replies
View Related
Sep 5, 2006
I have included a copy of the spreadsheet. What I need to do is get a cummulative duration report for each crew. This particular spreadsheet has less data than most.
Many times there are 8+ crews. I need to make a column or graph or report or something that will continually update as changes are made and report back something like; Sewer 142 hours, Water 200 hours etc...
View 9 Replies
View Related
Nov 3, 2006
I'd like to be able to drop one table of data into a workbook that already has sheets of two or more master tables, and two or more report pages. A formula on the report pages would compare aspects of the data to the master tables, and report the data on the correct sheet based on certain criterion. The reports would not have blank spaces, need to be sorted further, etc.
My initial impulse was to think in terms of ol' verable VLOOKUP, but I can't figure out how to get the report formula to move on to the next line of the data table if it doesn't find a match for the first line - short of adding another VLOOKUP (and another, and...), which is totally impractical given that the data table will likely have 20,000+ lines.
View 4 Replies
View Related
Nov 7, 2006
I have been lucky building codes, but this one has me bad. I already have a code that pulls data and puts it on a spreadsheet. To activate this I put in a date that I want to pull data from the PI server. What I want to do is save that data into an excel spreadsheet for history. I want it to know the date and save all the information. Then at a later time I can pull the data back up again. I can currently get it to move the data, but it doesn't sort by date and when you do the macro over it writes over what i just had. How do i get it to sort by date and enter into new cells?
View 2 Replies
View Related
May 21, 2007
i have this userform which has textboxes. Every information in the textboxes are linked in the two kinds of reports namely: Report 1 and Report 2.
After filling the textboxes, what are the codes that i can use so that the command button 'Generate Reports' will display the Report 1 in Sheet 1 and Report 2 in Sheet 2?
View 9 Replies
View Related
Oct 2, 2007
I would like to create one page (sort of like a form) to use to collect specific weekly payroll related data for multiple employees and be able to automatically transfer that data to its own weekly data page based on the week date, and then also automatically post specific data to each individual employees' total pages. While I am self taught and comfortable with formulas I do not yet have a lot of experience with VBA and am just now recognizing and exploring the true power of Excel so I don't always yet see the best way to accomplish my goal.
My current way of doing this is to reuse the same form page. I plug in the data for each employee that week, it calculates, I print it out, and then I manually update each employee's total page for that date. As I do this I lose the previous week's details. I would like to retain a page for each week's details in addition to having each employee's total pages. Is this possible or is there a better way to accomplish my task?
View 2 Replies
View Related
Aug 8, 2009
When I creating a Pivot Table Report on a different sheet. I have attached the Stock Report, where I have created a Button at the top to create the report, but the worksheet name is the default Excel name rather than the one that I want (in my case the name of the new sheet should be "Admin".
View 3 Replies
View Related
