Excel 2010 :: Creating Reports With Multiple Conditions
Jun 24, 2014
I am creating a report where there are multiple conditions and I am using MS Excel 2010.
Summary sheet will display
Date of Completion - If the exam has been taken already
In Progress - if the exam has been started but not yet completed
Not Started - if the exam has not yet been taken
Available data in Source sheet
Completion_Date - Date when the exam was taken or "Blank" if the exam is still In Progress
Completion_Status - Completed & In Progress only (all subjects that are not in the Source sheet shall be tagged as "Not Started"
View 4 Replies
ADVERTISEMENT
Sep 19, 2013
In column A, I need to highlight the cells green if the value is less than < the values in columns B and C, yellow if A = either B and C, and red if A is greater than B and C. But I only want the formatting to stay in column A. I'm using Excel 2010 and Windows 7.
A
B
C
Green->
444
512
482
Yellow->
364
571
364
Red->
101
99
87
View 2 Replies
View Related
Jun 12, 2014
I am trying to create a scatterplot from some data I have. I attempt this by selecting multiple columns using Ctrl and clicking on the letters above. This results in it plotting only the first selected column on the Y axis with only 1 2 3... on the X axis.
When I however select multiple columns by clicking on a column and dragging to more columns it does plot as I would expect with the first column as X values and the rest Y values.
I know it is possible to create a graph and add data manually but this is way slower and I think that it should be able to be done differently.
I'm using excel 2010.
View 3 Replies
View Related
Aug 20, 2013
The easiest way I can describe the scenario I am trying to create, is to use a company list of personnel (my index) and to generate a time-sheet workbook per person based on a pre-populated template. This time-sheet is saved as the persons name and has the persons name entered into cell D:10
I have a workbook which contains two worksheets;
1) An Index sheet which contains a list of names that I wish to use in Q16 downwards (note the length of this list will vary each time I run this)
2) A "template" sheet which I wish to duplicate in new workbooks
3) A second "data" sheet that I wish to copy across in new workbooks
I need a macro that will take the "template" and "data" sheets and copy it into a new workbook, renaming each new workbook to each name in my Index sheet. I also want that same Name to be copied into cell reference D:10 of the "template" each time.
The end result is that I should have a series of new files generated and saved which are named the same as the Index list, with both the "Template" sheet and the "Data" sheet present, with the cell D:10 pre-populated with the Name provided in the "Template" sheet.
I'm using Excel 2010.
View 9 Replies
View Related
May 19, 2014
I am trying to create challenge/response cards. Each card will have a 5-digit number in the upper-left and lower-right corner along with a series of rows containing a line number and 3 pairs of challenge/response words.
Rather than have have copies of these cards, I want the central office to have a macro-enabled workbook wherein they can enter the two numbers from the card and have the challenge/response matrix regenerate on a worksheet.
So, essentially, I want the challenges random number generator seeded from one of the 5-digit numbers and the responses random number generator seeded from the other 5-digit number.
I am trying to use
Code:
Randomize(Seed)
where Seed is one of the 5-digit numbers.
The problem appears to be that, no matter what I use for Seed, the RND(-1) function is not resulting in different numbers.
I am using Excel 2007 and Excel 2010.
View 1 Replies
View Related
Apr 24, 2014
I need to create 3 or 4 detailed reports from an excel 2010 worksheet.
My worksheet contains data including date, invoice number, company , consultant, days, dollars. There are approximately 100 records in the worksheet.
Requirement 1 :I want to use excel 2010 to automatically generate a series of reports broken down by consultant showing date, invoice number, company, and consultant with totals for days and dollars, when all the records for consultant 1 are listed, then I want to show consultant 2.
Same for any other consultants with a grand total at the end.
My understanding is that excel 2010 makes this process very simple .
View 3 Replies
View Related
Feb 17, 2014
I have an expense workbook (Data) with 4 columns (ID, Item, Cost, Date). I would like to create macros that will generate 3 different reports and write to 3 different worksheets.
The first report is sorted and sum up the cost for each item. Please see the worksheet "Item".
The second report is sorted and sum up the cost for each part ID. Please see the worksheet "ID".
The third report is the cost for each month and Year-to-Date cost right next to it. Please see worksheet "Summary".
write the macros for each of these reports assuming that we don't know the number of rows in the "Data" worksheet.
View 4 Replies
View Related
Jul 1, 2013
Using Excel 2010. I'm writing a macro that sets up a workbook to be used for estimating at the beginning of a project. In the code I need to create multiple tables (formerly known as "lists") in the workbook. Then later in the code I need to refer back to those newly created tables. Currently, the code that creates the table is part of a loop that creates the table on many different worksheets. The problem of course, is that I have to name the Table, and then it won't create a table of the same name on the next sheet. Then, later in the code, I need to make adjustments to the table that was just created before looping to the next sheet.
Is there a way to create a table without giving it a constant name? Or by giving it a name that builds off of other info in the sheet? For example, I would be good with the naming the table after the sheet name: "Sheet1_Table" or such.
Code:
Sub Auto_Open()
'
Dim sht As Worksheet
If Range("A1") = 1 Then
[Code].....
View 2 Replies
View Related
Sep 30, 2013
I have a workbook that contains
Tab 1 --a datatable with mulitiple suppliers. Data changes once a week.
Tab 2-25 --Report Tab for each client--to be sent out to client weekly to update status of deliveries.
Current process:
1. copy and paste data table into excel model Tab 1
2. use advanced filtering to pull data from the datatable into each tab one tab at a time--25 times!
3. Sort the date in the report tab--25 times
4. Format the report tab--25 times
5. Copy and Paste into a separate workbook--25 times
6. Send each report--25 times
How do I reduce the number of steps in this reporting process?
View 1 Replies
View Related
May 14, 2008
I am trying to create a report that takes two user inputs and looks them up on another worksheet in the workbook and returns the right value. As an example I have the user input a date and a job location and on another sheet there is also a date and a job location that has a population associated with it. Is there a way to return this information so that it changes whenever the user changes these two criteria
View 9 Replies
View Related
Mar 31, 2014
I have a hard time creating a macro from scratch I have 7 different excel files which im trying to create a macro that will combine all of this files (reports) into one, and then put the CT information (an excel file i have) on a separate tab within the 1 common report.
View 9 Replies
View Related
Mar 20, 2014
MS Excel Version : 2010
Attached File name : countif for 2 conditons a given month.xlsx
I want to a count with given 2 conditions AA11 / BB22 and xxx / yyy according to the month in yellow cells.
View 2 Replies
View Related
Mar 29, 2012
I have a date in column A. I want to format to YELLOW column F based on whether column F is blank and 20 days past column A. I also want the formatting to change colors to RED if it remains blank past 30 days. Then, when column F is filled in I want the formatting to be removed.
I am working in Excel 2010
View 1 Replies
View Related
May 28, 2013
I have two conditions for a cell. One is that it counts the time elapsed in cell M4, (=now()-J4). The other is whether or not we are still waiting for a response (in cell L4, Y or N). What I would like to do is make it so that when we are still waiting for a response the M4 cell is highlighted yellow if more than 3 days and red if more than 6 days, but if cell L4 is changed to "N", then the formatting goes away. II use Excel 2010.
View 1 Replies
View Related
Jul 29, 2014
I want to create a bell curve for my team's annual appraisal. There are 12 people in my team. The scale is 1-5, where normally rating range from 2.9 to 4.0.
View 1 Replies
View Related
Dec 28, 2011
Im trying to write a code in vba in Excel 2010 that would incorporate the vlookup function in excel.
My Data Looks like this:
List 1Weight 1List 2Weight 2List 3Weight 3List 4Weight 4a2.00%j20.00%a14.00%p2.00%b4.00%k32.00%d2.00%y3.00%
c7.50%e10.00%g14.00%u6.00%d8.00%d15.00%h20.00%h7.00%e9.00%q13.00%y45.00%f8.00%f20.00%r5.00%q5.00%
d9.00%g35.00%n5.00%t10.00%h10.00%r14.00%i4.50%w15.00%b0.18Z0.04S0.04Total100.00%Total100.00%Total100.00%100.00%
Where I have X number of Lists, each composed of two columns. The 1st column has the name of each item in each list, and the 2nd column has the value for that item.
What I'd like to do is create a function that would let me choose two lists, and tell me the amount of items in List X and what their values are in List Y, and then total them. Also, I would like it to work the opposite way, and tell me the amount of items in List Y and what their values are in List X, and then total them.
So for example, If I wanted to look at lists 1 and 2, the function would calculate that for List 1, Items D & E are found in List 2 and have a total value of 25%. For List 2, Items D&E are found in List 1 and have a total value of 17%. For all items not in both lists, it would return values of 0.
The code I came up with so far looks like this:
Function AK_Overlap_Go(x, y)
Dim Temp(1 To 2, 2 To 1)
Dim x As Integer
[Code]...
One of the problems I'm having is that the lists contain a different # of items, and so I think I need to loop the vlookup for each row. However, I'm not sure how to do that and get the cumulative values for each list.
View 3 Replies
View Related
Feb 23, 2012
I would like to create a sound alert if the answer to an "if" statement is false. I am using Excel 10. Can it be done, and if so, how?
View 5 Replies
View Related
Apr 26, 2014
I am creating a chart with more than 2000 dates (each day). How can I format the axis so it only displays the 1st day of each quarter (01/01/2010, 01/04/2010, 01/07/2010...).
At the moment I cannot format the axis and minor/major units remain numbers.
I am using Excel 2010 and European date format.
View 2 Replies
View Related
Aug 11, 2014
Im looking to create random data for an athlete over about 4 months with daily tests, however when im using the =randbetween(*,*) function the data is too 'Random'. so im looking to create a trend in the data, like real athletes will have. is there a function in Excel 2010 that can do this?
The boundaries of the data are -336,416 (i know its quite a big range)
View 8 Replies
View Related
Apr 5, 2012
How I could create a chart (standard column) with an average line in Excel 2010?
View 4 Replies
View Related
Nov 7, 2012
I have headings across the sheet "Inventory" in cells E5:AA5 with further headings down the sheet D6:D40.Data Validation drop down list is in cells E6:AA40.I want to be able to place a number 1 or number 2 in any cell in the range C6:C40 in the "inventory" sheet and with a macro create 2 new sheets.Sheet 1 will contain all the headings which had a 1 in any of the cells in the range C6:C40 and sheet 2 will have all the headings which had the number 2 from the range C6:40.Both sheets 1 & 2 will contain all the headings from "Inventory" sheet in cells E5:AA5.The data validation drop down list is not required in sheets 1 and 2 but if "inventory" sheet does get updated that it also updates sheet 1 and sheet 2.... Version i am using is Excel 2010
View 1 Replies
View Related
Mar 2, 2014
I'd like to create a simple graph exactly like the one on the picture, but for the life of mehow to do this on Excel 2010.This is the data I have:
Seperation First target Second target
0 59.00% 0.00%
2 64.28% 40.62%
4 62.68% 48.99%
6 60.81% 65.83%
8 61.82% 70.21%
View 6 Replies
View Related
Nov 11, 2013
I'm trying to create a bracket for video game tournaments and I'm having trouble with some of the functionality I want it to have.
Right now, I have cells B7 and B9 that will pull player names from another check in sheet. I have another cell, D8, that I would like to have a drop down list in containing the contents of B7 and B9. Everything I see online says to use Data Validation, but apparently that tool can't make a list using cells that aren't next to each other, so that won't work. How else can I tell excel how to populate this list? It is vital that the cells be apart from each other because the visual formatting is necessary for displaying the bracket on a projector so that the players can see their match assignments.
In addition, I would like to have another drop down menu in another cell with a list of valid bracket sizes (4, 8, 16, 32, etc.), and have the cells of the sheet either be visible or invisible depending on how many max players can enter. In other words, if columns B, D, F, and H correspond to the number of rounds it will take to finish the tournament (3, 2, 1, and 0, respectively), I would like the sheet to display only the cells necessary for the tournament, pull player names into the left most cells needed from another worksheet used for sign ups, and auto populate all of the columns to the right of the column for first round with drop down lists for the players who could have won that match in the previous round.
View 1 Replies
View Related
Aug 22, 2013
coding mandatory field in Excel 2010.
This is a code that i have for having a master workbook, and saving it in different folder so my employee cant access it
Sub NextInvoice()
Range("J2").Value = Range("J2").Value + 1
Range("E6:E9,H9,J9,B14:K20,H4,B28:K32,B36:B39,D36:D39,F36:F39,B42:K43,B46:K47,B50:K51,B54:K55,B58:K59,B62:K63,B66:K67,B70:K71,B 74:K75,D78:E78").ClearContents
End Sub
Sub SaveInvWithNewName()
Dim NewFN As Variant
'Copy Invoice to a new workbook
[code].....
I have found a code for mandatory cell, but it creates a second macro and i was not able to link the two.The mandatory field has drop down of employee's and it is located in the cells D78:E78.
View 1 Replies
View Related
Apr 26, 2014
I'm trying to auto populate a calender style sheet in Excel 2010 based on data from a Work Schedule sheet. The work schedule sheet contains a list of jobs, with each row representing a different job. There is a column for the start date (e.g. 25/04/14) and a column for the end date (e.g. 26/04/14). There are other columns which select resources such as people and vehicles. Each resource may appear on any one of several columns for each row, e.g. Site Operative 1, Site Operative 2 etc.
On the calendar sheet, in which one cell represents one day (e.g. 25/04/14), all the dates are shown along the top going right and all the resources are shown on the left going down.
On the calender sheet, in every cell I want a formula that will look at the Work Schedule sheet and see if that particular resource is being used on that particular date. If it does, the cell can display information from another cell such as the job number or job name to which the resource is assigned; if it doesn't, the resource isn't being used so it can display "Free" or "Available".
View 2 Replies
View Related
Jun 3, 2009
Trying to program cell C1:
If A1 is between .81 and 3, AND if B1 is Adult, then C1 is Priority Mail
If A1 is greater than 3, AND if B1 is Adult, then C1 is FedEx Ground
If A1 is Less than .81, AND if B1 is Adult, then C1 is First Class Mail
If A1 is Less than .81, AND if B1 is Child, then C1 is Hold for Inspection
etc.
There will be a list of ~45 conditions that will populate a specific value in C1. Can this be done? If so, how?
View 6 Replies
View Related
May 6, 2014
I would like a macro to be able to save 26 tabs within the one document to individual PDFs.Preferably I would like to be able to specify each time exactly which tabs get printed, because often I don't need to print all 26, just the first 10 or so.I would like each PDF to automatically be named with the value in cell E10 of each tab.E10 already has a formula to create its final value. It references cells from other tabs within the same document. Hopefully the fact that this cell has a formula in it won't affect my ability to use the resulting value as a 'save as' reference?I would like it if the PDFs save to the same location as the Excel sheet from which they're generated is located. The location of the excel sheet will change every three months, so I'd prefer not to specify a location with a specific filepath, as it will have changed by the time I run the macro again.
I am using Excel 2010.
View 5 Replies
View Related
Aug 5, 2012
I am using MS Office 2010. I want to count---on multiple sheets---the number of times that a given cell is greater than another cell if and only if a third cell is equal to a given value. I want to do this for 4 sets of data on each sheet. I thought I had it figured out with this formula---
=SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$R1"),2*(AND("'"&$H$1:$H$43&"'!$E1">"'"&$H$1:$H$43&"'!$F1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$S1"),2*(AND("'"&$H$1:$H$43&"'!$G1">"'"&$H$1:$H$43&"'!$H1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$T1"),2*(AND("'"&$H$1:$H$43&"'!$I1">"'"&$H$1:$H$43&"'!$J1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$U1"),2*(AND("'"&$H$1:$H$43&"'!$K1">"'"&$H$1:$H$43&"'!$L1"))))
but it returns a value of zero each time. Clearly there is an error in the formula.
Here is some background:
-- $H$1:$H$43 is a block of cells that has the names of the sheets in the workbook
-- E1 and F1, G1 and H1, I1 and J1, K1 and L1 are the four groups of cells that I am comparing.
In the entire workbook, I want to add 1 (counting function) only when:
R1=2 AND E1>F1 or
S1=2 AND G1>H1 or
T1=2 AND I1>J1
U1=2 and K1>L1
on each appropriate sheet in the workbook.
View 1 Replies
View Related
Jun 17, 2014
I need to convert data from column IDS into separate rows, all other columns need to stay in tact. There are several distinct patterns for the IDS column, main identifiers are always starting with FILER or TEAL and the trailing numbers behind it have no more than 6 digits.
BEFORE MACRO
ID
AREA
TYPE
CLASS
QTY
IDS
1
COAL
TYPE9917312
CLASS881345
2
FILER756911**/**FILER123188 ^** FILER877119*118
[Code] ........
AFTER MACRO
ID
AREA
TYPE
CLASS
QTY
IDS
1
COAL
TYPE9917312
CLASS881345
2
FILER756911
[Code] ......
What the MACRO would look like? This is for Excel 2010.
View 3 Replies
View Related
Jul 2, 2014
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
View 8 Replies
View Related
