See the attatched spreadsheet. I can not seem to figure out a formula that will calculate the totals for each type of data.
My goal is to have a sum for each location for # of audits, Critical Counts, and Major Counts.
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.
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’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
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.
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 ...
As per the attached file – I am trying to mark (with an X) the first 5 Adjacent cells which SUM up (and above) 480. As you will see, in some instants, one of the 3 formulas is correct. I am looking for a formula to work correct IN ALL situations.
In Col. A I marked (light Blue Background) the FIRST 5 cells which meet the Condition. In Col. F are the sum results and those which meet the Condition
are in Bold RED.
I need to have three rows link to each other. The first column cell A is a paste link and is always changing. The second B is a vlookup referenceing A and finding it in a changing table...the source for the paste link. C needs to follow the other two cells and remain "attached" to it. However, C has to be a cell that is hardcoded somewhere, meaning that C is not found in the source data and must be determined by a person. I have tried to link the sheets through vlookups and other formulas, however, this only works when the data is sorted and nothing new is added. I need a static sheet that can take in new values. I need to create some sort of relationship between the cells
View 14 Replies View RelatedI have list of names of people in a billboards league. Directly to the right of everyone's name in the adjacent cell is their "win percentage". These percentages are sorted in descending order and I need the names to move along with the adjacent cell as the percentages change. When i merge the cells things get really messy.
View 1 Replies View RelatedI need the syntax that allows me to reference the cell directly beside my target cell for each row in a column
Like:
Col A Col B
S D
T F
S F
If i was targeting Col B
In the following spreadsheet, I'm trying to automatically count the amount of times an A code (A1 or A2) or B code (B1 or B2) appear under each person's name in the K1:M5 table.
View 3 Replies View RelatedI am trying to sum 3 non-adjacent cells. Some cells are blank, not always in the same row / column, and all are the result of vlookups. I tried: E = I+M+Q, but the result is #value!
-In column E (respectively F, G, H) that's where I want the result of the sum
-In column I (respectively, J, K, L), depending on the row, some cells are blank and some cells contain a number. That's the first term of my sum.
-In column M (respectively N, O, P), I have also blank and non-blank cells, depending on the row. This is the second term of my sum.
-In column Q (respectively R, S, T), also blank or non-blank cells. This is the third term of my sum.
I will need to extend this formula to F=J+N+R, G=K+O+S, H=L+P+T, and probably also a sum with 4 terms where E=I+M+Q+U etc.
I am trying to work out a formula for calculating average of non adjacent cells but it should ignore any cells with 0:00:00 value or are empty.
At the moment I am using the simple formula =average(A1,A2,A5,A9). But if any of these cells is empty or 0:00:00, it then gives me a wrong average. I could have used a array formula with if function but it then calculates all other cells in between as well that I don't want to e.g. I may not want to include include A3 in my calculation but as it has some value in it, it gets in the calculation and gives the wrong answer again.
I've always been able to select non-adjacent cells in spreadsheets, but today noticed that I can't. I need to be able to do so to collect data from one spreadsheet to another for my monthly accounting reports. It makes collection of specific data fast and simple. Without this function, it takes far longer to do reporting and is very un-reliable. With the function working, I click on the cell ranges which are black, leaving the cell ranges which are red alone. We use black and red to identify charges which appear or do not yet appear on our monthly VISA statement.
I select all the black charges along with their corresponding account codes, then double-check the page before copying the non-adjacent ranges to make certain that all of the charge amounts and account numbers which are black on the page are selected and no red ones; then copy and paste the data into another sheet. It often makes collection very quick and easy.
When there are lines interspersed throughout a sheet with black and red charges strewn here and there, it is very easy to select only the black data and skip the red. Doing so by copying the entire range then deleting the red is laborious, and time-consuming.
What has happened to Excel's ability to select non-adjacent cells? Today, I can't select even two separate cells, using Excel's ability to do so by holding down the CTRL key, as I have in the past.
I'm using Excel 2007, on Windows Vista 64 bit; and I've made sure that my Insert function is turned off (thought that might change things?) and my Scroll Lock is turned off (I've bumped that many times in the past and found Excel doing weird things when trying to navigate a spreadsheet with the arrow keys).
I have a bit of an odd issue I need to solve. I use an Excel spreadsheet to track daily income for the store over a months' period. I have each day set to give me a rough estimate of profitability for that day. I also want to create a cell that tells me how much we've made so far, based on the number of business days so far in the current month. For example:
Day 1, $1500, $800 in expenses (monthly expenses averaged over the month, plus labor costs for the day), so a total of $700 profit.
Day 2, $1700, $749 in expenses (same as before), so a total of $951 profit.
The formula I'm using to calculate that isn't an issue, but I have the same formula for every single day, and each day has it's own cell with the estimated profitability for that particular day. In two other cells, I put the total number of business days in that month (in this case, 26), and in another cell I put the current number of business days we've had this month (in this case, 4), which is used partially to calculate the other cells. I want to use them to create another cell to contain the estimated profitability based solely on the number of business days out of the month we've had.
So E3 through E33 contain the individual days, I want to calculate - since we've only had 4 business days - what E3 through E7 would total. When I change it to 5 business days, i want it to calculate E3 through E8. So if we've had 4 business days, it only calculates the totals from the first 4 cells, if 5 business days, then the first 5 cells, etc, etc.
I can sort of picture it in my head, but I can't seem to find the necessary equation for it. It may require a macro, but I'd rather use a formula if possible. I'm not a big fan of macros.
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]....
I want a cell to countif the sum of two adjacent cells is greater than the number in another cell so for instance
Count if f101+g101 is greater than the number stored in cell u12, repeat this for cells f102+g102, right down to f50100+g50100.
I have a results table that i want to be able to rank into a simple list 1 - 4. However the results are not in cells beside each other.
I tried using a helper column and then the Large function, however cannot get it to work.
I have attached an example sheet, and in column L4 - L7 i would like to put the team names (taken from column B) into the right order from the results shown.
I have to replicate this over a number of different pools in a number of different divisions. This is just one pool.
I am attempting to program a column of cells so that if say "B3" doesn't match the contents of "A3", then "B3" will be highlighted in some way, maybe with a color. I'm using Excel 2000 and I can't see a way in Conditional Formatting to accomplish this without having to program each cell in column "B". I was hoping to be able to click and drag down in column "B".
View 9 Replies View RelatedI've been working on a spreadsheet that is used to track the application process of numerous clients. In doing this, I have a row at the top of each client that is used as a sumarry column. Below each cell in the top row, I have up to 30 non-adjacent cells that I'm trying to concatenate in the top cell separated by new lines. I'm also wanting to not display any duplicates, as there are up to only 5 steps that could be shown for each record. I found a UDF that will search an array and return only the unique values, but it accepts the data as an array, and it returns it as an array also. I seem to be having trouble passing the array from the sheet as well as formatting and returning the array with line breaks.
In the worksheet cell: ....
I have a series of data values in non-adjacent columns in an excel spreadsheet.
In the following example, assume the | (vertical bar) refers to the start of
a new cell:
F|45|A|30|F|15|F|10
Using the SUMIF formula, I want to add all numbers which are preceded by a
cell containing the letter F.
SUMIF(A1:G1,"F",B1:H1)
What I need to do is specify a range of every second cell in the row (starting with Cell A1) for validating they equal F, and a range of the alternating cells (starting in column B1) for the range containing the data to add. How can I specify these ranges (I can't name each cell individually as I have more than 30 cells to add up in my real life situation and the IF function allows selection of no more than 30 values)?
I need a code or macro which, when applied to a selected column, will merge a range of cells in that column into one cell. The criteria is to merge all cells into one cell that fall directly under the cell which contains "Items - total..." and that lines are separated by a line break (alt+enter).
Here's an example of what the column contains:
Before code:
A1: Items - total: 2
A2: four-legged
A3: cat
A4: two-legged
A5: chicken
A6: Items - total: 1
A7: four-legged
A8: dog
A9: Items - total: 3
A10: four-legged
A11: pig
A12: two-legged
A13: eagle
A14: marine animal
A15: shark
After code:
A1: Items - total: 2
four-legged
cat
two-legged
chicken
[Code] .......
Here's what I'm working with:
??
5
3
4
1
Total 13
5
6
7
2
Total 20
Is there any way I can sum only the cells in the right column where the left column contains "Total"?
How do I define two adjacent cells as a condition to be found in other adjacent rows. E.g. if the value A1=X and the value B1=Y, how do i perform the action that's seeking for X in K1 and Y in L1 at the same time. Subsequently, I'd like to display the X and the Y behind as they are displayed in the original A1-A2.
View 9 Replies View RelatedHow (in VBA) to enter the same data in a variable number of adjacents cells, given the number of cells is indicated in column P, the data to enter in column Q and the location of the first cell to enter the data is in column R?
eg
Column P = 3
Column q = 1,000
Column R = v2
I would want to enter "1,000" in V2, W2 and X2
I have following data in Sheet1
A1B1C1D1E1F1G1H1
5812643 1517
Now I want that the above values be pasted in 02 columns of Sheet2 as under:-
SHEET2
AB
58
126
43
1517
What Formula should I put in Cell A1 and A2 of Sheet2 to get the
desired result automatically.