I want a summary list of data from all columns of worksheets that contain the target data and only where the value in col D is 1. So, in the attached example, the data to be checked is on Sheet1 and Sheet2 and the result should be shown on Summary sheet. There will be some tabs that don't need to be checked for the data so it's not a case of check all other tabs except for Summary.
View 14 RepliesI have a worksheet with several columns of data that I have to summarize elsewhere on the sheet. Here are the columns:
ID
Status
Joined
Name
Name can take on 4 possible values. For each value in Name, I want to count the number of cells in a column that fit certain criteria. Here are the counts I want to make:
Status OK: Status is a date.
Joined OK: Status is a date and Joined is a "1".
There are going to be about 500 records to count. I've already implemented these counts in a Windows scripting language that interfaces with Excel, but I know there must be a more direct and faster way to do it entirely in Excel, whether with VBA or writing functions into cells.
I have a workbook that we use for our internal customers to use to place orders for equipment. There are five worksheets within that file that outline the different products. The requestor will indicate what items they want by putting a number in the quantity cell that is appropriate for the product. This could be using a combination of all five worksheets for the one request.
What I want to do is to create a summary page that only extracts the information from the other worksheets that has a value marked in the quantity spot only.
I have a requirement where I need to summarize multiple work sheets. And each work sheet as different range. Column names are same in each sheet but number of rows in each are different. Like consider there are 3 sheets with employee details. Each sheet has Employee Name, Employee Number, Employee Location. But in first sheet as 10 employees and second sheet has 20 employees and third as 25. So the requirement is I need to summarize all employees.
View 1 Replies View RelatedI have a problem with excel. I attached the excel sheet to show the question.
excel problem.xlsx
I have a "SUMMARY" sheet and then five other sheets. I need to be able to extract the data which is >0% from each of the individual sheets and capture the results in the relevant section of the "SUMMARY" sheet.
For example, the data from sheet "PAYG" would need to be entered into cells F148:AJ155.
In each instance it is just the "model" detail I need to capture (column D)
I have data on a worksheet (“Interface”) that I need to copy over 7 other tabs for analysis.
("Module_1", "Module_2", "Module_3", "Module_4", "Module_5", "Module_6", "Module_7").
The linking Field on the Interface tab defines what row I need to paste the data to on Module 1-7. The column headers are the same on both the interface and other tabs (“YES, NO, N/A, BLANKS, STANDARDS MET, STANDARDS NOT MET, NOT APPLICABLE”)
The tab is selected based on the row
Module 1Medication Management
Module 2Privacy
Module 3Process
Module 4Patient Safety
Module 5Medical Records
Module 6Infection Control
Module 7Environment of Care
I’m trying to modify code written for a similar purpose with no luck. This is the code I'm trying to modify. I know I'm not on the right track yet.
I have multiple sheets with the same format.(all columns have same format) in column "A" i have a value lets say "B1-1".
What I need to do is to copy from all sheets the rows containing in column "A" the same criteria("B1-1") to a new sheet and skip the rows that have a blank cell in Column "A".
I have a workbook with many sheets of similar but not identical data. I need to extract columns from each sheet based on 5 header criteria and paste them to a single sheet. Each worksheet contains these 5 criteria.
I've been working with the VBA script I've pasted below. It's grabbing the 5 column criteria that I have in sheet 12, and comparing them to sheet 1 in the workbook, then copying them to sheet 12. This much is good, but I need the script to also return the data from the other worksheets as well. I've tried modifying the script based on other loop functions in other scripts I've found, but I'm not having any luck.
Im creating a workbook for my office. The workbook has a sheet for each user. The user chooses a channel from the drop down list, then complete the next 5 fields (must add to 100). I have a totals sheet where I want to summarise the data from the whole workbook as per the template on the totals sheet. Ive attached my example.
View 3 Replies View RelatedI have the attached spreadsheet that has 122 columns I would like to create several common button at the top left hand side to summarize my information. The columns that I would like to are name, id, partner.
View 2 Replies View RelatedI have three worksheets. The first one has a 20 digit code that is broken into eight sections ( cells) and an amount assign to it. There are currently 150 codes but can grow bigger as new codes are assigned with their own amount. The other two worksheets have the same breakdown for the code but will be filled in as needed. They will record additions or subtractions to the code, like a register. I would like to then summarize (on a fourth worksheet) the +/- activity from worksheet 2 and 3. To do this I will copy the first worksheet then setup three additional columns one will be Increase the other Decrease and the last one Balance. What I will need to make sure that I only pick up the amount for the right code. So I need to create a formula that makes sure that it compares wks 2, cell A1 to wks 4, cell A1 AND wks 2 cell B1 to wks 4, cell B1 AND wks 2, cell C1 to wks 4, cell C1 and so fourth until the eighth breakdown of the code. When this is true then I will need to add the amount that was recorded on wk2 or wk3 for that code on wk4. The codes may be used various times so the sum of the values of wk2 or wks3 will need to be cumulative.
I've been reading about Sumproduct and I have created this formula but the result is # NUM....
I have a monthly template that has 31 tabs labeled 1 through 31 for the days of the month. I would like to add a macro that would print only the sheets that have a value greater than 0 in either of two specific cells.
Example:
Sheet / Cell B2 / Cell B36 / Action
1 / 0.00 / 0.00 / Don't print
2 / 100.00 / 0.00 / Print
3 / 0.00 / 100.00 / Print
4 / 100.00 / 100.00 / Print
I could add a summary sheet that would pull over these values and/or add if statements that return results like "don't print" or "print" if it would be easier in the macro or I could just use the code in macro to determine to print or not.
I am a record and tweak macro user and this isn't something you can record. I found some simple code in another post that would print one page based upon single criteria but it isn't exactly what I was looking for.
I need to assign to variable the number of cells in axle "Y", I explain them, I have assigned to the variable "Celda" the number "4" that, celda = 4 where 4 is the number of cells where begin the information that i need to copy,at firts they were separated sheets, and the macro it was working, assigning a letter a the processed row in the column of the left, but now is a one sheet, and I need, this macro find the last cells that the contain a letter in the left and assign the number of cells to the variable "celda" dont the range for example "A33" else "33" and begin toward down, I have this
Workbooks(origen).Activate
Application.ScreenUpdating = False
celda = 4 'where the number 4 will be changed for the number of cells in axle "Y" in this case "33"
celda2 = 4
cdestino = 1
While Workbooks(origen).Worksheets(1).Cells(celda, 2).Value <> ""
dato = Cells(celda, 2).Value
Select Case dato
Case "Aplicación de material"............
how to Transfer data between worksheets using multiple criteria?
View 1 Replies View RelatedI have multiple worksheets to add employee details using a form and create worksheets (copy templateSheet and paste) for each employee using their uniq user id, which works fine. I have 12 worksheets named by month (Jan, Feb, Mar.... Dec). I have only 2 worksheets Jan and Feb for test.
I want data from employee worksheet's (worksheets created by employee user id) to be in 'Jan, Feb... Dec' worksheets to use a criteria (userid, worksheet name) to get monthly holiday booked data from employee worksheet (userid and worksheets name are same) to month name worksheet. I can do it manually (Example: =rahmanm!C3
) for each row and column, but I will also have a function to delete employee name and details. Is there anyway in VBA I can update worksheets (Jan, Feb, Mar... Dec) from employee worksheets (rahmanm, hallD, aldridh.. ).
I would like one column (column A) to be an "indentifier" column. Column B is the customer name, column C is the customer city and state, column D is the customer phone number, and column E is the customer email.
By calling column A an "identifier" column, what I mean is that I would like to be able to go down the list and put an "x" next to each customer I would like to include in that particular workbook. I would then like to be able to run a macro which would take each of the customers with an "x" in the column A, create a copy of the template comfirmation call sheet, and fill in the identifying information from columns B through E into the copied template. Each worksheet would also need to be renamed the same as the customer name. It would need to perform this same procedure for each row that has an "x," making sure that all worksheets (confirmation call sheets) are grouped into one new workbook.
Sheet 1 is called "main"
Sheet 1 contains 5 columns
A = Blank
B = Customer Name
C = Customer City and State
D = Customer Phone
E = Customer email
Sheet 2 is called "template"
"x"'s are placed into "main" sheet, column A to identify which customer will be affected by macro...................
Spreadsheet contains 15 worksheets, all named differently, "apple", "pear" "plum" etc etc. Worksheet has data columns A to E including in column E a "Next Review Date".
On worksheet 16, called "Crumble" .... I want to click a macro that will cut and paste in all the data from the 15 fruit worksheets where the date is
I need a summary page in a workbook that will pull data from multiple workseets with mulriple criteria.
I have treid sumproduct, but that doesn't seem to be working when I select a range from multiple workseets.
Here is what i want to do. # I have a workbook say 'Template', which has two columns: ColA -> Names , ColB -> Value, his acts as a template, i.e a budget. # Another workbook 'sample' which has similar ColA(may contain additional rows) and corresponding value in ColB.
Here is what i need::
I need to copy the contents of ColA and ColB of 'sample' wholse ColB valules are greater than ColB values of 'Template' for corresponding ColA values in both, into a new workbook. SO basically, template acts as a budget check for the sample workbook, and if any value in ColB goes above the budget in template, it will show up in the new workbook. I have attached both the workbooks. Basically the red highlighted cells in Sample are the ones i want in a new workbook.
I maintain the data flow at my work. We send and receive the data using excel files with specific formatting that I then upload to the database. Each time I send or receive the excel file I must log them, this is what my code question refers to.
I use RDBMerge to merge all the contents of the 100 plus excel files into one worksheet. The first part of the macro cleans up the merge data for use in the log (i have attached an example of the clean data and finished log).
The blue shaded area of the "Raw_Data" is what the clean data looks like, the yellow column is what current macro records for each record.
As you can see by the example the Raw_Data is only two files LL_LLL_BOB_ToLLLLL_20121228_01 & LL_LLL_BOB_ToLLLLL_20121230_01, each with more that one record.
The log code in column "H" Is based on this criteria:
First Letter of the Unique ID in column "E" - O, M, or L
Program Type in Column "F" - U or R
1. O-U = U
2. O-R = RU
3. M-U = U2
4. M-R = R2U
5. L-R = R
You will note that Columns G-R of the "Log Sheet" correspond to the "Record Type" found in Column "G" of the "Raw_Data" sheet.
This is the area where my skill at using scripting dictionaries fails.
The results for the log list each file only once, but the log code for each corresponding "Record Type" in columns G-R of the "Log Sheet" must contain each unique instance of the code. In other words
if LL_LLL_BOB_ToLLLLL_20121228_01 contains an O-U with an "A" Record Type and an M-R with an "A" Record Type; then, on the log sheet there needs to be the codes "U/R2U" in the cell intersection of the LL_LLL_BOB_ToLLLLL_20121228_01 record row and "A" column (which is column "G")
So, If the File contains one of each code for each Record Type the corresponding cell must house one of each code separated by a "/" without any spaces. This means the cell value could no code, or one code and all the variations in between to all five codes. Also, for ease of human reading the log codes should be concatenated in the 1-5 order that I listed them in (U/RU/U2/R2U/R)
Here is my code so far.
VB:
Option Explicit
Sub test()
Dim dic As Object, a, i As Long, rng As Range, e, w, n As Long
Set dic = CreateObject("Scripting.Dictionary")
[Code].....
I have a directory which contains many files, they are all names based on their locations. eg. Burwood-File1.xls,Burwood-File2.xls,Burwood-File3.xls etc
I have a master that which will contain the branch in the first column.
I have defined the directory location in a separate sheet as well as email template.
When I click on the Send Email button I want ti to attach the files that match the Branch name.
The Branch list will constantly grow.
Im struggling to find a formula to populate cells based on values to be checked in two fields (ICODE, COMPANY) as shown below.
In the example here, all records where ever the ICODE = 49, i need to populate the fields(columns) AD1,AD2,AD3 or AD4 with the product name of the same company where the ICODE <> 49. There could be between 1-7 products per company.
CURRENT SHEET
ICODE PRODUCT COMPANY AD1 AD2 AD3 AD4
89PENS ABC STATIONERY CO LLC [code]....
The attached example file may be a better alternative to view this problem.
Worksheet 1: In column A I have a people's initials. In coumn B I have text boxes with miscellaneous text. (The same person could have multiple rows within this sheet.)
e.g.
AAA blue
BBB orange
AAA round
CCC smelly
AAA elongated
Worksheet 2 I want to show:
A2 = initials, B2 = first text box associated with that person, C2 = second text box (different row) associated with that person (if applicable), D2 = third text box (different row) associated with that person (if applicable), etc.
e.g.
AAA blue round elongated
BBB orange
CCC smelly
I'm starting a dashboard, where on the front page I have two combo boxes on the left, and three empty fields to the right. I'd like the three fields to the right to auto-populate table-based values depending on the chosen criteria from BOTH fields (by store and month/date). I've attached a sample of what I've got so far. I've only provided three tables for this example, and I have a table with the same column/row titles for each metric and I have three different metrics I'd like to auto populate: COGs, Sales, and GM% or in the example, metric 1, metric 2, metric 3. No pattern in the table values, just wanted to populate the fields quickly. All fields are organized by store/month-date and I've set up a link to my combo boxes on a calculations tab.
View 2 Replies View RelatedI track distribution using 1 workbook for each sales rep. Each workbook has a separate worksheet (scorecard) for each of their locations showing which of our products are used in that location, for various functions. Each of the locations has a segment (bar, nightclub, casual dining, etc.), and a class (AA, A, B, etc.). All of the scorecards are identical, and are in the same file folder on our shared drive.
Sample:
Location A Class:AA Segment: Sports Bar Rep: John Smith
Product: Product X Product Y Product Z
Well: 1 0 0
Back Bar: 0 1 0
Cocktail Menu: 0 1 1
I was unable to find an HTML maker so this is not an actual sample but what I'm looking at. The above data indicates that for location A (Sports Bar, Class AA), Product X is used as the well, Product Y is on their back bar and their cocktail menu, and Product Z is not used at this location at all.
I need to set up an analytic workbook where I can sum data based on multiple and grouped criteria, and for multiple reps ie- display a scorecard that shows totals for nightclubs and sports bars, class AA, A, and B, for a particular rep; OR display a scorecard that shows totals for nightclubs, class A for a group of reps. Ideally I would like to do this using drop down menus that allow the user to select multiple criteria in each of the drop downs, having the data auto-populate based on those selections.
I have an excel spreadsheet with various worksheets, each worksheet is named different according to tests that must be performed. Each test is different and inputed by rows, there is one column from each test in which we populate "passed", "failed", "pending", "N/A", or "user issue".
The problem is searching for all the "failed", and "user issue's" throughout all the tabs. I want to create a tab which will identify and display all the "failed", and "user issues" on one tab, and sort it according to its tabbed test name. Now, not to be picky, I would like to copy only a few cells along with the failed message, if not, the entire row would be fine. Could anyone assist? to sum it up, I want to create a sheet that'll identify all the issues existing throughout tabs.
I've got a matrix combining actions on the left hand side (Col A) and owners on Cols B to F. I have drop down list for each action/owner combo: N/A, OK, KO, TBD.
Owner #1
Owner #2
Owner #3
Owner #4
Owner #5
Results
Action #1
OK
TBD
N/A
N/A
N/A
OK
[code]....
I would like to create a formula in Col G that does the following:If any of the values in columns B to F equals OK, then OKIf any of the values in columns B to F equals KO, then KO I've been tinkering with multiple IFs and quite rightly got bogged down as too many criteria to take into account...
So, another question with regards to this sales sheet that i am setting up. I want to be able to take the information below and for each of the people listed (i.e. by their initials) i would like to...
1.) Take their associated % (Columns C, E, G)
2.) Multiply the % by the Revenue (Column H)
3.) Sum up the total for each of the names (Locatedin Columns B, D, F)
4.) Only sum up the ones that occur in Month 1 of the Year 2012
I have tried multiple ways with a Sumproduct but none of them have worked. I would love to be able to do this with a single formula without having to add another column, for instance, to track what MonthNum it is. I tried using some kind of offset, but i could never get it to work.. For instance to look through the range, find the initial go over 1 column to the right and multiply that % by the revenue if the Month is 1..
Date (A)Rec1Name (B)Rec1Pct (C)Rec2Name (D)Rec2Pct (E)Rec3Name (F)Rec3Pct (G)Revenue (H)1/1/12AD25.00%RU25.00%BF25.00%1000.001/1/12AD25.00%RU25.00%PNF25.00%2000.001/1/12BF50.00%00.00%00.00%1200.002/1/12BF50.00%00.00%00.00%1000.002/1/12BF50.00%00.00%00.00%1000.003/1/12AD25.00%RU25.00%00.00%1000.003/1/12AD25.00%RU25.00%00.00%1500.004/1/12AD16.67%RU16.67%BF16.67%1500.004/1/12BF50.00%00.00%00.00%1500.00
I would like a macro that select all cells based on multiple criteria. It's a big sheet, but I've made it smaller in the following example.
In row 1, there are 2 headers, in B1 (electricity) and C1 (gas) In column A, there are 3 houses, in A2, A6 and A10. The blocks of cells in between (e.g. B2:B5) have 2 headers, 'period' in B2, and 'account number' in B4. B3 and B5 have a list of options in them.
I want to select cells that meet certain criteria. Either electricity or gas, and either period or account number. So, if in cell D1 I choose electricity and in D2 period, I would like to select B3, B7 and B11. If I choose gas and account number, I wish to select C5, C9 and C13.