I have multiple sheets cataloging multiple vehicles' mileages in multiple areas (one sheet per month). I want to reorganize this data by Vehicle ID rather than month.
Here is an example spreadsheet to help explain
I have attempted this by writing this formula ...
I’m trying to make my life a bit easier, by adding a few macros and formulas to the spreadsheet (Everything was done completely manually before I got here!!!).
What I would like to do is take two columns, which contain a start and end time for work shifts, and colour them GREEN once I have entered a name in the Worker column (Along side the two with the time), and also to fill a cell with a Yes or a No. I’m aware of auto conditioning, and I’ve tried to have a play to get this to work, but I just can’t work it out.
I have posted a link to an image which shows what I want. I hope I've explained it well enough!
http://img530.imageshack.us/img530/6239/excelspfk0.jpg
Any method to determine the number of instances a value ("4" for example) appears next to a specific value ("x" for example). I don't have a workbook; I'm just looking for any formula that can search for specific values and find the total number of instances that an adjoining cell contains data.
View 14 Replies View RelatedI have a column with Cells that will sat True or False, the amount of rows will be different every time, I need to work out what the total percentage of True cells compared to the total number of cells. How would this be achieved.
View 9 Replies View RelatedPicture1.jpg
How do I create a CF if a cell is blank (in my case represent no sales) i want to have it yellow (ex. C2)
But if it has two consecutive blank cell i want to CF with Red. (Ex. F3)
But i also need to exclude blank cells when we we have no previos sales of that item (Ex.B3).
I have been reading through trying to find a similar post but the nearest i could find was one dealing with numbers rather than text. This is how the workbook looks.
Column A contains a list of numbers based on vehicle types. Column B contains a list of vehicle names.
A B
1 Car,
1 Taxi,
2 Bus,
2 Lorry,
3 Skateboard,
3 Bike,
1 Motorcycle,
What I am looking for is the best way to combine the text from column B depending on the value of column a. the finished results would then end up as: "Car,Taxi,Motorcycle,", or "Bus,Lorry," etc. This is just a simple list.. The one I am working with has around 2500 entries. I have been trying to do this using the CONCATENATE function and IF statement but it's just not working..... My head is ready to explode
Based on a trigger point (being a month), and the number of times the expenditure is spread over (factor), can I formulaize the repetition of the figures?
Sheet1 *CDEFGHIJKLMN5***Trigger Points**Desired result via formulae
**6CostFactor*JunJulAugSept*JunJulAugSept7
£ * * * *10,000 2**1**** £ * * * *5,000 £ * * * *5,000 *8 £ * * * * *6,000 3*1**** £ * * * *2,000
£ * * * *2,000 £ * * * *2,000 *9 £ * * * * *8,000 1***1**** £ * * * *8,000 *
I am really stumped at this particular point in my coding. What I am trying to do is to have a user input an ID number. When they click OK, I want to find the ID number and then subtract 1 from a number that is 13 columns over. So if the ID number is "123456789" and that matches the value in A3, I want to subtract 1 from the amount in M3 (leaving the new value in cell M3). So, if M3 was 30, now it would be 29.
View 2 Replies View RelatedHow would you select cell values from differing rows based on the name in an adjacent column. ie, if it says John, add that data to the source data, if not move on.
View 5 Replies View RelatedI have a column of dollar amounts (variances) and next to each is the shift they belong to.
I am looking to get a break down of the variances for each shift.
Attached is an example of what it looks like.
I have a matrix in excel to show % and $ for multiple columns - set up like this:
Rows Part 1 Part 2 Part 3
Criteria 1 % $ % $ % $
Criteria 2
Criteria 3
the $ columns are conditionally formatted to highlight the top 10 values on the matrix. is there a way to have the corresponding % cells also highlighted? The % and $ aren't both top 10, so the % would have to be highlighted based on the $ top 10 cells.
I have a column of dollar amounts (variances) and next to each is the shift they belong to.
I am looking to get a break down of the variances for each shift.
Attached is an example of what it looks like.
I have a worksheet with following data in 1 tab.
[TABLE]
Seq Class
SE110
SE270
SE110
SE370
SE310
SE110
SE170
SE370
[/TABLE]
In a different tab I want to calculate the unique count of sequence for a specified class.
In above eg: for class 10 it should return 2
I would like a macro that deletes rows based on having two blank cells in adjacent columns. I have achieved this with the following code however i need it to only delete rows below a certain row. How would i achieve this?
Sub DeleteBlankARows()
Dim r As Long
For r = Cells(Rows.Count, 30).End(xlUp).Row To 1 Step -1
If Cells(r, 3) = "" And Cells(r, 4) = "" Then Rows(r).Delete
Next r
End Sub
I have a spreadsheet with data populated down column B. This size will vary from day to day so is there a macro I can run that will populate Column C with a formula based on their being data in the adjacent cell.
View 2 Replies View RelatedI have a spreadsheet with 'due date' for returns in column L and 'received date' for returns, in the adjacent column M.
I would like to get the due date to turn red when it is overdue, ie. the date in the cell has arrived and no return was submitted. By trawlling the forum, I managed to accomplish this with the following in conditional formatting:
Cell value is less than or equal to =TODAY()
All's great.....but - I would like the date to turn back to black if I enter a date in the adjacent received column, column M.
In other words, I want to flag a problem if the due date has passed, but once I receive the return and enter the date I receive it, i would like the due date entry to revert back to the original formatting ie. black. So only the outstanding returns are highlighted.
Using Excel 2002. Here's my problem.
Column A contains the month (as text)
Column C contains an employee name.
Column O contains a reason for absence.
Column K is the number of hours of absence.
The employee's name may appear several times in the worksheet. What I want to do is count the number of hours per type of absence.
E.g. If A=MAY and C=BOB and O=SICK then total hours from all instance of K = X.
This will be used on a seperate worksheet where the name C will be referenced from a validation list.
I have a workbook that has two worksheets. One worksheet is an input worksheet. A user will select a date from a drop-down list and type in the events that occurred on that date in 8 adjacent cells. The user selects a button that advances date and clears form. On sheet 2, whatever was typed into sheet one is saved via VLOOKUP formula. However, if I try to change something retroactively and select a previous date from the drop-down list, it clears everything in sheet 2 that was typed for any selected day.
View 1 Replies View RelatedIm trying to make a formula that lets you type in your current level, and then calculates how much XP you have remaning untill you reach the max level.
The way the forumla would need to work would be something like this:
(E10) Enter Current Level:
(F10) 37 (which is my current level)
F11 - would then add cells from C37 to C70 (calculating the total amount of XP required) and then display in (F11)
Being able to say if, then is far to cpmplicated for me in excel. I'm just trying to make this for a bit of fun and am sure one of you talented people can easily do this.
Basically from Sheet OEE V20:V500 I have a list of problems being selected from a drop down list validation (which users can add to the list for new problems). Along side these "problems" in Sheet OEE U20:U500 I have a number which represents the number of minutes the problem caused them. Some cells in both these columns will however be empty if there was no problem occour. But wherever there is a problem selected, there will be a number alongside it, there will never be one without the other.
What I want to do is look down Sheet OEE V20:V500 and get two lots of information -
The unique problem names (no duplicates of the same problem) in Sheet Reports A1:A100 for example (I may change the range of this).
The number of occurances of each of the problems it lists in Sheets Reports B1:B100.
Count up the total number of minutes of each problem. So for every occurance of "Paper problem" there will be a unique number in Column U in the same row as the problem and place this in Sheet Reports C1:C100.
I would ideally like to have this as VB code as I am going to tie it into a command button which formats and prints my report page.
I have found various bits of code dotted about the forums for counting unique cell entries but they always seem to produce a list with lots of blank rows (I would like a list one after another without blank rows all over the place) and I'm really struggling to figure out how to make it count up the numbers in the adjacent cell of each entry it sees.
Just in case the list of problems for the cell validation in Sheet OEE V20:V500 is found in Sheet OEE AQ16 downwards.
I have a workbook with 30 worksheets. Each sheet has 84 rows of data (start in 15 columns (A to O). I would like to create a summary sheet that only shows the most important data from each sheet.
The summary sheet would have 12 lines of headers and formatted crap at the top.
The Summary sheet header columns would be:
Site (A), Date (B) Health (C), Status (D), Critical (E), Task (F),
Dependencies (G), Owner (H), T-Date (I), Task Date (J), Mitigation Date (I)
The data in the sheets are not in that order, of course.
That
1. puts the name of the sheet I am copying from in column A
2. the deadline date in Column B (that date is always in C10 of each worksheet)
3. and copies Cells from Column A,B,G,H,I,O in any row in which the value in A is not "good" into columns C through H. I would like to paste those rows into the summary sheet. I have code that loops through the sheets and rows in each sheet to find the rows to copy. I can copy cell values directly from the active sheet to the summary sheet, but because I am copying a cell at a time, it takes 7 minutes. Yes I am impatient :)
Here is the code snippet where the copying is done:
Dim sh As Worksheet 'current worksheet
Dim DestSh As Worksheet 'worksheet in which to paste summary
Dim Last As Long
Dim CopyRow As Long 'row to copy
Dim LastCopyRow As Long
[Code] ........
ExitTheSub:
Application.Goto Sheets("KMARollup").Cells(1)
End Sub
I think there must be a way to use ranges to build an array of cell values and paste only once but I am lost here.
I have a couple of spreadsheets that has several columns each containing several hundred thousand rows of codes. To quickly analyze this data, I am trying to come up with a conditional formatting formula to highlight the respective cells when specific values occur next to each other. A particular code will show up in many cells, but the code that is the respective adjacent cell is always different. I need to know when row A contains, for example, '9928559' and row B contains '36415RT'.
View 3 Replies View RelatedI have 2 columns of data and want to be able to merge as follows
COL A COL B
4123567 123.45
4125467 900.56
4356456 456.32
need to get result of
COL a
4123567
123.45
4125467
900.56
4356456
456.32
I am trying to merge text, in four adjacent cells in the same row (say cells A1,A2,A3 and A4), into a single cell (say cell A5).
I would like a comma or full stop and then a space between each item merged (cell A5).
The text to be merged may appear in any one of the four cells (cells A1 to A4). Those cells without text are blank.
Only where all 4 cells are blank, will I need cell 'A5' to indicate this.
The formula needs to be relative as I will need to copy the formula down the spreadsheet so that it applies to additional rows.
I would like a macro that when run, finds empty cells in a column within the used range and fills them with the same formula in the other cells in the same column but relative to the row.
I have a basic understanding of VBA so if someone can set me on the right track i'll have a go myself as i appreciate this would take a while to write out from scratch.
Code:.....
I am constantly editing this (we currently have over 100 accounts) and therefore the totals are changing.I have a formula for Total but I need formulas for the other two, based on when the cells in columns F and J are blank or have dates in them: For active, the total is the sum of all numbers in column M but only when there is a date in column F and a BLANK in column J. For yet to enter, the total is the sum of all the numbers in column M but only when both column F and column J are blank. At the moment, my accounts run from row 6 to row 142, with the first line of totals in row 145, however this is constantly expanding.
What I have In Column B, I have the datesIn Column I, I have engineers name What I need I want a macro to generate Serial Nos. (1,2,3....... n) in column A If an only if the date in column B is today's date and the engineer's name matches with the PC's username
The following is my code
[Code] ....
Above code runs without errors but does nothing.
What I want to achieve: those cells have number 1 ( in column E) , adjacent cell values ( In Column D) are copied to G12 in the following format:: Mark,Lark ( i.e. cell value , cell value, etc)
is it possible ( with a command button) Sample attached
I have 500+ rows of data in column A (Credit), with corresponding data in column B (Cash). I use "=SUM(LARGE(A:A,{1,2,3}))" in C1 to find and sum the three largest "Credit" entries, how do I sum the corresponding cash values associated with those three large credit entries?
View 9 Replies View RelatedI have two simple columns of data: one with names (Column A) and the other with numbers (Column B).
I am looking for the most efficient way of selecting cells in Column B of a certain value and also selecting the cells to the left of those selected cells in Column A.
I have the current code based on SpecialCells, it works most of the time but for some reason it's buggy and sometimes returns an error. Is there another way doing this without using Special Cells?
Code:
Sub SelectbyValue()
Dim Cell As Object
Dim FoundCells As Range 'Range that's found
Dim WorkRange As Range 'Range to search
Range("E1:F4").ClearContents
[code]....