Dynamic Report By Criteria

Mar 26, 2009

Right to the point. I got two sheets in my file, one named "Order" and one named "Input". In the input sheet there is costs & revenues divided into several different divisions/activities. In column A I got the name of the cost/revenue and then the value for every period Jan-Dec in columns B-K. And the name of the activity is in column A under the cost/revenus for that activity, like a " SUM" row.
Now, in the the "order" sheet, you can select a specific activity from a rolllist and then I want that activity's revenues & cost to be shown. I've attached the file to make it easier for you guys to understand my needs. I think I need some sort of 2 criteria VLOOKUP, that is first search för the correct division, once this is found search for the different costs & revenues and the return a value. But I'm not so good at this so I can't get it to work. Maybe the INDEX function is good here but I don't get that at all. I tried Daves "2 criteria Vlookup" but all I get is #ERROR.

View 4 Replies


ADVERTISEMENT

Creating A Dynamic Report Tab

Mar 12, 2009

I am trying to generate a good looking, dynamic report tab that runs off another tab that contains ugly raw data for website traffic. This should then drive a couple of charts based on the parameters I specify.

The Raw Data

This is daily data for keywords that we advertise on through Google. Associated with each keyword are various fields:Clicks
Cost
CPC (Cost divided by clicks)
Quotes
Sales
CPQ (cost divided by quotes)
CPS (cost divided by sales)
CTQ (quotes divided by clicks)
QTS (sales divided by quotes
CTS (sales divided by clicks)
Avg Pos

There are maybe 30 keywords - each with daily information for the above fields.

The layout of the raw data is:

Column A: Date
Column B: Keywords
Column C: Clicks

and so on.......

The Report

This is the nice looking report where we can pull in the required bits and bobs in a nice format. Because some of the above fields are calculations I've not been able to achieve what I need using a normal pivot report.

What I would like to be able to do:

Populate a summary report using the above raw data but be able to specify certain things such as:The date range I want to see data for
The specific keyword(s) I want to see data for

For example v- we are feeding the daily information into the raw report each morning. One day I might want to see summary stats for a particular data rang on maybe 3 of the keywords so I'd somehow like to select appropriate date ranges and keywords from drop down lists and then I see the summary stats for the appropriate date range broken down by each keyword.

I'd also like a total underneath this report to add up or calculate the totals and averages for the data based on the parameters I specify.

Finally, given all of the above, I would like to run a dynamic chart based on the raw data but taking into account the filters I specify on the slick report sheet.

So....if I set the date range as From: 01/03/09 to 12/03/09 I can see daily clicks, costs etc for the particular keywords I specify in the drop-down menu or what-have-you

View 14 Replies View Related

Need To Build Up One Dynamic Report

May 28, 2009

I need to build up one dynamic report.

Here is the task:

1) Everyday workers fill up with records in each sheet (record count can be different, because there is a "List" table)

2) Each day is in one separate sheet (for example: 1st June is in sheet with name 1, 2nd June is in sheet with name 2 .)

3) ALL sheets are named 1, 2, 3 ... 31

4) And in the sheet with name "ALL records" must be generated all records from sheet 1,2, ... 31

Here is my Example

View 9 Replies View Related

Summary Report Of Dynamic Table Data

Nov 10, 2008

I am interested in finding vba code that I can enter as an add-on for a weekly training report that I receive.

An example of the weekly report is attached. A Computer based training program populates the reporting tool with the date that the course was completed. Each week a report is generated as attached with the dates completed filled in the matrix.

The goal is to: Report the total percentage of training completed. I would like to be able to run a vba add-in that will determine what rows have entries. Perform a countA on the date fields. Sum the counta's and populate a cell with the % complete. In one simple touch of a button or keyboard function that can be ran each week without editing the code. These reports are filtered by department and the size of the matrix change all the time.

The hang up I am having is making the code dymanic enough to figure out what cells are filled and calculate and populate by that factor.

View 9 Replies View Related

Subtotal Rows & Summary Report Of Dynamic Table

Dec 1, 2008

writing a VBA to convert a set of data in sheet 1 to one in sheet 2(I am enclosing that as a Excel document"Test -Original").I have described what needs to be done ( step by step ) below.

Develop a macro
1. I have a report from SAP BW, the original format of which is in sheet 1.I need to develop a macro using VBA and need the report with the format in sheet 2.

2. I need a “Results “row after every Bill to Party in column A as below(screen shot 1.doc):

The number of customers is dynamic i.e.it keeps changing every month

4. Nothing needs to be done to column.SAP BW will not overwrite the format and the data in column G.Hence leave it as it is.

5. Calculate the number of Sales document numbers for each customer and put the value of 1 for every value. If it is blank it should not be counted and put the value of 0 for those rows. (Shown below) Display the sum of the number of sales document numbers in the results row for column H

6. In the column I, put the value of 1 if the difference column (column G) is 0 and put the value of 0 if the value in the difference column (column G) is any value apart from 0.now sum the value in the results row for each customer and display the summation value in the results row under column I

7. Compute the percentage which is the values in (column I/Column J)*100 .This should be done only for the results row

View 9 Replies View Related

Report Of Data By Criteria

Jan 18, 2008

I have a large list of items (up to 3000) that are listed by category, description, part number and weight in columns C-F. Column A is where a user will enter a quantity in the rows of the items that they want. Once the user is done picking their items they hit a button that I have written a macro in, to copy and paste only the rows that contain a quantity in, on to another sheet. My problem is that for these items (that contain a quantity) I need to have a line number (in column B) that starts at 1 and counts up for each line item. Issues to consider:

-Line item 1 may not always be the top item. (because the user may not choose the top item)
-There will be spaces inbetween the line items since the user may not pick all of their chosen items in order.
-Users can pick an item at the bottom then pick one above it. (The Line item numbers will need to readjust to include the new number in the middle)

This has been bugging for a few days now cause whenever I think I finally get it I keep creating circular references

View 2 Replies View Related

Multiple Criteria Report

Jan 29, 2008

I have a 7-column 'Task' list that I need to query, and extract (preferably to a separate sheet) only those tasks that start 'ON' or 'BEFORE' the queried Date (or date range), AND/OR end 'ON' or 'AFTER' that date.

Column 1 = Dates
Columns 2-4 = Task IDs (ID1, ID2, ID3 - must match as a group)
Column 5 = indicates either Start or End of Task ('S' or 'E')
(each Task has 2 such listings - a Start [s] and an End [E])
columns 6 & 7 = Misc. & Notes (unimportant as identifiers)

So, if a Task (identified collectively by col. 2-4) starts On or Before and ends On or After (col 5) the date (col 1) queried, then that Task should be included in the results list. The tricky thing is that a task that starts long before the queried date and/or ends long after the queried date needs to be included in the results list - therefore 'S' and 'E' (Start/End, col 5) and the 3 Task ID (col. 2-4) must be used along with the date for the query. And, if a task starts (or ends) on the queried date, then its counterpart (S/E) should also be included in the results list (if available).


Tasks List:

Date | Task-ID1 | Task-ID2 | Task-ID3 | S/E | Misc. | Notes

Jan 1 2008 | AA | def | XX | S | B-11 | notes
Jan 1 2008 | FF | xyz | ZZ | S | C-44 | notes
Jan 2 2008 | DD | def | YY | E | J-55 | notes
Jan 2 2008 | GG | abc | CC | S | C-22 | notes
Jan 2 2008 | BB | xyz | DD | S | M-33 | notes
Jan 3 2008 | AA | xyz | CC | S | S-77 | notes
Jan 3 2008 | BB | def | ZZ | E | A-99 | notes
Jan 4 2008 | GG | abc | CC | E | C-22 | notes
Jan 5 2008 | AA | def | XX | E | B-11 | notes
Jan 6 2008 | BB | xyz | FF | S | J-55 | notes
Jan 6 2008 | DD | abc | AA | S | A-99 | notes.............

View 9 Replies View Related

Report By Date Criteria

Feb 1, 2008

I am trying out a new spreadsheet to log my cycling. I have attached the example so far. I am going to have a form appear and fill it out. Upon clicking 'Add' button I want a macro that will search column B (a list of dates) and match the date the users filled out on the form with the spreadsheet and then paste the corresponding form data in that row.

How would I accomplish this? Does the formatting of the date field complicate this since on the spreadsheet I display "Saturday 1/08" and on the form the user enters in "01/08/2008"

View 9 Replies View Related

Pull Value From Report Based On Two Criteria

May 31, 2014

I am trying to pull a value from a report based on two criteria. I want to get the most recent date that has a value in Col K

1
Col F
Col G
Col H
Col I
Col J
Col K

2
106
ET
Product A
14-May-2014

[Code] ........

I use

{=IF($K$2:$K$60000>0,(MAX(IF($F2:$F$60000=C2,$I$2:$I$60000))))}

On another sheet (I left the sheet names out of the formula to make it easier to read) with C2 as the matching Col F value and it will return the most recent date, May 19th, not the most recent date with a value in Col K, May 18th. The report itself is thousand of lines long and has a couple hundred unique values for Col F.

View 3 Replies View Related

Creating Report Based On Criteria

Jun 20, 2006

I have the following worksheet (see attached). What I would like to do with it to create a report is as follows:

1. Create separate reports for each estimator (KM, JW, WH)
2. Carry over the information in Columns I, A, B & H
3. Each report will filter column I to leave out COMPLETED and just report the number of days remaining is ascending order.
4. Flag data if the days remaining is <=5
5. Generate an email to send out notification of 5 days remaining
6. New report can be generated on any given day

View 9 Replies View Related

Statical Report Of Table By Criteria

Feb 28, 2008

I have a 5 column LIST that will have new rows added daily. Column A= Date (mm/dd/yy), B=id (s or u), C= ArriveTime (hh:mm), D=DepartTime (hh:mm). E is Delay time (D-C). Arrival and Departure times will vary from 5 AM - 9 PM but will always be on the same day. I would like a count of all s with delay of 0-6 min, 7-12 min, 13-18 min, 19-24 min and 25+ minutes. Also count all u with same delays. I should get 10 results (1 of each) using the following data. Would also like the mean, median, mode, min & max for all C (Arrival), D (Depart) and E (Delay) times.

1/1/08 s 08:00 08:01 1
1/1/08 s 08:00 08:07 7
1/1/08 s 08:00 08:13 13
1/1/08 s 08:00 08:19 19
1/1/08 s 08:00 08:25 25
1/1/08 u 08:30 08:31 1
1/1/08 u 08:30 08:37 7
1/1/08 u 08:30 08:43 13
1/1/08 u 08:30 08:49 19
1/1/08 u 08:30 08:55 25

View 2 Replies View Related

Rearrange Some Cells On Report Based Of Some Criteria Using IF Formula?

Sep 13, 2013

So I am trying to rearrange some cells on a report based of some criteria using an IF formula and getting Blank cells. Here's what I have:

LastRow = Range("A" & Rows.Count).End(xlUp)
With Range("P2:P" & LastRow)
Formula = "=IF($F2=""/FEDERAL EXCISE TAX"",$I2,"""")"
.Value = .Value

[Code].....

So I just want it to identify whats in a cell and populate a related value if the text is present. Do I need to change formatting to find Tex or something along those lines?

View 2 Replies View Related

DSUM Formula With Varying Criteria Across Report Matrix

Sep 21, 2006

I currently have a report with so many large array formulas that it is virtually unusable. I want to use the DSUM formula in place of my array formulas but I am vexed in regards to creating a DSUM formula that I can put in the top-left cell of my report and then copy down to the bottom right side of my report. Currently, the array-formulas sum data from a large list when it meets two criteria -- one part of the data-record in the list must match the row label of the current row in the report and another part of the data-record in the list must match the column label in the current column of the report. I have found with the DSUM formula that you can use a "formula" criteria in place of a static criteria...the problem I am having is that the part of the formula that points to the data-set must be relative while the part that points to the formulas criteria needs to be static -- in order to be able to make one DSUM formula and copy it down and over I would need my formula criteria to have the opposite setup (with the data-set part static and the formula criteria as relative).

View 9 Replies View Related

Formula To Report Back Cells That Meet Multiple Criteria?

Feb 16, 2014

I've got a forecast from a customer and need to summarize it with part number, quantity and date.

The spreadsheet is part no in column a due dates in row 1 values at the intersection of part no and due date and i don't want 0 quantity to report back.

my output needs to be partno, date due, quantity.

View 4 Replies View Related

Create Report Table From Two Original Tables (source And Select Criteria)

Mar 31, 2014

I have two tables (Table 1 and Table 2). Table 1 has names of all employees in my organization ('Name' column) and corresponding information in the second column ('Textinfo'). I need to create Table 3 from table 1, but only select those employees who are in my team, ie matching names in the Table 2. I also need to extract corresponding 'Textingo' column information. I tried vlookup but it did not work because if there more that one name in the table 1, vlookup confuses it (i think). Would you know what function or code to use?

Sample file is attached : excel question 1.xlsx

View 2 Replies View Related

Creating Individual Report Cards (worksheets) Based On Template For Report Card

Aug 30, 2013

I have two worksheets in my report cards:

1) Data - the students are listed in Column A, math scores in Column B, reading scores in Column C and science scores in Column D. The grades of 300 students are entered in this sheet.

Student
Math
Reading
Science

Jimmy
75
84
100

[code].....

2) Report Card template - This is the report card that needs to be generated for each student. It's pulling the student name and grades from the Data worksheet.

Student Name
=Data!$A2

Math
=Data!$B2

Reading
=Data!$C2

Science
=Data!$D2

How do I create worksheets (report cards) for additional students? I have 300 students in the school. I need the next worksheet to reference Data!$A3. I know how to cut and paste the report card template and then edit =Data!A2 to be =Data!A3 to create a report card for Sally. How do I create the 300 report cards I need?

I've been teaching for 13 years. I can create a report card for each student in my class and edit each worksheet individually for each student. Now I've been asked to do this for the whole school and I don't know how to create the other 299 sheets I need.

View 1 Replies View Related

SUMIF Criteria Across Dynamic Range?

Mar 3, 2014

I'm looking for a way to write this:

[Code].....

as this :

[Code] .....

Where the criteria runs across a range (G to BG) that can sometimes have columns deleted, but the range must remain fixed.

View 3 Replies View Related

Search Dynamic Columns With Criteria

May 12, 2014

Trying to create a dynamic vba code that searches the second last active column for a particular criteria. In this case it is the value "901".

If the criteria is met it will search the row to see if value is >=0 then change to zero where applicable.

In the example, I have a loop which searches Column 6 for the matching criteria. I want to change from Column 6 to "second last column" which is Dynamic to allow for more column to search through.

The two codes below search through column "A" and Column "B". Is there anyway to make this Dynamic also so it searches from Column "A" to the 4th Last Active Column.

So the Column to check for Matching Criteria is "Second Last Column" The Data to Loop through will be from Column A to the "Fourth Last Active Column".

Public Sub ChangeCellValue_TwoConditions_ColA()
Dim Last_Row As Long
Dim iLoop As Long
Last_Row = ActiveSheet.Range("A65536").End(xlUp).Row + 1
For iLoop = Last_Row To 1 Step -1
If (trim(ActiveSheet.Cells(iLoop, 1).Value) >= 0) And (trim(ActiveSheet.Cells(iLoop, 6).Value) = "901") Then
ActiveSheet.Cells(iLoop, 1).Value = "0"

[code].....

View 3 Replies View Related

Multiple Criteria Sum With Dynamic Ranges

Dec 11, 2007

I have a sum part of which is $I$4:$I$500.

The 500 part of the sum is constantly being manually changed using edit replace because of a growing amount of data.

In cell A1 ona another worksheet within the workbook I use the Count function to count the number of rows containing data. This cell is named DataCols

Is it possible to combine $I$ with the named Range DataCols?

I have got this far - ="$I$"&INDIRECT("ColNo") which returns 500 if I remove "&I$"& in front of it.

View 6 Replies View Related

Copy Data From Weekly Report To Monthly Report

Jul 2, 2008

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 Related

Build Dynamic Named Range From Criteria

Jan 14, 2013

I have some raw data, and in that data are 2 columns. They are Manager and Role. I am trying to create 2 lists, that correspond to their role. I can create a dynamic named range from the column no problem using this formula:

Formula:

[Code] .....

Now, what I need to be able to do is, add a criteria option to the formula. So build the list off of column B, but only if their role (in column C) is Perm, or Temp.

See the attached spreadsheet : dynamic range criteria.xlsx‎

View 8 Replies View Related

Dynamic Columns - Filter Based On Criteria?

Feb 19, 2013

I have a 6000+ row database with 5 columns across. The 5 columns have the following headings: Hair Color, Eye Color, Age, Location, and Salary.

I have created macros that will filter each column based on criteria - for example:

[Code].....

As you can see, at cell J10 and K10, I list the filter that is used for a particular search. Some searches, will only have 2 search criteria, some may have all 5, etc.

On a separate worksheet (in the workbook), I have a "Report Tab", my question (after a long build up) is how do I dynamically change the columns and values on the "Report" tab depending on the search that I perform? For example, if I did a two variable filter (Hair and Salary), those would be the only two columns on the report - if I did all five variables for the search, all columns would be on the report, and so on.

View 1 Replies View Related

Dynamic Lookup Based On Specific Criteria?

Dec 21, 2011

I am getting an error i have list (sheet4) i want to lookup the data from different table (Name range) based on the location in column A i am using the small function copy down, in cell C6 i want the range ROWS($A$2:B6) go back to ROWS($A$2:B2) since the criteria in column A has changed from China (A5) to US (A6)

Sheet4ABC1Locationproductsamount2ChinaA2954.63ChinaB1565.284ChinaC3772.055ChinaD2027.76US#REF!#REF!

Array FormulasCellFormulaB2 =INDEX(INDIRECT(A2),SMALL(ROW(INDIRECT("2:10"))-ROW($A$2)+2,ROWS($A$2:A2)),1)C2 =INDEX(INDIRECT(A2),SMALL(ROW(INDIRECT("2:10"))-ROW($A$2)+2,ROWS($A$2:B2)),2)B3

[Code].....

View 1 Replies View Related

Concatenate Cells Based On Dynamic Criteria

Jul 22, 2014

I am looking to write a formula that can concatenate cell A$1$ (absolute) & B1: B245 (dynamic)and then A$2$- B1:B245 and then so on.

So for example, I need to concatenate cell A$1$ & B1, and then A$1$ & B2, all the way up until B245, and then I need to restart the sequence with A2 and B1.

Formula to preform this concatenation, or will this need to be done with VBA?

View 1 Replies View Related

Advance Filter With Dynamic Criteria & Range

Dec 4, 2008

I bring in data with these same columns A thru J. I do not know how many rows will be comming in. I have create a macro that uses the advanced filter. I have a criteria range set up on sheet 2 with 6 variables, the main data is on sheet 1. This works fine if I use all 6 variables. I wish I could select from a list box which of the 6 or all variables I need(I might select 2,3 or more variables), & have that information populate the criteria range. I only filter in place(no copying), there is only filtering on column B, the label for the criteria range will always be "Name" it will be located on sheet 2 D:1. Is this possible or should I be trying some other function? Here is the macro that uses all 6 criteria. I have searched and could not find what I was looking for.

'add criteria range
Sheets("Sheet2").Select
Range("D1").Select
ActiveCell.FormulaR1C1 = "Name"
Range("D2").Select
ActiveCell.FormulaR1C1 = "e11*"
Range("D3").Select
ActiveCell.FormulaR1C1 = "e12*"
Range("D4").Select.........

View 9 Replies View Related

Advanced Filter Dynamic Rows Of Criteria

Mar 27, 2007

attached is a spreadsheet effectively replicating a criteria box I’m using to do an advanced filter on a large amount of data (5000+ lines). The criteria can be anywhere from 1 to 7 different lines. What I’m trying to achieve is code that will look at the specified criteria box, determine how many rows of criteria actually exist, and then use that code to perform the advanced filter function on the data.

Below is the code I’ve put together so far. The problem is that this code can only determine that there are 7 total rows of criteria to use, and not the exact number of rows of criteria. For instance, say I only wanted to use 2 rows of criteria, I’m hoping the macro would only use those 2 rows instead of picking up all 7 rows.

See criteria box on spreadsheet for example. In this particular example, I’m wanting the macro to only use B49:I51 as criteria. The ideal solution would be for the macro to look at the criteria box and determine the last row used that is not filled with “1”s. Does anybody have any thoughts on ways to tweak my code to get it to achieve this?

Sub RunDynamicSelection_Click()
Dim wsSheetDS As Worksheet
Dim wsSheetRS
Set wsSheetDS = Worksheets("DataSheet")
Set wsSheetRS = Worksheets("ReportSelection")
With wsSheetDS
.AutoFilterMode = False
With wsSheetDS. Range(("A4:N4"), wsSheetDS.UsedRange.Rows(Worksheets("DataSheet") _
.UsedRange.Rows.Count)).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _....................

View 2 Replies View Related

Report A List Line By Line Based On Name Criteria

Jun 21, 2008

Currently I am using Excel 2000. I did a search and couldn't seem to find the answer I was looking for. Hopefully someone can help. It would be greatly appreciated since I have been trying to figure out how to do this for days.

I am trying to create a worksheet that will pull info from a row on one sheet to another sheet based on the name; then continue to list the info on each line afterward until there is no more of the that particular criteria. See below (this is just an example to simplify the data but would need the same process):

This would be the data on sheet 1:

Name Bonus Commision

Tom $45 $50
Mary $25 $75
Mary $30 $80
Tom $60 $50
Tom $90 $25

What I would like to do is have 2 more sheets. One would be Tom's sheet and the other would be Mary's sheet and it would look like this:

Tom's Sheet:

Tom $45 $50
Tom $60 $50
Tom $90 $25

Mary's Sheet:

Mary $25 $75
Mary $30 $80

I am not completely familiar with all of the functions in Excel and compared to you guys I am a complete novice.

Is this possible to do in Excel without using a macro?

View 9 Replies View Related

Formula To Retrieve Data With Multiple Criteria And Dynamic With Dropdown List

Jan 6, 2014

I have a complicated situation with Excel since I need a formula that can SUM data from the Pull 1 worksheet into the summary Sheet. I attached my sample for your reference because it is a bit complicated therefore I can not explain. It is better if you look at my Summary Sheet, need formula that can retrieve the same information.

Formula Test.xlsx

View 3 Replies View Related

Excel 2007 :: Create Dynamic And Unique Sublist Based On Multiple Criteria

Aug 26, 2012

Excel 2007, Windows 7.

Most details are shown in the spreadsheet below. I would like it to be dynamic because the quarterly and annual data dumps I'm working with are are hundreds to thousands of lines.

Have the list be sorted, which is a part of the first attempt, would be nice but is not necessary. At this point, just being able to generate the dynamic list would be fantastic.

Excel 2007ABCDE1product lines:consist of these product subtypes2Widget series:Widget.type1Widget.type23Fidget series:Fidget.type4Gidget series:Gidget.type1Gidget.type2Gidget.type356data dump of parts sold or used in repairs7product subtype repairedpart number

[code]......

View 5 Replies View Related

Report Structure (create A Report Either By Using Or Without Using VBA)

Apr 12, 2009

find the attached Example file. I need to create a report either by using or without using VBA.

View 3 Replies View Related







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