# Search Sheets And Create Sums Based On Criteria

Jan 23, 2012

I have a workbook with many, many worksheets. On each worksheet there is a column called "Cost Code". I need a Macro to locate all like "Cost Code" values and extract data from several cells along that row. Macro would then tally the values from all those like cells on numerous worksheets and place sum on a seperate worksheet in the same workbook, on a "Totals" worksheet page.

For instance -The "Cost Code" value Macro to look for is 1000. This value is located on several worksheets. For every instance of "Cost Code" 1000 Macro is to find value of "Material Costs" in that row (always column M) and value of "Labor Hours" in that row (always column P). Macro would tally all "Material Costs" for "Cost Code" 1000 and put that sum on a "Totals" worksheet page in a specified cell. Macro would do the same for all "Labor Hours" for "Cost Code" 1000.

## How To Create Sums Of Columns Based On Date And Category Criteria

Nov 1, 2011

I have spent a good amount of time trying to create the below using botched IF functions, SUMIFS, SUMIF formulas etc.

Basically I have a sheet (called Sheet1) with the following columns:
A - Date (by individual day DD/MM/YYYY)
B - A catagory (so using the old example, Apples, Orange, Pears)
C - Price.

I would like to be able to create a formula, whereby I can sum the Total sales for each catagory across each month onto a summary sheet (sheet2).

I would use pivot tables, however am not sure how i can make all the require data appear permanently in the best way, especially if there is a one liner that can be done more easily. So far I have managed to create a sum dependent on month, but have been unable to encorporate the catagory.

## Create Userform To Search Using Multiple Criteria

Jul 31, 2014

I have created a userform which searches from the data using the value that i put in text box.

E.g. if i put student i.d in textbox1 all other textbox show me the data like name contact detail etc.

But I want one more criteria. i want to add one more textbox to search for e.g either i put student i.d or locker no whereas right now i can only put student ID.

## Search Multiple Sheets For Criteria In Col A?

Oct 30, 2013

I have thought about recording a vlookup but this might be messy and prone to errors. Is there anyway to search through multiple sheets for a value (code) in Col A sheet 1. And return X in sheet 1 in the next column.

## Hide Rows Across All Sheets Where Row Sums To Zero

Jan 23, 2008

Is there a way to append this code to accomidate accross all sheets?

Sub HideRows()
On Error Resume Next
With Range("e4:e34")
.EntireRow.Hidden = False
For i = 1 To .Rows.Count
If WorksheetFunction. Sum(.Rows(i)) = 0 Then
.Rows(i).EntireRow.Hidden = True
End If
Next i
End With
End Sub

## Create A Formula Which Sums The Values Associated With Several Defined Names

Aug 6, 2007

I am looking to create a formula which sums the values associated with several defined names. For example, I have a workbook with the following defined names SalesPerson1Total, SalesPerson2Total, etc. and these amounts are all sourced from multiple tabs. The amount of defined names (i.e. 'SalesPersons') is variable, therefore, I want the formula to read Sum the values of all defined names which are named with the following convention 'SalesPerson(X)Total'.

## Conditional Sums Using Multiple Criteria

Feb 2, 2009

I've been struggling to figure out why my Conditional Sum formulas using multiple criteria keep reporting zero. I am tracking resource loads for a project which is divided into 4 parts (Iterations). I am trying to sum the total resource allocations per Iteration. see attachment!

## Calculate Sums, Multiple Criteria

Sep 2, 2007

I have a worksheet of hockey player statistics that I need to tally the Career Totals and the Totals for One Specific Team. Since each player has played a different amount of years in both their career and by team I cannot use a simple Sum formula as each player will have a different amount of rows to calculate and the rows may not be congruous.

The statistics also use text "--" to specify when a player did not play and "0" for when they did play but no statistics were registered. When the totals are calculated, I need to know whether they were registered or simply didn't play (i.e retaining the "--" in the totals instead of "0" if they did not play in those specific years). One example is listed, while the rest need filling in... attached is a partial sample of the data

## Search For Value Based On Two Criteria - VBA

Mar 12, 2012

I am trying to find the best way to give a result based out of two criterias. My original list is 1000+ row long, so I am expecting that my vba code will be refering to a worksheet with this list on it.

The list will look like this :

Group SubgroupName1aaaname11bbbname21cccname32cccname42dddname42dddname5

I will always have the group and subgroup available and I want to get the name.

So I would like to write a function or a macro that would look for the specific group and subgroup and return me the name.

## Select Based On 2 Search Criteria

May 20, 2009

I have an Excel Sheet with three columns of data. In a separate worksheet, I am setting up some calculations. To do so, I am having the user select from a drop down list, the data in the first and second columns. Based on the two values selected by the user, I would like to bring forward the value in the third column. Can someone please help me out with the formula to do so?

## Search Based On Multiple Criteria

Aug 29, 2013

An example of my data is:

Column A Column B Column C

I need a formula that checks both column A for text (ABC) and column B for text (JKL, two different criteria) and give 'Answer 3'. There will a lot of data and it will not always be the bottom answer.

## Match 2 Criteria And Search Column And Return Value Based On Value

Feb 14, 2014

I have a spreadsheet that contains data for a fiscal year broken out by month and quarter. I want the formula to return the data from that month and use whatever the latest quarter is. For example in the data below:

If it has only pulled data only thru Q1 it would return Q1 for Jan-Mar, however once I have Q2 data I would want the formula to return Q2 for the months of Jan-Mar.

2013Thru Q1STOLISTOLIJan-2013STOLI BASE
2013Thru Q1STOLISTOLIFeb-2013STOLI BASE
2013Thru Q1STOLISTOLIMar-2013STOLI BASE
2013Thru Q2STOLISTOLIApr-2013STOLI BASE
2013Thru Q2STOLISTOLIMay-2013STOLI BASE
2013Thru Q2STOLISTOLIJun-2013STOLI BASE
2013Thru Q3STOLISTOLIJul-2013STOLI BASE
2013Thru Q3STOLISTOLIAug-2013STOLI BASE
2013Thru Q3STOLISTOLISep-2013STOLI BASE
2013Thru Q4STOLISTOLIOct-2013STOLI BASE
2013Thru Q4STOLISTOLINov-2013STOLI BASE
2013Thru Q4STOLISTOLIDec-2013STOLI BASE
2013Thru Q4STOLISTOLIDec-2013STOLI BASE
2014Thru Q1STOLISTOLIJan-2014STOLI BASE

## Create Sheets Based On Lines

Mar 25, 2014

result of command is like below:

Code:
Entity,DATE,A_C,A_N,A_T,A_C,N_C,PER
YYY,24/03/2014,222 , 0002,ALARM TEXT DIFFER IN TIMEFORMAT , 1, 1,3.03030303
YYY,24/03/2014,333 , 0001,SCRIPT FAILED , 31, 6,93.9393939
YYY,24/03/2014,111 , 0003,ALARMS NOT RECEIVED , 1, 1,3.03030303
Entity,Sub-E,DATE,A_C,A_N,A_T,A_C,N_C,PER
YYY,9999992867290,24/03/2014 ,333 , 0001,SCRIPT FAILED , 7, 1, 100
YYY,9999992813525,24/03/2014 ,333 , 0001,SCRIPT FAILED , 3, 1, 100

[code].....

Now I'm looking is when opened in Excel, it should create a two sheets excel file 1) with the First Section and the 2nd with second section . based on headers included in file.which then will be used for PivotCharts..in 2nd stage of automating..

## Trying To Create Calculation Based On Particular Criteria

Jan 29, 2014

I have 2 worksheets with data. One of them is a bench mark template or expected times to complete devices based on batch size and product. The other worksheet is a report generated that shows the actual time techs worked on batches of equipment by product type. I am trying to automate a report that I will need to run every week. I have most of it automated but need some calculations. I want to calculated the expected time to complete the work, based on the data provided in the benchmark worksheet.

I do not want to use an average, and I want to use the expected time of completion based on batch size and product relative to the tech report. Column T is highlighted and where I want this formula to be entered. So if the Total Batch items is 1, then the expected calculation uses that time relative to the product and then multiplies that number to the batch size. Or if the batch size is 3, then it uses the time relative to a batch size of 3 and then multiplies that number by the batch size on the tech report and is entered in the highlighted column.

I have attached the workbook I am working with : Production Report.xlsx‎

## Create New Records Based On Criteria(s)

Feb 28, 2013

I have over 1000 of rows of data and I want a formula or a Macro to create 5 new records (rows) based on the following:

Below is how data in master sheet1 looks like...

STORE#ZIPCODEID #1ID #2ID #3ID #4ID #5
10134567A100B200C300D400E500
10245678A200B300C400D500E600
...
...

With the formula or a macro I want it to look like this in Sheet2 in the same file...

STORE#ZIPCODEID #
10134567A100
10134567B200
10134567C300
10134567D400
10134567E500
10245678A200
10245678B300
10245678C400
10245678D500
10245678E600
. . .
. . .
. . .

As you can see there are 5 records per STORE# listed in Master Sheet1.

## Search Text String And Return Value Based On Multiple Criteria?

Apr 8, 2013

Here is an example of the data I get each day Letter order granting Sabine Pass Liquefaction, LLC's et al 4/16/12 request to add an alternate water source etc under CP11-72.Letter order granting Cameron LNG, LLC?s 4/5/13 filing of a request to introduce natural gas or process fluids into the BOG Liquefaction Project under CP12-15.Letter order accepting NorthWestern Corporation's 8/7/12 submittal of revisions to its transmission planning process to comply with the Commission's June 8, 2012 Order under ER11-2932.Letter order approving Public Service Company of New Mexico's 12/7/12 filing of a joint Offer of Settlement with Navopache Electric Cooperative, Inc under ER11-4534 et al. How can I set up my spreadsheet and what formulas can I use to search and return a value for each text string based on the attached table (column B)?

E3a7qta.png

## Copy Multiple Columns Based On Search Criteria To Another Worksheet

Sep 27, 2011

Rep Name is Column J Row 5. I need to find multiple columns based by the header which is row 5, if column range (j5:az5) is "Video - Actual" then copy column to sheet "Data", this needs to be repeated for about 8 more times for different criteria

Rep NameBundle - TargetBundle - ActualVideo - TargetVideo - ActualJoe, Jane
2.06.06.04.0Smith, John
7.010.02.06.0Tucker, Chris
15.011.06.010.0Iglesias, Gabriel
8.03.06.010.5Smith, Will
8.03.06.06.5Powers, Austin
10.025.07.09.0

## Create A List Of Results Based On Criteria

Sep 21, 2012

Col A has list of order numbers, col D has list of corrisponding order status (open , closed etc etc)

I simply want a formula to display a list of all the 'Open' order numbers in a separate sheet.

Obviously to size of the list will vary depending on how many open orders there are.

## Create List Based On Multiple Criteria?

Apr 13, 2014

I am trying to formulate a commission form which looks up the name of the sales person and then displays a list of the deal names withing Q1.

## Create Unique Table Based On Criteria

Aug 31, 2007

Our reservation system exports a spreasheet daily which contains hundreds of records relating to bookings. The worksheet has an ID column which is not unique, but is sorted alphabetically. I need to write some code to delete all records below the first 'set' of records relating to the first ID value found. i.e. i want to end up with only the set of records relating to the first ID found. So the basic logic would be to look at the value of the field in the ID column of the first record and then iterate down the rows until it came to a different value and then delete all rows below that. Its simple in concept, but im stuck on how to write this.

## Printing Sheets Based On Criteria

Jul 10, 2009

I am looking for a macro that will, upon command, print all sheets (20+) in the workbook with a value greater than \$0.00 in cell I27. Some have a value there, others don't.

## Two Sheets - Lookup Based On Several Criteria

Apr 29, 2013

I have 2 sheets.

Sheet one column A has random dates from 1 jan 2013 to present in order base on fuel purchase dates for our company.

sheet one column F has the purchase price i.e 3.98 gallon

Sheet one column A can have a date entry of lets say 2/4/13 with no data in F because no fuel was purchased just the meter was being read.

Then Sheet one column A can have 2/5/13 where column F has 3.78 where fuel was purchased. (these are 500 gallons tanks that are being refilled as needed)

Sheet 2 row B has just the month formatted as Jan 13, Feb 13, Mar 13, .... you can use B1, B2, B3...ECT for the remaining months

What I am looking for is on sheet 2 in cell C1 a way to look at B1 for the month (i.e Jan 13) look at a range of cells on sheet 1 column A for any dates that fall in the month of Jan, then look for data in corresponding F range and if no data is found then use the last months data (Dec 12)

I can make sure I place a dollor figure in the first row of Jan on the sheet, so by default we can use that if no data exist for Feb.

## Hide Sheets Based On Criteria

Sep 3, 2006

i would like to hide different sheets at one shut. i tried this, but did not work:

Dim sh As sheet
For Each sh(1 3 5 7 9).Visible =xlSheetVeryHidden

## Macro To Create Sheets In New Workbook Based On Template And Evaluator?

Feb 10, 2013

I had created a base data with evaluator and performance appraisals templates which should be sent to each evaluator for filling up the forms based on templates

Next,

Macro that would i require will be

Where in my base data if i select filter of "Arun", i will performance appraisal templates in column I. Next, i have sheets individually created templates which refer column I

So, based on arun filter if i select, it should create a new workbook for arun and six individual sheets of employees reporting to him with a base data and appraisal templates similarly if i select anita in filter , it should create a new workbook for anita and six individual sheets of employees reporting to her with a base data and appraisal templates

## Create And Name New Sheets Based On Text In Cell As Well As Copy Entire Row

Jul 20, 2013

I have attached a file showing what I would need the output to look like. I need to know how to search a range of cells (in this case column E) and if the day of the week is Wednesday to copy that entire row to the Worksheet titled Wednesday. I would do it manually but I have several thousand rows of data to get through, and there has to be a better way.

Example.output.xls

## Automatically Create List Of Column Contents Based On Criteria?

Jan 15, 2014

I have a spreadsheet that lists dates in row 6, columns V through BE. We use these dates to log attendance for kids, so under each date there may be one of many symbols, such as "x" if the student was there. I would like to create a list of the dates that meet one of two criteria: either blank or containing "WR". So, let's say column Z is blank and Y has "WR" in it. In column FB I would like a list of all of the dates that were blank or WR, so I would want the dates contained in row 6 for Y and Z. Is there a function that will 1) look for a blank or WR across a row, 2) find the associated date for that blank in row 6, and 3) write the in another column in the form of a list, each date separated by commas? Here is an example of my information. I would like for a function to automatically make the list in column FB for any dates that are blank or contain "WR".

V
W
X
Y
Z
AA
AB
AC
...
FB

[code]....

List of Dates Absent

x
x
x
WR

x
x
x

1/23, 1/24

[code]....

## Print Multiple Sheets Based On Criteria

Sep 5, 2007

I am trying to find a way to select and print multiple sheets based on a criteria or a list. I have a large worksheet with many sheets. Each sheet falls into one of three categories, and I want to be able to automatically print all tabs in each category. I have all of the sheets rolled up into a summary where I have access to all of the sheet name and print criteria.

## Match Two Columns Across Sheets And Based On Criteria Of Another Column

Aug 10, 2009

This is driving me nuts. What I have is:

Sheet1:
ColumnB - number stored as text (Work ID)
Remaining Columns: - mixed data, some columsn numbers, some text

Sheet 2

ColumnA - number stored as text (Work ID)
ColumnB:ColumnAW - mixed data, some columsn numbers, some text
CollumnAX - Dollar Values

What I want is in Column F (Sheet1) a formula to:

if workID in Sheet1!ColumnB is present in Sheet2!ColumnA AND Sheet2!ColumnAX>1 then return True

## Excel Macro To Create New Workbook Based On Category In List With Respective Sheets

Apr 27, 2014

I need new workbooks based on category name in the below list. Respective sheets should be copied from the existing workbook with items as sheet names. List can be updatable.

category(workbookname)Items(sheets)
Vegetables carrot
vegetables brinjal
meat chicken
meat Fish
meat Mutton
grains Wheat
grains Rice

## Transfer Sums In One Column Based On Date To Another Cell

Dec 24, 2009

Attached is an example of what I am trying to do.

I am looking to transfer the sums for a calendar month in one column to cells in another location for each month.

I put some notes in the sample attached, I appreicate any help getting this to work.