I have a spreedsheet that has 5 worksheets in it named game1 thru game5 i want to count games won and games lost from each worksheet. how do i do that? the games won and games lost in the same cell on each worksheet.
View 9 RepliesI have a large list of data and need to filter unique values of 1 column with a criteria.
For example:
A B
Car 1
Bike 1
Car 2
Car 2
Bike 3
Bike 3
Bike 4
Car 4
I want to filter out only the lines whereby the column A & B is the same comment/value.
Other words: both column A & B have to be unique values.
Based on the list above this means that the list should look like this:
A B
Car 1
Bike 1
Car 2
Bike 3
Bike 4
Car 4
I've attached a very small spreadsheet where a few cells are highlighted.
These cells are where I can't figure out how to perform the appropriate calculations.
None of the cells can ever be moved. They are linked to AutoCad and MS Access.
I've calculated the few I know how to do, but when there are two types of criteria, I don't know how to set up the formulas.
I’m relatively new at using the INDIRECT function, and am having a hard time setting up the syntax for ranges, and even knowing if those ranges will work. I have a workbook with multiple sheets (let’s call them Program sheets) created from a template that contains variable numeric data that I need to sum by creating a formula on a Summary sheet within the same workbook. The criteria for IDing and summing the data from the Program sheets is spread over 3 cells in adjacent columns (let’s call them $E7, $F7 and $J7) on the Program sheets. A string concatenation of these cells will not create a unique string value on any one sheet as there are potentially multiple rows of data on each sheet and across sheets that could have the same value string. The Summary sheet is a report that contains hard-coded values in adjacent cells ($C4, $D4 and $E4) that will match values found in columns E, F and J from the Program sheets.
I’d like to have the formula sum all values within the range P7:AA70 across all the Program sheets when the entries into E, F and J cells (from Program sheets) match $C4, $D4, and $E4 cells on the Summary sheet, keeping in mind that there could be multiple instances of the same values over several rows within the Program sheets (that’s OK, because I want each instance to be part of the sum) Here’s a formula I created for summing values found in a range based on a single matching criteria across sheets. Can this be adapted to the new sum formula I need? =SUMPRODUCT(SUMIF(INDIRECT("'"&Sheet_List&"'!G7:G70"),'By-Month Summary'!$G7,INDIRECT("'"&Sheet_List&"'!P7:P70")))
Sheet_List is a named range on a separate tab that lists the names of the Program sheets that I need to sum from. P7:P70 is the range that the sumable data lies in.
G7:G70 is the range that contains values that need to match the criteria on the By-Month Summary sheet cell G7. For the new formula, I no longer want to sum based on criteria in the G column, but rather on criteria in the multiple columns I outlined in my diatribe above.
I'm Tim, and I'm just an average Excel user who found his way here through Google. I did a little poking around and this looks like a great community. Maybe I can contribute on future visits.
I'm wracking my brains over this one but I can't seem to get anywhere. What is easy on a single worksheet becomes a conceptual nightmare when extended across multiple sheets........
I have an Excel 2007 workbook with ten worksheets, each is 5 columns by *about 50 rows. My column headings are the same on each sheet. All of the cells are formatted as Text.
My workbook contains 30 worksheets with identical layout, named 01 to 30. I'm trying to count the number of sheets in which the value of cell B12 is less than 0.6, using the following formula:
=countif('01:30'!B12,"<0.6")
However, it returns a #VALUE error, although it works fine on a range of cells on the same worksheet. Does Countif not work across multiple sheets? If not, is there another way to achieve the required result?
I extract data from a data base and return the query in details, i need to merge 2 rows of data that has the same project number. example i have project ABC with billable time of 10hrs @rate of £200 and unbill time of 6hrs @rate of £0. The query will split the data as there is a difference in the rate.
I need a formula that recognise that it is the same project and merge the row together!
i am having some difficulty modifing some existing code which is this: It will count all entries from G14 to G217 if *Ost* is in E14:E217
View 3 Replies View RelatedThere are 2 sheets, the 'Open Cases by Resolver' sheet is where I am trying to gather all the relevant information from the 'Open Cases - STATS' sheet. I need to be able to group the tickets in ages, ie tickets open Inc<30, Inc 31 to 60, Inc 61 to 90, Inc >90. The spreadsheet will help explain it in better detail. I thought a ' CountIf(And' formula might work (am trying to keep it formula based) but am having trouble recognising the team name then counting the ageing tickets.
View 3 Replies View RelatedI have 2 nearly identical workbooks and I need to update historical data from the old workbook into the newer one.
My current Coding Snippets that I want to use look like the following:
Code:
Sub UpdateWorkbook()
Dim ws As Worksheet
Dim r1 As String
Dim r2 As String
Dim r3 As String
Dim r4 As String
Dim r5 As String
Dim r6 As String
[code]....
Now, this code isn't working I suspect because the Copy and PasteSpecial Functions don't work the way I wish to.
Instead of just counting all worksheets I want to count the number of worksheets between 2 control worksheets (Start and End). Reason for this is that I have a Workbook that grows weekly and each new worksheet is inserted after "Start". I have a macro that lists the Worksheet names but it grabs all and I only want those between Start and End.
View 9 Replies View RelatedI'm currently doing a survey using an excel workbook that contains multiple questions across multiple worksheets using radio buttons linked to certain cells.
I have around 400 workbooks coming back to me, so what i want to do is take specific values from across many worksheets within each workbook and combine them into a large master table in a seperate workbook.
I've tried using VBA, but not being very proficient at it i've hit a brick wall with that, so i'm hoping that there is an easier way to do it than what i'm currently pursuing.
I have a transactional data set with a line for each transaction and I am looking to count the number of documents (each contains multiple transactions) against criteria.....
It looks something like this.....
Column A Column B
Document No Category
11000001 A
11000002 B
11000003 B
11000002 A
11000001 A
Is there anyway to do this without subtotalling for each document and then a count?
I have multiple sheets in my workbook, I need t o be able to copy M10:M1016 from each sheet and then paste that information into a sheet called comparison.
Sheet1 would be pasted into D10:D1016 , Sheet2 would be pasted into E10:E1016 sheet3 into F:10:F1016 etc.
I have been trying to figure this out for some time and cannot seem to
get it to work. I am using Excel 2003
I need to know the number of times a certain situation occurs across
12 worksheets.
Workbook has 12 sheets named Jan, Feb, Mar, etc to Dec
All 12 sheets exactly the same (different data)
In column F12 to F62 is a range of numbers like 1010, 1056, 1168. All
are four digits and there may or may not be repetitions of the
numbers. The numbers could be any number from 1000 to 1200 (total of
200 possible numbers)
In column G12 to G62 there are 5 different names. Bill, Bob, Mike,
Helen and Sally that may or may not be repeated or not entered.
On a separate sheet I need to figure out the following.
In rows I want to put all the numbers.
In columns I want to put the names.
What I want to do is to count how many times Bill gave away item #
1056, 1137 etc. So that in the column below Bill beside each number
it will give me a total of instances Bill gave that item away in 12
months or a 0 if none of any number. It would be nice if it auto
updated when Bill gives away item # 1423 next month.
I want to do the same for all five people.
I have visited many web sites and tried numerous formulas but just
cannot get anything that works.
I have a Main worksheet, 5 Operational worksheets and 1 worksheet containing data tables.
I want to in the Main Worksheet count, as a total, the number of times a word eg "Aviation" appears in the same range for each of the 5 Operational worksheets. For example,
=countif!'Aviation','team1',a6:a50,'team2',a6:a50,'team3,a6:a50,'team4',a6:a50,'team5',a6:a50
I have an Excel Spreadsheet (2007) that contains over 500,000 records that shows Electric meter usage per month over a 24 month period per meter. What I want to be able to do is to select a meter row per number and copy is to it's own worksheet. At the end - I want to be able to have a seperate worksheet per electric meter number - that I can create a graph. If I go through all 9000 meters and copy and paste into a different worksheet - it will take me weeks to do manually. How can I do this automatically?
View 1 Replies View RelatedI need a macro that would consolidate all data in multiple worksheets of multiple workbooks in one Master file.
All the workbooks will be in one particular folder. The macro should search for data in all the workbooks and consolidate it in one master excel workbook.
I am currently using both excel 2007 and excel 2010. This macro would really reduce manual work as currently consolidating data from 45 to 50 sheets takes an ample amount of time...
I have attached a test workbook excel 2010 (ignore ref# errors, I've cut the workbook down for uploading purposes) What I would like to do is have a 'Button' on my 'information Sheet' which when clicked would clear certain cells. I have searched the forum but can't find a solution, everyone seems to want to delete rows or columns but I just want to clear certain cells. The workbook will have 11 sheets each named 'caravan 1' through to 'caravan 11' The uploaded test workbook only only has 3 sheets.
On 'caravan 1' (which is slightly different to the other 10) I want to clear the content of cells
B4 & B5
C4, C22 & C41
D4 & D5
E4,E5, E22,E23,E41 &E 42
On all other 'Caravan sheets' I want to clear the contents of cells
D4 & D5
E4, E5, E22, E23, E41 & E42
It would be icing on the cake if it could give a warning such as " are you sure you want to clear these cells" but that isn't really necessary. The worksheets will be password protected, but the cells mentioned above will not be. If it proves too difficult to clear all the cells on all the sheets with one click, then perhaps a simpler solution might be to have a button on each sheet instead
How can I count the number of selected worksheets in VBA? I've been looking in the Excel object model, but with no avail. Perhaps I'm overlooking something simple.
View 2 Replies View RelatedI am trying to figure out how to count all open workbooks, then count all worksheets within the workbooks and come up with the total amount of worksheets in a message box. I know that it sounds very simple and probably is, but I really can't figure it out.
View 6 Replies View RelatedI'm making a Excel list for trading cards (MTG) and I've divided it into different worksheets in order to sort it by the color of the cards (not really important I guess).
The issue I have is that I want it to copy certain columns (in this case C3:C1000, F3:F1000, G3:1000 ) from almost all of the worksheets into a new worksheet where it should paste them into column A, B and C.
In two of the worksheets it's also different (C3:C1000 , G3:G:1000, H3:H1000).
I've seen many where you copy one range from multiple worksheets, and that would've been okay I guess if not those two worksheets were different. I'd prefer not to change them, and I'd also prefer if I could've gotten just the information I needed also. Is this possible to do?
I'm trying to email multiple worksheets from a list to multiple recipients in a list, but in one file per person. In a sheet called "Email list", for example, I have a list of 50 worksheet names (e.g. one for each cost centre) in column A (with a heading in cell A1, if that's OK, so the list starts in A2) and in columns B-F up to 5 recipients for each worksheet (e.g. 3 recipients might be 2 supervisors and their manager).
Also, each manager and supervisor might also receive several sheets.
Although columns B-F are presently free-format (they can be anyone's email address, regardless of whether they're a supervisor or manager), it might be easier to split these columns so B,C,D are supervisors emails and E,F are managers, if that makes it any easier creating the collated file of worksheets..?
Unfortunately the recipients might change occasionally so they can not be hard keyed into the macro itself (which would be much easier), but need to be in a table so it's easier for the user to update and maintain - in the past, I've managed to hard key the requirement into a macro which I've maintained (by recording and editing the macro), but I don't know the VB to figure this new request.
Ideally, I am trying to create a macro that can collate the various worksheets each person will receive and send the selected sheets in one file to that recipient, rather than multiple files within one email and/or multiple emails.
I have a function that counts my worksheets and I reference that to a cell so another macro that creates a new worksheet will be named +1 numerically. My problem is I need it to anchor on "MISLC" and count to the end of the workbook. Or start on the last sheet and count back to "MISLC"
VB:
Function Sheets()
Sheets = Worksheets("MISLC").Index - 1
End Function
I have an excel workbook with 8 worksheets. Each worksheet has vertical columns (approx 250 columns per sheet) of numeric data. Is there a function or macro that will combine all of this data into one vertical column without having to individually cut and paste each one into the new column?
View 3 Replies View RelatedI would like to append multiple workbooks with multiple worksheets in a separate workbook. For eg. I have workbook "A" with sheets 1,2,3 and workbook "B" with sheets 4,5,6. Now I want to append "A" and "B" to create workbook "C" with sheets 1,2,3,4,5,6.
I have a folder - U:30000 - that contains a number of identically layed out workbooks. What I'm trying to do is, in each, change a number of cell values. Where dominicb's solution to the previous problem didn't work for me, however, is that I need to change cells in multiple worksheets. The cells are specifically:
On worksheet 'Construction': C3 (which is a date), D3 (which is a text value); on worksheet 'FF&E': D3 (the same text value as D3 on 'Construction'. Unfortunately, whoever initially set up the workbook didn't have the foresight to link it!)
Code to merge worksheets from different workbooks stored in different location.
I have a sheet called "Master" in all the workbook I want to combine.
I have a unique password for all the workbook as well.
All the workbooks are stored in different folder location.
I would like to do a paste special values when the consolidation takes place.
I'm trying to combine data from several worksheets (one sheet per workbook) into a single, consolidated master worksheet for reporting purposes (filters and pivot tables). We do not need to keep formulas for the master worksheet, only values and formats. Individual worksheets are used by different users to capture case data in a Human Services field. Column headings are identical, but rows contain data on individual cases. I'm trying to find a relatively easy way to combine multiple worksheets into a single master. After I establish the worksheets and technique, it will be operated by extremely basic users so I've been reluctant to use extensive macros.
Because of complex reporting needs, the exact combination of worksheets being combined for reporting may vary. For example, one time I may combine Tom, Dick and Harry, another time Tom, Dick and Bob, and yet another time Tom, Dick, Bob and Harry. Obviously, one method is to cut and paste the rows into a single worksheet. Are there more elegant solutions that could easily be handled by very basic users? Worksheets are stored in a single folder along with a separate worksheet used for validation rules (as you can guess, this would ideally be a database application but for various economic and political reasons we are using Excel). One possibility, if straightforward, is to use Access to consolidate data then export it back to Excel for analysis. I've scoured the various threads but have not found a situation mirroring mine. The number of rows for each worksheet is generally less than one hundred, but there will be a few exceeding several hundred. Total numer of rows of the resultant master worksheet will not exceed 10,000.
I am trying to consolidate information from multiple (atleast two or more) worksheets (in the same workbook) into a single sheet.
The worksheets have identical column headings. There are 4 columns in each worksheet. The first three columns are text strings (and some cell in these columns may be blank). The fourth column is an integer. The number of rows in each worksheet is most likely to vary. Within a worksheet the rows are unique. However, some rows may be duplicated across worksheets.
To reiterate, here's what I am trying to do.
Step 1. Create a summary sheet.
Step 2. When there is a unique row (identified by the first three cells in that row) across worksheets then I have that row as is in my summary sheet.
Step 3. When there are duplicate rows (identified by the first three cells in that row) across worksheets, I want to add up the cell in the fourth column associated with those row in my summary sheet
I have tried union (works only within a sheet), consolidate (works only with a single column). I don't need the sophistication of a pivot table but simply an aggregation of data
I am trying to do this using macros (within an add-in)
I have enclosed a sample workbook with the expected output. I created this workbook by hand in an attempt to clarify my problem statement.