SUM Based On Cost Center Wise

Jan 14, 2014

I need a sum of amount based on the Cost center & Category from the details table , can i have a formula to get them .

Please find the example amount in the attached excel sheet highlighted in RED Colour.

View 3 Replies


Excel 2010 :: Importing Data That Has Imbedded Cost Center Subtotals - Move To Column

Jul 29, 2014

I am trying to create a Dashboard in excel (2010) using tables/pivot tables to build it. The data I am bringing into excel has these key fields of data: cost center+cost center description, general ledger account+general ledger account description, and YTD amount.

My problem is the data is from an external source report and the report has subtotals built in at cost center, and the report's format of subtotaling puts the cost center first and then the general ledger accounts below. There is no
formula value in the cell that has the subtotaled amount and the number of general ledger accounts can vary depending on whether there has been general ledger activity.

I want to take this format:
July YTD
Cost Ctr 1050 XYZ$6.00
625110 Supplies$2.00
650150 Postage$2.00
650550 Fees$2.00
Cost Ctr 1052 ZZZ$4.00
670500 Pens$2.00

and have it look like this
Cost CenterCost Center DescrGL AcctGL Acct descYTD Amt
1050 XYZ 625110 Supplies $2.00
1050 XYZ 650150 Postage $2.00
1050 XYZ 650550 Fees $2.00
1052 ZZZ 679200 Pens $2.00

Besides manually doing data moves and assigning a unique sort sequence number to keep the records together, how else can I quickly move my cost centers to a new column and keep the cost center with the gl account and $amount?

View 3 Replies View Related

Date Wise / Name Wise Total With Round Function?

Aug 3, 2014

i have a large data in excel sheet A2:C1500 i have attached a sample sheet for it, i want in cell C27 round off sum (as like in cell D27)

View 3 Replies View Related

Copy To New Sheet By Party Wise And Date Wise?

Jan 29, 2013

I would like to make copy by party wise and month wise to new sheet up to last row.

Tax Exempted
Tax Exempted
Party Name


View 1 Replies View Related

Iteration Inconsistency: Allow For A Cost Being Added To Loan Amount Where The Cost Is Based On The Total Loan Amount

Mar 15, 2007

In a financial environment we have a calculator which uses iteration to allow for a cost being added to loan amount where the cost is based on the total loan amount. Iteration is set to 100 iterations with max change .001

On one PC the first time the calculator is opened it gives a particular (incorrect) result. If the input cells are cleared and the data re-entered, it gives the correct result. This only happens on one particular PC. Is there some other setting , other than the iteration setting, that would cause this?

View 5 Replies View Related

Draw Line From Center Of Cell To Center Of Another

Aug 13, 2008

Following is what I want to do:

Draw a line from the center of the first cell to the center of another cell.

View 3 Replies View Related

Image Wise Records To Record Wise Image?

Oct 10, 2013

With this msg i am attaching one excel. I have the data on the basis of "image wise files data", but where how to get "record wise image numbers", i am unknown on this.

Image Wise Data final-1.xlsx‎

View 1 Replies View Related

Cost Based Mark Up

Sep 26, 2007

set up a cost based markup spreadsheet?

I need to be able to mark up values from 0.01-9.99 by 20% of their value, and 10.00 and above by 10% of their value.

I only know how to mark up values by a single percentage.

View 9 Replies View Related

Excel VBA - Merge And Center Cells Based On Date Values In Row Below

May 22, 2014

I'm using the following code (found here: [URL]......) populate dates in row 13 for a dynamic Gantt Chart...

[Code] ....

It works perfectly to display daily dates in the mm/dd format. Now I want to have an overarching month/year displayed over the entire range for any particular month by merging and centering the cells above the month's dates the above code populates, and pull in the month/year formatted as mmm-yyyy. I've attached a mock up I did manually to convey the desired result.


View 1 Replies View Related

Calculating Cost Based On Several Factors

Jan 7, 2009

i. I currently have a spreadsheet which is used to forecast resource cost for a project. The forecasted cost is calculated on a few factors - rate, allocation, contract start and end date, and expected days worked per month. One of the mods actually helped me out with this a few weeks ago.

I now have been told that there is a possibility that certain resource costs may change in the new year and that will need to be reflected in the sheet whilst keeping the historic information.

For example - XXX has a rate of 200 p/d, allocation is 1, working 18.83 days p/m and is working from 01/01/09 to 01/06/09. The current formula will work out his cost per month until contract end. Now say his rate will be changed to 150 p/d from the 01/03 and all other info remains the same, I need the sheet to calculate his revised cost from 01/03 onwards and not change the calculation previous to that month.

Now Ive actually managed to figure that part out myself by adding in two columns (over-ride rate and over-ride date) using a nested IF statement. The only problem is that if the new rate starts mid month then it will still calcuate the original amount for the full month and the revised amount from the next month.

Edit - Also, could someone advise as to how do I remove my old attachments as I have almost used up my allocation.

View 10 Replies View Related

Making A List Of IDs And Cost Based On Date Range

Jul 14, 2014

I need to make an automated way of extracting a list of IDs and the associated cost by a date range, say 6/1/2015 to 12/31/2015, plus the starting letter of the ID. I need the data to then fill a form where it can be sub-totaled. There are several additional columns of data that are not relevant so I know I can create a pivot table and then filter the data, but then I need to copy and paste the filtered data into the form. Is there a way to do this without me copy and pasting everytime?

I added a test worksheet to show the data and form.

View 6 Replies View Related

Simple Form To Display Cost Based On Discount?

Jul 14, 2014

How would I create a simple user form to display a price based off the category selected.

User types in List Price, then selects 1 of 4 discount categories, it then displays that price.
An added bonus would be copying that price to another sheet, but not necessary..

View 2 Replies View Related

Formula For Total Cost Based On List Of Hours For Each Employee Times A Table Of Rates

Jun 10, 2014

Looking for a formula for total cost based on a list of hours for each employee and a table of rates for each employee.

Please see attached file : Formula.xlsx‎

View 1 Replies View Related

Lookup With Multiple Criteria: Return The Amount Paid And Full Cost Based On The Person's Name And The Date

Apr 21, 2006

I would like a lookup that takes multiple criteria and that is not an array formula! Unfortunately I decided to use array formulae and my spreadsheet went to over 45mb!! Not good. I've searched the forum for an answer to my questions but couldn't find any! I've attached a spreadsheet as an example. The examples I am using have {Sum(IF)} formulae in it (array) and I would like to change those to others that will not increase the file size so much and will not take too long to calculate.

Basically, I would like a lookup that will return me the Amount Paid and Full Cost based on the person's name and the date. the data and the results table are both on separate sheets. It would be nice to bring that file's size back down to less than 4mb!!

View 7 Replies View Related

Sum Of Top 5 Category Wise?

May 8, 2013

I need a sum of top 5 numbers category wise. I have attached a sheet in which I would be requiring Sum of Top 5 for the month of May. I have shaded cell where i would be requiring a formula...

Also I may want to include other category as well.. For e.g. sum of top 5 for the year 2011 as well... I have tried every thing i could from Sumif to sumproduct to Large...

View 9 Replies View Related

Month Wise Sum The Values

Feb 13, 2009

In sheet-1 I have the following table

App Value Date
A 5,2 1/3/2009
B 0,3 1/2/2009
C 5,1 1/5/2009
D 8,1 2/3/2009
E 1,6 2/13/2009
F 7,5 3/3/2009
G 6,8 3/30/2009
H 2,2 4/3/2009

In sheet-2 I have the table which has the columns as below.

Month Value

The sum of the values month wise should be calculated by replacing the comma by dot

View 9 Replies View Related

Sorting Column By Birthday Wise

Oct 14, 2009

i have a data of empl their birthdate wise. i want it to sorting from birth day wise for example first " DAY then Month then year". day come first then month then year. find attched file.

View 3 Replies View Related

Choose Category Wise Sheets

Jun 22, 2007

I am attaching a sample file wherein my requirement is that on clicking one button labelled (actually textBox) "show Data sheets" should display a list of all three data sheets which are hidden and other button labelled (actually textBox) "show Reports" should display a list of all three Report sheets which are also hidden.

Actually users will be able to put data in Datasheets whereas Report sheets contain formula etc and users will not be able to put any data on those report sheets.

Only one sheet should be displayed on the screen ie when user will move to Datasheet1 from Introduction sheet, only Datasheet1 should be displayed.When user will move from Datasheet 1 to Datasheet2 only Datasheet2 should be displayed and so on.

View 11 Replies View Related

How To Count The Region By Date Wise

Feb 8, 2010

how to count the region by date wise,

For Example: Below 2 columns there are four dates available,
i want to know the count for date 1/1/2010, how many UK?, IND?, US?.
As per the below format....


Date Region

View 9 Replies View Related

Arrange Numbers 1 To 11 Row Wise Randomly With No Repetition

Mar 29, 2014

Arranging row wise an array of numbers from 1to 11 randomly with no repetition. I need it for 700 rows.

Sub test()
Dim a1(1 To 11, 1 To 2) As Double, a2(1 To 11, 1 To 2) As Double, a3(1 To 11, 1 To 2) As Double
Dim a4(1 To 11, 1 To 2) As Double, a5(1 To 11, 1 To 2) As Double, a6(1 To 11, 1 To 2) As Double
Dim a7(1 To 11, 1 To 2) As Double, a8(1 To 11, 1 To 2) As Double, a9(1 To 11, 1 To 2) As Double
Dim a10(1 To 11, 1 To 2) As Double, a11(1 To 11, 1 To 2) As Double, i As Integer

[Code] .....

View 2 Replies View Related

Month Wise Work Order Totals

Jul 27, 2009

See the attached file. I need month wise work order totals. for more details see the pivot table also

View 4 Replies View Related

How To Get Counts By Date Wise Data In 2 Different Columns

Dec 24, 2012

I have a query with regards to Counts

This is my below data.
No.Request NoDateSilverGold

in other sheet i have products Data by date wise
TotalRequest countTotalRequest countTotalRequest count

My query is how to get the total Request Count for Silver and Gold by date wise. Eg: Answer should be Silver has 2 (counts) & Gold has 3 (counts) How to arrive to that.. i have tried.. countif, countifs....

View 3 Replies View Related

Coding To Select Data Criteria Wise

Jul 29, 2013

I need to filter data on criteria wise and to move on specific sheets accordingly.

Example: sheet 1 contains all the details such as starting with AAA - 10 rows, BBB - 12 rows, CCC- 15ROWS ,DDD-13 ROWS etc in column 1.

I have created separate sheets for AAA, BBB, CCC etc

now I need all the details of AAA (in sheet1) to be moved to specific sheet AAA which i have created and so on.

I tried the below coding but there are few dependencies found.

Selection.Insert Shift:=xlDown
Selection.AutoFilter Field:=1, Criteria1:="AAA"
ActiveWindow.SmallScroll Down:=-6

View 2 Replies View Related

Count The Closed Transactions Month Wise

Dec 4, 2008

I would like to be able to count all the closed transactions for the month of May and then add Column B if they match with May ....

View 9 Replies View Related

Chart For Requirement Versus Available Stocks Month Wise

Feb 19, 2014

I want a chart for month wise requirement Vs available stocks to watch or plan for further purchases availability

Monthly Graph (Data file) is attached

View 1 Replies View Related

Count Current Year Month Wise Entries Formula

May 9, 2014

I use this formula to count number of entries month wise.

Following example is for Month of Feb.

How can I convert this to count entries for current year ( of course month wise).

View 9 Replies View Related

Summing Or Adding Up Date Wise Column But No Duplication Of Dates

Jan 29, 2013


A B C D E F (B+C-D-E)
Date Opening Stock Purchase Sales Consumption Closing Stock
17/7/2012 10000 5000 1000 2000 12000
17/7/2012 12000 2000 2000 1000 11000
18/7/2012 11000 0 1000 5000 5000
18/7/2012 5000 10000 3000 0 12000

Note : I want the above result as shown below datewise but no duplication of date but summing up of column C,D and E which is in above.Pls note that this is accounting question and also plz note that Opening stock(column B) from next date onwards must be equal to previous date closing stock(Column F).

Results :

A B C D E F (B+C-D-E)
Date Opening Stock Purchase Sales Consumption Closing Stock
17/7/2012 10000 7000 3000 3000 11000
18/7/2012 11000 10000 4000 5000 12000

if I could get the formula in excel ,since this is the sample data as I do have a numerous & huge data like shown above datewise where I need to add C ,D and E but Opening stock(column B) from next date onwards must be equal to previous date closing stock(Column F).

View 2 Replies View Related

Excel 2007 :: Row-wise Conditional Formatting On Color Scale

Jan 14, 2014

I need to color format several matrices of about 1000 rows each in order to find a pattern. Each row needs to be formatted on a color-scale so that the maximum value in each row has the darkest color and the minimum value has the lightest, while others are lighter according to their weight. It can be done on a single row, but it does not work for multiple rows at once. I have tried format copying and range options but it takes the maximum and minimum for the whole range rather than individual rows.

I'm wondering if it is possible to do it for multiple rows while the criteria of maximum and minimum applies to individual rows.

View 2 Replies View Related

Macro To Copy And Paste Data Column Wise From One Sheet To Another After Row Count

Mar 24, 2014

in creating a Macro which copy and paste the data from "Sample2.xlsx" file to "Sample.xlsx" file after row count in each column. Macro should not overwrite any previous data present in workbook.

Note: Sample2 workbook is containing a Variable Columns. Macro should match the headings of the Column in both workbooks and then copy and paste the data after row count.

View 4 Replies View Related


Aug 8, 2008

"Get rid of merged cells!"

You can read it often on this Board.

I will not open any debate on the subject, but just want to provide a solution to get rid of them automatically. Anybody who likes can refer to this thread.

This code is changing layout.

1. Could take a while when you have sheets with large used range or a lot of merged cells. You can follow the status and can interrupt it if you want.

2. Save before executing. I'm quite sure it works good, but the result might be something else then you had in mind, especially when you are quite new to this.

Option Explicit

Sub Unmerge_CenterAcross()
'Erik Van Geit

'merged cells will be unmerged
'contents will be centered across merged area

Dim LR As Long 'Last Row
Dim LC As Integer 'Last Column
Dim i As Long
Dim j As Long

View 9 Replies View Related

Copyrights 2005-15, All rights reserved