Formula To Capture Completed Status For Relevant Data
Apr 2, 2014
I have attached the the excel sheet where in i want to calculate the total number of completed data points for one person.
Let's say "Wasim" would have completed 7 tasks and would updated the relevant cell as Completed.
Now in a huge data list How will i find "Wasim" and how do i calculate how many he has completed.
I believe Pivot table would be the answer here but is there any formula which would replace the pivot table?
(Please see the attachment) Example.xlsx
View 2 Replies
ADVERTISEMENT
Apr 22, 2009
I am trying to do what I thought was a simple look-up. On one sheet in colum A I have some text and in colum B I have a number. On a second sheet i have a list of text that may or may not match the text on the first sheet in colum A. If the text in colum A matches one of the text items in the list, I want to have the value in colum B appear next to it in colum C. I tried to modify a vlookup formula but would only get a value error if there was a match.
View 3 Replies
View Related
Oct 26, 2009
I have a spreadsheet with multiple columns. (Sheet1) The heading row is my customer name. (Sheet1) My column are the dates. On another spreadsheet, I would like to find the customer in Sheet2 in Sheet1, then do an index to find the relevant data I need, returned in Sheet2.
Sheet2: find customer "ABC" in Sheet1, if found, then find the date that also matches Sheet1 and Sheet2, and return data "apple" .
sheet1
DATE ABC DEF
2007-04-01 £55.60 £61.13
2007-04-02 £56.33 £60.27
2007-04-03 £52.14 £62.58
2007-04-04 £51.69 £61.28
sheet2
CUSTOMER DATE RATE
DEF 2007-04-04 This should be £61.28
ABC 2007-04-02 This should be £56.33
View 3 Replies
View Related
Aug 12, 2009
I have a moving average spreadsheet that I am having trouble with. In column F of 'Data for Pivot' Tab, I have a set of data that doesn't begin populating until 101 data points (cell C8 in 'Mov_Avg_Chart' is the input; in this cell F138 of 'Data for Pivot' tab) after data begins populating in Column E of 'Data for Pivot' Tab (in this case data begins populating in Column E in row 38).
What I would like to do is have the data in cells F38 to F137 = the value in cell F138. From there on forward (cells F138 and onwards), I would like the formula to calculate data in the same way as it is currently calculating it. That is, I would like the values in cells F38 to F137 be 0.00110 (in the current example). However, the data will be constantly changing. Thus, sometimes the data in column F will begin at a different cell than cell F138. In essence, I want the formula: when column E begins populating data but column F has not, I want those column F values to equal the value of the first populated value in column F (which is usually numerous cells below this value).
I just can't seem to get this one right after spending considerable time on it. I would appreciate any help you can give on this.
View 10 Replies
View Related
May 6, 2009
I am trying to figure an easy way to create a formula or possibly even a simple macro to determine % completed of a particular activity (milestone) within a process. Within each activity there are 5-10 items that need to be accomplished. Each Activity could have a different number of items. In order to provide status at an activity level, each item within the activity will be designated with an N/A (0%), Not Started (0%), Just Begun (25%), Half Complete (50%), Wrap up (75%), or Completed (100%) from a drop down box that the user will select. Each one of these criteria can have a weight assigned to it that is in parenthesis. How do I come up with a formula based on the choices above to reach an overall percentage for the activity at any given time and then tell me status level in descriptive terms of the overall activity....
View 9 Replies
View Related
Oct 6, 2006
I have a large workbook with hundreds of rows of data.
I have sorted the data and ended up with product codes in column A and my data between columns C and V.
I want to do a sumif with a difference if that is possible.
I want to sum all of the data in column C based on the product code in Column A. But I don't want to include any rows of data where Column V is blank.
i.e. If my sumif for a product code covered 3 rows C3:V5
I want to sumif Column C dependent on the product code in column A but I only want to include rows where there is a value in Column V.
So if all of the cells between C3:V3 contain data include count C3.
Moving down to row 4 if this row only has data between C4:M4 don't include C4.
Moving down to row 5 if this row has data C5:V5 include C5 in the sumif.
i.e if there was 100 in C3, 200 in C4 and 300 in C5 the sumif would be 400 because although C4 has a value V4 does not.
I want to roll this out across all of the columns C:V using the same method if possible.
View 7 Replies
View Related
Apr 9, 2009
I have a spreadsheet that is being used for tracking work completed each day in a week. Each day has 5 columns and 10 rows of data to potentially be entered, some of the data is text and some is numerical. Can anyone help me come up with a formula that will count the text and the numerical entries for each day? I tried to use a nested if statement but it exceeded the number of nestings available. The range of cells for the first group is B6:F16.
As a side question, will count work for a range of data or only one column or row at a time?
View 9 Replies
View Related
May 12, 2014
I have some vba code that currently copies a status table out of a worksheet, into a temp file for an email. The status table in excel has a column D which can have different status. I want to select everything in the table except rows that are 'Descoped'. Currently my code looks like:
Set rng2 = Sheets("Execution Status").Range("B2:F420")
This picks up everything without looking at the status. I want to change it so it doesn't pick up the rows where Column D contains 'Descoped'.
In pseudo code I would describe it as:
Set rng2 = Sheets("Execution Status").Range("B2:F420") where value in D5:D420 is not equal to 'Descoped'.
I have tried:
Set rng2 = Sheets("Execution Status").Range("B2:F420").Value "Descoped" and nothing gets selected.
View 1 Replies
View Related
Jun 16, 2014
What I would like to do is create a userform where I can input a case reference number and click a command button which searches the CSV/TXT file for any notes for the specific reference number and then returns all notes (seperated by a blank line) in the textbox within the userform in the posted/saved format.
I can find lots about importing to excel but nothing about importing data to userforms.
CSV file added for format. The userform contains two text boxes, one blank for import and one for completing the case reference number (format on CSV file. Line item 1).
i.e. if I searched by SL-001-0155648 I would get the below (It's not code but I thought the best way of showing what I mean):
[Code] .....
Attached File : Notes Test CSV.txt‎
View 15 Replies
View Related
Jan 27, 2014
I've got a password protected spreadsheet with only certain cells unlocked to allow users to complete them. I need the following to work on Excel versions 2003, 2007 & 2010.
Cell E3 has a drop down list with types of car to choose from. One of the car types is "Pool_Car".
Cell D12 should only be used if "Pool_Car" is selected from the drop down list. If any other car type is selected, and the user tries to enter a figure in D12, I need an error message to appear.
E3 & D12 are unlocked cells.
I'm happy for D12 to become locked & only allow it to be unlocked if "pool_car" is selected, but I've looked elsewhere, but can't find VBA or data validation to work.
View 3 Replies
View Related
Jan 27, 2014
I've taken part of our instrument service data and eventually want to trend the performance but have decided it will only useful to use data where I have 4 or more data points. However with so much data I want to automate the process rather than scrolling through the column. I assume this requires a lookup table of sorts but I don't know how to combine that and getting the other bits of relevant information (I.e. all dates and results).
GavInstrument Spec data.xlsx
View 1 Replies
View Related
Oct 8, 2008
I have two worksheets and need to consolidate the relevant data into one worksheet.
On both worksheets there is one column that I can relate to the other worksheet, called "TKR", which contains a code in each field, such as "000210 K", or "004170KS". Both worksheets have this column. In the first worksheet, called POS, each "TKR" code tells me that this is a relevant "TKR" code in the DB worksheet. I am not interested in the data in POS, just the code to tell me which records are relevant in DB.
In DB is the information I need to extract. But in this worksheet, there are many irrelevant records, which I need to ignore. I need to reference the data in the TKR column in the POS worksheet with the data in the TKR column of the DB worksheet, then extract the record from the DB worksheet and place it in a new worksheet, along with each other extracted record, compiling a consolodated list. I could do this by cut and paste, but I have 30 different databases, both POS and DB, each containing up to 6000 records.
In the POS worksheet there is more than one record for each "TKR" code. I can run an Advanced Filter to extract a consolidated list of "TKR" codes. I am stumped however on how to then use that list to reference the same codes in the DB worksheet and extract the records to a new sheet.
View 5 Replies
View Related
Jun 16, 2014
My task is to write a macro on auto filtering. I got 2 columns of relevant data. Cells in column A show the team name and corresponding cell in column C shows the person who belongs to the team.
For clear illustration, this is an example of the list I got.
A1: A, C1: XX
A2: A, C2: YY
A3: A, C3: ZZ
A4: B, C4: XX
A5: B, C5, 00
A6: B, C6: 11
What I need to do is to write a macro that auto filters and shows only relevant data.
Assume XX used to be in team A but now moved to team B. When i filter for team A, i need all members who are/were in team A.
For instance, when i want team A, it has to include A1-A4 and when i want team B, it has to include A1,A4-A6.
View 4 Replies
View Related
May 14, 2009
IF/ AND/OR
I have 6 tables on one sheet that I want to match data and print out the name of the relevant table ie brown, white ,pink etc
There needs to be a data match in both columns of a table before it prints the name of that table.
I have tried the following but haven’t quite got it right:
IF(AND(B3>9,B349,E39,B327,E3
View 9 Replies
View Related
Mar 5, 2009
I have a list of names that I'm looking up, and I want to list the data relevant to each name in a seperate place using a VLOOKUP ,(maybe theres a better way to do it) but some of the names appear more than once, resulting in an error.
I have on the first sheet, Name, Customer, Amount and Variance.
On the seperate sheet (in the same workbook) I have then name with 4 VLOOKUPS underneath, trying to extract the data Customer, Amount and Variance. But, I get errors in all of them.
Is there anyway I can List all the data relevant to each name using a Lookup function or is there another formula I should be using?
View 4 Replies
View Related
Feb 27, 2007
I would like a formula to capture all values >=50 to <=99 in a row.
View 14 Replies
View Related
Oct 22, 2007
******** ******************** ************************************************************************>Microsoft Excel - Board.xls___Running: xl2000 : OS = Windows Windows 2000 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutC2C3C4C5C6C7=
ABCD1StageProduct*Status*2C001MFProfiler*3C002Equity*
*4C003Bonds*
*5C004MF*
*6C005Bull*
*7C006Equity*
*Sheet4*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
My problem here is that I have various values from C001 to C006 in the Stage column to which the value of Status depends upon.
For example, I have put a formula in C2 which wud work out a result "Profiler" when the cell in A2 is C1 provided column B has one of the mentioned values as per the formula.
Moreover, when the value in A3 changes to "C002", how do I caculate a different value for Status in the same formula?
View 9 Replies
View Related
Feb 24, 2014
I want to find a way to create multiple worksheets and matching data to be placed on appropriate sheets.
Here are more details (Please check the attached sheet screenshot as well):
Excel_Macro_Requirement.jpg
In a workbook, there is a "Master-Sheet". This master sheet contains 8 columns.
I want to create as many new worksheets after master sheet as the values are there in column B (Column 2 i.e. Ad Group). In above attached screenshot, there are 8 values (B2:B9 or A-H). So, I would like to create 8 new worksheets after the master sheet. Also, I want to rename them based on their value from Ad Group column.
Each newly created worksheet should have same columns as the master sheet . Same 8 columns with their name intact.
Finally, I want all matching data of the Ad Group values to be placed on their respective newly created worksheet. For example, worksheet A should have A2:H2 data. Worksheet B should have A3:H3 data, and so on.
Please note that same ad group may have more than one row data. But I don't want to create multiple worksheet of same name ad group. The worksheet should be just one, but all matching data should be placed in that one sheet.
I know it is a bit complex task, but I am sure there would be a way to perform this automatically - probably a macro.
View 1 Replies
View Related
Nov 30, 2009
I have a spreadsheet that has two tabs that show different views of my report. In tab "Team View" I'd like to capture the information that is in "Cost Centre View" in the fields Nov09 to Oct10.
I want my formula in "Team View" tab to take the TOTAL "Run Rate Reduction" value in "Cost Centre View" ONLY and have the same "Initiative Type" in "Cost Centre View" that align to what is shown in "Team View" in column A (i.e. ISO).
I tried using a SUMPRODUCT but my formula didn't work.
Is there another formula that will give me this calculation?
View 14 Replies
View Related
Apr 4, 2008
How do I capture a formula from a cell? I want to create a macro that takes each reference (let's say 'B4') and changes it to if(B4=0,0,B4).
View 8 Replies
View Related
Jan 26, 2007
I use a scanner that initiates a f2 command and trips my macro. I would like to use multiple scanners using a 8 port hub but I am having trouble with a scanner starting my macro and another scanner dumping data into my macro before the first one is finished. This is dropping scanned data into my macros. Is there a way to prevent the scan from accepting data until the macro has completed its cycle.
View 2 Replies
View Related
Mar 17, 2014
I have a file that tracks orders placed.
Column A gives the request date of the order, based on the TAT in column B the projected dispatch date gets calculated.
Once the order is dispatched and the date gets updated in column Z, the redemption status in column V gets updated as "Dispatched within TAT" or "Dispatched out of TAT" based on whether they have met the TAT or not. If the dispatch date is left blank then it shows "Yet to be dispatched".
My problem is that the column D does not stop calculation even after the order is dispatched. And therefore because of the conditional formatting turns red the minute the number is more than 0. So it is misleading.
View 3 Replies
View Related
Oct 6, 2007
I have streaming data coming into my excel spreadsheet. The data is in 1 cell and its a number that changes almost every second. How can I capture and store this data.
View 1 Replies
View Related
Jan 24, 2007
I need to find a term "aa" and upon finding this term, the term on the right must be "bb" before we capture the number. Output will be 15 for this case.
Note that we do not know which cell "aa" will occur in, but the term "bb" will always be on the right of "aa". Can anybody help me with this? I have attached an example for your reference. Hopfully by modifying the below code, i can get the value i desired.
Sub test2()
Dim xx As Variant
Set rng = Cells.Find(What:="aa" , After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
True)
If Not rng Is Nothing Then xx = rng.Offset(0, 3).Value
MsgBox xx
If rng Is Nothing Then xx = 0
MsgBox xx
End Sub
View 8 Replies
View Related
Jul 10, 2014
I have an excel file with column stating month and rows are phases ... for each Phases we add the revenue numbers by month and it does keep changes as and when scope changes. However, i am finding it difficult to capture the changes ... is there a way i can set the guidelines to ensure all historical data changes as and when any updates and it should store the historic inputs for consolidation ... i would like to be called as Baseline, Re-baseline1, Re-beseline2 like wise.
Sample attached : sample_auditing.xlsx‎
View 13 Replies
View Related
Nov 20, 2007
how data capturing works, using controls. I try to explain what I mean. I have a userform with a combobox that allows to select data from a worksheet in another workbook (database). Using VBA, I'm asked to define the "source" for the combobox. I don't understand the difference between controlsource and row source because when I close the userform, I find only the first field filled in (column "A"), while I would like the entire row to be copied.I also would like to have an "ok" button to allow me for multiple selection. In other words:
I select the desired data from the combobox
I click on the "ok" button ahd this event should copy the data (entire row) selected via the combobox on my worksheet without closing the userform in order to allow me to select other data on the same session. The row to copy data into is selected using the firstemptyrow method..
View 4 Replies
View Related
Oct 2, 2008
Trying to capture data from specific fields (which are populated with live data collected elsewhere) into new fields based on date. Ex:
A1 - A4 have "totals" derived from an external source, and the fluctuate daily.
I want to take today's totals and drop them into D1 - D4. Tomorrow, I want totals dumped into E1 - E4, the next day they go into F 1- F4, and so on... in other words, I'm tracking daily totals over time.
I've started with a macro that I would attach to BeforeSave as follows--
View 2 Replies
View Related
Oct 22, 2009
find the attached file. Book 2 there is 4 columns ie,reg no,fleet no,product & model and on book 1 there is fleet no,reg no and model and what i need is to capture fleet no & model from Book 1.
View 2 Replies
View Related
May 22, 2007
I'm trying to do is definetly simple to a lot of you, but I'm struggling on how to get started. I attached a workbook that clearly shows my work! I'm almost there but not quiet. Basically the user will be able to make a slection that will be used to lookup values from a different sheet. Each selection triggers a ranking number at the bottom (red pattern) in the worksheet. Now I would like to capture all scenarios and display the ranking.
View 4 Replies
View Related
May 29, 2008
In the Data tab under Connection Properties, I have "Refresh every x minutes" checked. Question is how do I capture in a cell within a sheet the last time it was refreshed?
View 2 Replies
View Related
