Excel 2007 :: Sum Data Based On Row And Column Variables
Sep 25, 2012
I'm working on a workbook that will combine a cash flow report by category and also by date. The categories can change (which I think is the easiest part) but so also can the frequency of data. For instance, you choose how you want to see the data, monthly, quarterly, annually and this will change the Row that the data should be summarized based off of.
Examples:
1) Annual: Sheet 1: Row = Rental Revenue and Column = 2001. I want to pull from sheet 2 all of the Rows that are mapped to Rental Revenue and all of the Columns that are mapped to 2001.
2) Quarterly: same idea but by quarter.
3) Monthly: No issue, just a SUMIF.
In Sheet 2 I have added a mapping at the top of each column for the Quarter, Year or Month that should be included in the Sheet 1 heading Row.
I tried using a sumproduct on both row and column using the whole data set underlying, but this did not work.
"=SUMPRODUCT(1*('Sheet 2'!$A$9:$A$140='Sheet 1'!$C28)*('Sheet 2'!$E$9:$GA$9='Sheet 1'!E$27)*('Sheet 2'!$E$10:$GA$140))",
It gives me a #N/A result.
I have tried to attach a table of what I am trying to do.
Sheet 1
Choose Frequency
1
Annual
[Code]....
View 4 Replies
ADVERTISEMENT
Feb 23, 2012
Excel 2007. I have an Excel file that contains a data dump from an external database file with numerous analytical sheets that perform calculations. Some of these calculations utilize the SUMIFs function that was introduced in Excel 2007. This function does an outstanding job of summing a column on the data sheet based on multiple criteria.
However, someone high up in management in my organization would like to "drill down" into the data behind the
SUMIFs formulas to get a quick snapshot of the lines in the database that roll into the
SUMIFs formula. =SUMIFS(DataBase!E:E,Data!A:A,C7,DataBase!B:B,D7,DataBase!C:C,E7,DataBase!D:D,F7)
If I double click on a cell with the formula above, Excel takes me to the Database tab and selects Column E which is close, but not exactly what I need. What I really need is for Excel to only show the rows on the database sheet that make up the total in the SUMIFs formula and not the entire data dump from the database.
At present, we have to manually apply the autofilter on multiple columns to show the rows in column E that make up the total in the SUMIFs formula which is a tedious and time consuming task. Is there a way to force Excel to do this? Suggested custom database application or pivot tables, but we do not want to reinvent the wheel.
View 2 Replies
View Related
Oct 5, 2011
I have a workbook that has a sheet called CustInvData, this sheet contains 4,421 rows of invoice transaction data for 178 customers starting on row 2 (headers on row 1). I need to split the transaction data for each customer out into a workbook template based on the customer name in column A. I need each workbook named by the customer name along with a month and year (example: Bellsouth-0911.xls), this should create 178 unique workbooks. And since we sometimes have to go back and rerun invoices for previous months, I'll need to control the month and year manually in the code.
The parsed data needs to be copied to a pre-formatted invoice template. This template has 2 sheets, Sheet1 is called 'Product Summary', this is a table that uses VLOOKUP functions to read the data in Sheet2 called 'Product Details', this is the sheet the parsed data needs to be copied to for each customer invoice. The 'Product Details' sheet has formatted rows 1 thru 11, row 11 being the header row for the data from CustInvData to be copied. So the parsed data needs to start at row 12.
Last, once the data has been copied into the 'Product Details' sheet, I need the data to be SubTotaled at each change in column J (Product) and use the 'Sum' function to add a Subtotal in column L (Retail Price) for each unique product category.
Example data below, I've simplified it (the actual data array spans from columns A to Y)
Customer NameProduct Retail Price
ABC CompanyAVMPCR10
ABC CompanyAVMPCA15
[Code]....
I'm a bit of a novice with macros, but I know Excel pretty well.
Using Excel 2007 running on Windows Vista
View 2 Replies
View Related
Nov 27, 2011
Autofill column B with sequential values based on whether value in column A changes its value.
I would like to autofill column 'B' with sequential values (i.e. GenoMap1, Genomap2, Genomap3,... GenoMap10, GenoMap11, GenoMap12,... GenoMap104, GenoMap105, etc...), but changing to the sequential GenoMap# only when the value in column A changes.
This is what I imagine.
A1 "Alfiero", B1 "GenoMap1"
A2 "Alfiero", B2 "GenoMap1"
A3 "Alfiero", B3 "GenoMap1"
A4 "Allocati", B4 "GenoMap2"
A5 "Amaranto", B5 "GenoMap3"
A6 "Amaranto", B6 "GenoMap3"
A7 "Amaranto", B7 "GenoMap3"
A8 "Ambrosiano", B8 "GenoMap4"
A9 "Ambrosiano", B9 "GenoMap4"
A10 "Ambrosiano", B10 "GenoMap4"
A11 "Ambrosiano", B11 "GenoMap4"
I listed examples above of GenoMaps higher than 10 and 100 to show how I need them numbered.
I'm using MS Excel 2007 in Windows 7.
View 2 Replies
View Related
Sep 7, 2012
Excel 2007.
I'm basically copying and pasting a bunch of columns. Currently, my code is very long because I'm not using a loop to plug in the column header. how I'd store all the column headers in an array (I think) and loop through 1-by-1.
Here's a look at what I'm doing now:
Code:
' ''Project Number
x = "Project number"
i = Sheets("RawData").Rows(FirstRow).Find(x).Column
[Code]....
how to store these column headers in an array and then pull them?
View 4 Replies
View Related
Dec 22, 2011
Using Excel 2007, I'm trying to figure out (and not succeeding!) on how to declare worksheet and range - in order to:
input a formula to cell xfd1, then copy down to xfd2:xfd100 - and here's what I've been playing about with
Code:
Sub filldownxfd()
Dim src As Range, out As Range, wks As Worksheet
Dim sRangeName As String
Workbooks.Item(1).Sheets.Item ("Sheet1")
Dim example As Range
Set example = Range("xfd2:xfd100")
[code]....
View 2 Replies
View Related
Jun 1, 2011
What I am looking to do is this... (using Excel 2007)
I need to have more dependent lists based on the selection in Column A. (My column B is already set up and works perfectly using INDIRECT)
For example:
Column F would be a dependent list of colors based on the sales rep chosen in Column A
Column I would be a dependent list of managers based on the sales rep chosen in Column A
Column M would be a dependent list of part numbers based on the rep chosen in column A
without using VBA? (If I HAD to - okay, the other person maintaining the sheet would be lost....) I haven't been able to find anything close to what I want to do online - maybe I am just not using the correct terms.
View 4 Replies
View Related
Feb 28, 2014
I am looking for VBA code to extract data from each tab into master tab based on data.
I have 3 tabs (inputs)
Tab1 (Dept A-NAME) (RANGE B4:I7)
SL NO
ID
Date
Customer
Start Time
End Time
Trucks
Supervisor
1
A
2/25/2014
Customer 1
10
1
3
ABC
[Code] .....
Tab 2 (Dept B-NAME) (RANGE B4:I11)
SL NO
ID
Date
Customer
Start Time
End Time
Trucks
Supervisor
1
B
2/25/2014
Customer 3
10
1
3
RTY
[Code] .......
Tab 3 (Dept C-NAME) (RANGE B4:I7)
Is it possible to run vba code to get below result in new tab
SL NO
ID
Date
Customer
Start Time
End Time
Trucks
Supervisor
Result
1
A
2/25/2014
Customer 1
10
1
3
ABC
Dept A
[Code] ......
Condition here is date 2/25/2014, is possible when running code message box pops up to ask date, when we give conditional date it extracts those dates.
Excel -2007 & Windows 7
View 7 Replies
View Related
Oct 12, 2011
Is it possible to take the information below and make one list out of columns F, H and J based on the common information in column C? For example, group all the 18s in a list, followed by the 44s.this is excel 2007
column CColumn Fcolumn Hcolumn J
Option code
18180L12369301/123/54
18180L12369301/123/54
18180L12369301/123/54[code].....
View 2 Replies
View Related
Feb 17, 2012
I have a workbook (excel07) that I use to import data onto our purpose built database. I have some macros to sort the data and send it to the correct columns however the one stage I still have to do manually is assign each row its own unique sorting Code (in a bespoke column "B2:B999").
These are objectives and the code should contain three seperate parts (part 1a and b do not need to be seperated);
1a. The characters "PO" should be the first in the code (for sorting purposes)
1b. The persons initials (first letter of the first name and last name) found in column J2:J999. [They are sorted by name so It would also be good to add a number after the initials seperated by a space incase of multiple objectives however I could defintly live with typing these in manually]
e.g. "Joe Blogs" second objective = JB02
2. The numerical part of the stategic objective they have selected found in column H2:H999. [incase you need this these range from 1.1-1.4, 2.1-2.5,3.1-3.6 and 4.1-4.5 all with a short text strings after which shouldnt be included in the code]
e.g. "1.1 We are Committed to being nice" = 1.1
3. The year they are applicable for which will all be "12/13" however I will need to change this next March.
Therefore the final code should appear as "POJB02 3.1 12/13"
There is a 16 Character limit on these codes but all of that information is needed for sorting, filtering and report generation, the spaces could be replaced by "_" if that makes things easier.
View 5 Replies
View Related
Mar 28, 2014
I'm trying to populate a table based on two different criteria in a another table. On is based on a date and the other the number of a unit. I'm trying to get the data in sheets Week 1 and Week 2 into the format in sheet Example. Is there a way to create a series of functions, filters, etc that I could use to create a macro to do multiple sheets or at least a whole sheet at once? I've been trying to think through it with my limited knowledge of filters and functions
Here is the file with an example and I'm using Excel 2007: Example.xls
View 2 Replies
View Related
Jul 26, 2012
I have some data in a Excel 2007 workbook in the range a1:d100 and I want to sort the data according to "A" column, which is the first column of the range and when I am giving data sort it is prompting to select the column, where as in 2003 it will prompt to select the column but by default it will take the first column of the range, so if we want the first column the we can hit enter key otherwise need to change the column name which will save time.
My question is that whether there is any setting I can make at the time of sorting data and by default it will take the first column of the range.
View 1 Replies
View Related
May 24, 2012
In the spreadsheet shown below I would like the user to select a project via a dropdown list in cell B2 which is from the data set shown in row 7 downwards. Then based on the project they select, they need to be able to see all of the locations associated with that project and choose in B3. Finally they then need to be able to select a team which is associated with the project & location combination chosen in cells B2 and B3.
Excel 2007
ABC2
Project3Location4Team56ProjectLocationTeam7Project 1PerthTeam 18Project 1MelbourneTeam 29
Project 1SydneyTeam 110Project 1Brisbane Team 111Project 1Brisbane Team 312Project 1DarwinTeam 413
Project 1DarwinTeam 514Project 2PerthTeam 315Project 2PerthTeam 416Project 2MelbourneTeam 117
Project 2MelbourneTeam 218Project 2SydneyTeam 419Project 2Brisbane Team 6
View 9 Replies
View Related
Feb 26, 2014
I need to transpose data from Column A to Row 2 and down.
The data in column A is in sections of 19 rows and then a blank cell and another 19 rows of data contimuously, It is a dynamic range and can contain many thousands of Rows.
The data needs to be transposed from Column A to row 2 (row 1 has the head line for each column) so the 19 lines of data is now spread accross 19 columns in row 2 and the next section from column A is spread accross the 19 columns in row 3 and so on.
My data looks similar to the below. (Test Number 0001 starts in A1)
Test Number 0001
21-Feb-2014
Kettel
Office
Demo
[code]....
I use Excel 2007
View 6 Replies
View Related
Jun 6, 2014
I am using excel 2007. I have data which, instead of being posted on multiple columns, is all within 1 column. The data most simply has the characteristics of:
AAA
AAA - description
BBB
BBB - description
CCC
CCC - description
and so on..
I would like to move the descriptions from column A to column B:
AAA AAA - description
BBB BBB - description
CCC CCC - description
and so on...
What would be the best way to accomplish this?
View 4 Replies
View Related
Apr 30, 2010
I am trying to create a formula which will look at the data in columns A - I (50 rows) and copy this data to columns K - S but ignoring any blank cells. Thereby consolidating the data in the upper rows with no spaces.
View 10 Replies
View Related
Feb 19, 2014
I have a situation that I cannot get my hands on. I have a set of Project Numbers in my Column A. (Will Try to post an example).
I need that everytime I change a number in my Column B that it will change all the numbers, in my column B, based on the matching Text in my Column A.
Demo1.xlsm
View 14 Replies
View Related
Dec 9, 2009
A B C D
COL 0
COL 0 1
COL 0
COL 0 1
COL 0
EDM 0
I need to add the number of 0's in column B if Column A is COL and C OR D is 1. correct function yields 3 for this example. I've played with =sumproduct(if)) and =sumproduct(if(and))) etc... can't figure it out.
View 9 Replies
View Related
Jun 28, 2007
I have data in three columns. The first column contains the machine name (Robot 3, Robot 4, and Robot 5), the second column contains the paint color (1-20), and the third column contains the data that I need to sum. For example the data could be paint purges, the number of purges from each robot. Each row of data will contain a number of purges for each robot, for each color. The data is provided by day, so I will be summing the data weekly.
View 8 Replies
View Related
Dec 28, 2012
In my worksheet, I know the last line of my report AND I know how many "reporting segments" (all equidistant and identical) there are in the report based on the last row using the below (which I tested in several reports in messages):
VB:
Dim vListEnd As Variant
Dim vTaskCount As Double
vListEnd = ActiveSheet.Range("A1").Offset(Sheet1.Rows.Count - 1, 0).End(xlUp).Row
vTaskCount = ((vListEnd - 5) / 22)
My "reporting segments" are ALWAYS 22 rows each and the first one always starts in row 5. Example: A document where the last row (containing an End of Report special character) is 247 would have 11 reporting segments.
Within each reporting segment, there are two different formulas needed.
For the first reporting segment located in rows A5 thru J27:
I need FORMULA1 in all cells of the range (D5:D15 dragged out to H5:H10(not 15) and D18) and FORMULA2 in cells (F20:F22,J20:J22).
Then I need to repeat that vTaskCount number of times (number of segments) and at intervals of 22 (segment repeater offset). Some reports may have 2 segments, some might have 50, but the structure is always identical.
My formulas are all pretty simple if it makes a difference (originally using FormulaR1C1 = "=RC[-1]+RC[-3]" --- but because I now know solid cell locations I suppose I don't need to make them relative, too.)
View 2 Replies
View Related
Jul 19, 2009
I am trying to select a range based on two variables which store the column numbers. what I have is:
View 4 Replies
View Related
Oct 19, 2006
I have a project that looks at a table of train schedules for a range of dates. The data is formated so that arrivals and departures are on the same row but differnent columns. The dates are to the right and the grid is completed with the number of arrivals for a paticular day. I have created a formula that looks at the data and returns the arrival time for that date. It worked fine until the schedule was altered so that the same train could arrive at 2 different times on 2 different dates. So train 976 arrives at 4a on some days and 5a on others. In the example Ihave posted train 976 has 2 passengers arriving at 5a but the formula shows 4a. Is there a way I can have the formula return the correct time based on the day of the month? I should point out that the same problem could exist for departures.
View 8 Replies
View Related
Dec 2, 2013
I am having a small problem with microsoft excel 2007.the problem is that: my excel 2007 cannot display tooltip of the column width when I keep and drag left mouse
Show tooltip of the column width as attached file.
I also tried many ways to find settings of advance menu. But cannot.
Pic.jpg
View 2 Replies
View Related
Feb 6, 2009
Another VBA problem I am having to need help with...
Andy Pope and DonkeyOte graciously helped me with the first stage here:
http://www.excelforum.com/excel-prog...-a-time-2.html
where I was trying to loop through 1000 records at a time and retrieve data from my Oracle database based on a single Field ID.
This is the code that ended up working...
View 14 Replies
View Related
Aug 26, 2008
I was trying to use the VBA code to generate a data table:
Private Sub createDataTable(WS As Worksheet, initialRow As Integer, numCol As Integer, numRows As Integer)
Dim initialCell As Range 'specify the upper left cell
Dim RefCell As Range 'the reference cell on the caculator sheet
'activate source sheet
ActiveWorkbook.Sheets("Calculator").Activate
Set RefCell = ActiveSheet.Cells(2, 3)
WS.Activate
Set initialCell = ActiveSheet.Cells(initialRow, 1)
initialCell.offset(numRows, numCol).Select
Selection.Table ColumnInput:=RefCell
'the calculation should be automatic, if not, then calculate
WS.Calculate
End Sub
and it kept giving me an input celll not valid error on this line: Selection.Table ColumnInput:=RefCell the thing is, I have defined RefCell as a range object, so it should be OK rite?
View 4 Replies
View Related
Jan 8, 2014
I need to remove the duplicates under column B for each item under column A and I can't seem to figure out how to do it.
I'm using Excel 2007; I would prefer a VBA solution, as I have to do this on a monthly basis. However, if a formula is the best way to go, then that's just fine. Either way, I'm preparing the data for a pivot table. I am unable to post the actual document in the interest of data protection.
Essentially, I'd like to turn this-
_ A B
1 Paris Bill
2 Paris Bill
3 Paris Mike
4 Paris Derek
5 Paris Derek
6 London Mike
7 London Bill
8 London Mike
9 London Bill
10 London Derek
11 Tokyo Derek
12 Tokyo Derek
13 Tokyo Derek
into this-
_ A B
1 Paris Bill
2 Paris Mike
3 Paris Derek
4 London Mike
5 London Bill
6 London Derek
7 Tokyo Derek
In other words, each city should have only one instance of any name that accompanies it; not all names will accompany each city.
View 8 Replies
View Related
Mar 20, 2008
For what I'd like to do, the attached workbook actually represents 3 worksheet. one input sheet and two output sheets (output1 and output2)
I plan to use command button " CommandButton1" on input sheet to start the calculations
Here's what I want to do after pressing the " CommandButton1" :
1. start with case 1 and find the value of Input A to Input J from input table using Vlookup and paste those values to respective palceholder for inputs in blue colour area above the input table for each input.
2.for empty cell in the input table paste 0 (zero)
3. after this will give results for case 1 inputs on sheets "output1" and "output2" on basis of formulas used for each output
4.then copy the results for case 1 inputs from sheet "output1" and "output2" and paste them on "input sheet " in Output table (its below Input Table) next to case1
5.Do this for all cases (in actual workbook there will be more than 100 cases)
View 9 Replies
View Related
Oct 28, 2009
I have a spreadsheet where raw data is entered ("RAW_DATA") for each month it is collected. There are 7 questions that are tallied and each of those responses are transfered to a separate data table for analysis (7 individual tabs within the spreadsheet). I'm currently manually data entering the values from the raw data tab to the other seven tabs.
I would like to have VBA code that could transfer the data from one month ("RAW_DATA") onto the other 7 tabs based on the date and facility identified in the raw data tab. I have not attempted the VBA since I do not know where to start with searching on two variables.
View 2 Replies
View Related
Mar 23, 2012
I have been tasked with doing a work tracker so we can see what work operatives have done between a date range. The processing of the date range is being done in VBA.
The tracking info comes from an external table that is linked into Excel (version 2007). This table is also filtered.
The problem I have is that there are 3 different tasks that the operative can be doing (each has a different time on how long it should take to complete the task, however that is irrelevant for what I need).
This results in multiple entries in the table (min of 1, max of 3) for each operative. There is a seperate column in the table as well which has the total number of each task that they have done.
So for example the table looks similar to this.
Clock No - Task - Sum of task
239 ------- A -------- 5
239 ------- B ------- 10
239 ------- C ------- 15
What I want to be able to do is to get the sum total for the sum of tasks for the operative, so in the above example for operative 239 I want a value of 30
Is there a formula that can do this? (it is a filtered table so the formula needs to count only visible items)
I have tried playing around with
=SUMPRODUCT(SUBTOTAL(3,OFFSET($A:$A,ROW($A:$A)-MIN(ROW($A:$A)),,1)), --($A:$A=K414315))
but I cant get what I need.
View 9 Replies
View Related
Feb 24, 2011
I have a question regarding filtering of columns. I have set up Excel 2007 so that I can click on the drop-down menu in each column to filter them.
However, in my data set there is one blank row separating two set of rows. Now, when I apply the filter on a column, all the rows above the blank row filter correctly, but all the rows below stay un-filtered.
I guess Excel only looks at the consecutive rows, then stops when it hits a blank row. Is there any way of applying the filter beyond a blank row (i.e. the whole column)?
View 4 Replies
View Related
