I'm trying to write a formula where I can add the values found in non-sequential cells in a row based on if the value in the previous cell in the same row meets certain criteria.
View 14 RepliesI need to calculate ratings 1-5 of questions on client surveys received.
I have recurring sets of data, A1:E1, a number 1-5 is to be entered into one of the cells within this range. This same pattern is repeated 25 times....G1:L1, etc. At the end I need to total all the 1s, 2s, 3s, etc. Then I need to calculate an overall pct for each question based on the totals for that question, divided by the maximum score (5) multiplied by the total surveys received. The problem I am having is that my formula below is counting empty cells, and not giving me a proper pct.
=SUMIF(A1:E1,">0")/(5*$B$1)
I am doing a financial analysis for a hospital. I want to know how to add something to a number based on conditions. Base pay is $300 for the first 10 patients. If more then ten patients >11-20 add 40 dollars per visit if greater than >21 add 50 dollars per patient
View 3 Replies View RelatedI work in Local Government and I am recording the contact that we receive for each transaction that are avoidable or unavoidable. This is broken down into service areas.
The data is imported into excel from a database and therefore is constantly growing.
What I want to do it a very basic summary page so that I can group those that are and those that aren't avoidable for each service.
Because the data is ever expanding I would like this on a separate sheet from the actual data.
I attach a sample of the worksheet and would be grateful if someone could advise what formula to use on sheet 2 in cells c4, c5, d4 and d5 to make it do it automatically.
You will probably find this very easy but I am having all sorts of trouble making it work as I want to! Basically I need to do the following procedure...
1) Open an Inputbox to collect an eight digit number
2) Insert a column in A:A
3) In A1, enter a col header (URN)
4) In A2, enter the number that was collected in the inputbox
5) Enter sequential number from A2+1 to the last row
Ideally, this would be randomised, so after stage 2 do RAND(), sort, and clear contents, but if I can get the main part right I'm sure I can work that out!
Is there an easy way to change
='Week 1'!$D$21
='Week 1'!$D$30
='Week 1'!$D$39
='Week 2'!$D$21
='Week 2'!$D$30
='Week 2'!$D$39
to
=IF(ISERROR('Week 1'!$D$21), "", ('Week 1'!$D$21))
=IF(ISERROR('Week 1'!$D$30), "", ('Week 1'!$D$30))
=IF(ISERROR('Week 1'!$D$39), "", ('Week 1'!$D$39))
=IF(ISERROR('Week 2'!$D$21), "", ('Week 2'!$D$21))
=IF(ISERROR('Week 2'!$D$30), "", ('Week 2'!$D$30))
=IF(ISERROR('Week 2'!$D$39), "", ('Week 2'!$D$39))
Without having to change each one by hand?
There is a pattern to the numbers.
There are 5 weeks, rows 12, 21, 30, 39, 48 in columns D,I,N,S,X
I’m trying to change color in cell B9 according to different conditions in Cell C9 (than on b10 according to c10 and so long until necessary - probably will be around 2000 lines). Since there are more than 3 conditions and in two cases condition depends on the color of the C Column, I can not use conditional formatting. I’ve searched and found similar forums here, but since I’m ignorant in VBA code, I couldn’t manage to make adjustments. So if you can help me with the code, I would really appreciate it!
Condition and Results required would be:
IF column C = “S” than on Column B = color cell light blue with white border
IF column C = “P” than on Column B = color cell Green with white border
IF column C = “A” than on Column B = color cell Yellow with white border
IF column C = “L” than on Column B = color cell Red with white border
IF column C = “C” than on Column B = color cell Dark Blue with white border
IF column C = “ ” than on Column B = color cell white
IF column C = “V” and has white background than on Column B = color cell white
IF column C = “V” and has a Dark Grey background than on Column B = color cell Dark Grey
I don’t know if the last two are feasible.
I am writing a data validation macro which checks various conditions and highlights cells if they are incorrect.
i.e. If cell in column D = "specific text", then cell in column AH of the same row must = "specific number". If it is not that number, colour the cell red. If another cell in column D = "another specifci text", then cell in column AH of the same row must = "specific number". If it is not that number, colour the cell red.
What I've found is that I can get one condition of this nature to work, but if I run two or more in the one macro, that they supercede eachother (and thus the final one is the only one that works).
See below sample code.
Code:
Dim val As Integer
val = UsedRange.Rows.Count
For i = 2 To val Step 1
[Code]...
I have a form made that needs to copy a value from another sheet. The Form is 10 rows 5 columns and and in format for easy print. On sheet2 I have a column where each row is filled with a name.
I need to put this name into sheet1, so in the the appropriate cell I put =Sheet2!B2 and it gives me the value (name) from the other sheet.
Then I want to copy the form bellow the 1st one so that I have the same form but with the next name, which means I want =Sheet2!B3 to appear when I copy, but since my form is 10 rows when I copy it the formula copies to =Sheet2!B12 instead of B3.
I need to make close to a thousand of this forms ready for printing and I would like to avoid having to manually set the formula for the next cell.
I have an issue log containing (separate) columns with dates, numbers and text.
One column (A) has the status (open/in progress/pending/closed/re-opened)
One column (B) has raise dates
One column (C) has close dates
One column (D) has the type of track (this is text, 3 types)
I want to count the amount of cells that have a close date before date x, given they are open (in Column A), and for a specific track (column D).
After creating a new sheet with a date range in one column encompassing all dates that occur in my column B & C, I tried to come up with a formula to count how many cells, and consequently how many issues, were open (or closed) on a particular date per track and as a whole.
So for instance; count cells in column C if Column C is before date 101112 and Column A is open and column D is "Build".
After trying COUNTIF and SUMPRODUCT the conclusion is I can't get it right.
I have a list of people to send drawings out to. I have already made the selections am just trying to work out how to simplify the output so as to use as a field in a data file to go onto a letter.
"| A01 | A02 | A03 | A04 | A05 | A06 | A07 |" are for Joe Bloggs
( | are cells)
In another cell, i would like to merge them together like so
"A01-A07" are for Joe Bloggs
And also as follows
"| A01 | | A03 | A04 | A05 | | A07 |" are for John Smith
would like this to be
"A01, A03-A05,A07" are for John Smith.
So differing conditions depending on what happens in the range. I have had a couple of attempts at this using a VBA concatenate command, I can get them to display like "A01,A02,A03..." with a selected separator, but cannot get it how i want it.
I have 2 worksheets (X and Y) and am trying to write a macro/vba to copy certain cell values from a row if that row has a marked cell. For example, on sheet X, marked cell is column P and when P = "Y", then values from cells B, D, I, J, L need to be copied to sheet Y.
Sheet Y has multiple rows and the values from X need to be copied into appropriate rows. B from X goes into A from Y, D into B, I and J into C, etc.
Im not sure how to go about this other than have every cell copied and then filtering sheet Y. But Id like to have a macro only copy marked rows and appropriate cell values (some contain formulas) as the data is constantly changing and sheetY has to be printed up.
I'm trying to remove cells that contain certain suffixs. Here is the code I'm trying to use:
Code:
For i = 7 To 2000
If Cells(i, 7) = "IV," Or Cells(i, 7) = "Jr," Or Cells(i, 7) = "III,"
Then Cells(i, 7).DELETE Shift:=xlToLeftEnd IfNext i
but it does absolutely nothing. I've used this for similar macros in the past and it worked just fine.
I was hoping there was an alternative to having to use so many named ranges for each sublist (there would many sublists in the actual workbook). Anyway, I'm a little uncertain about the code you posted - do the LIST and SUBLIST codes go in Source in Data Validation, or in Source in Dependent Validation Lists Based On Header Values? Can you give me a bit more instruction on this? Also, I'm trying to use the following code to color cells upon a change in value in V1 or V2, but can't get it to work. Can you kindly tell me what's wrong with it?
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "V1" Or "V2" Then Exit Sub
Select Case [V1].Value
Case "A"
Target.Interior.ColorIndex = 40
Case "B"
Target.Interior.ColorIndex = 35
Case "C"
Target.Interior.ColorIndex = 36
Case "D"
Target.Interior.ColorIndex = 34
Case "E"
Target.Interior.ColorIndex = 19
Case "F"
Target.Interior.ColorIndex = 24
End Select
End Sub
In 'MAIN REPORT' there are thousands of columns of data, of which the important columns are 'Factory No.' and 'Row No' and 'Quantity'.
Each factory has its own worksheet as well.
In each of those factory sheets there is a 15x65 grid for each of the 65 rows that are mentioned in MAIN REPORT.
I have created and IF statement that checks if there is any data in 'Quantity row' and if there is it enters a "1" in the that cell of the virtual Factory grid.
This is the formula:
=IF('MAIN REPORT'!I2="","",1)
However, this simply checks that data while there is only 1 quantity for each row of each house.
Sometimes there will be different quantities (for diff. products), and I need a formula/macro that checks the line no. column and then,
If there are 5 items in row 7 for example then it will place five "1"'s in the first five cells of row seven in the corresponding factory sheet.
I want to highlight the cell of Column 'A' of excel only when it does not contain the text like "Verify", "Validate" or "Evaluate" in its content if, the value in corresponding cell of Column 'B' holds the value 'Y'. Secondly, the column 'A' can't contains the words like 'Verify', 'Validate' and 'Evaluate' if the corresponding cell in Column 'B' holds value 'N'. So just need to highlight those discrepancies if its there.
Column A
Column B
Expected Action
Press F3
N
Its Fine
Verify this..
Y
Its Fine
this....
Y
Need to Highlight Cell of Column A as Value in Column B is Y but the column A does not contain any value like "Verify", Validate" or "Evaluate"
Verify This.
N
Need to Highlight Cell of Column B as the Value of Column A contains words like "verify", "validate", "evaluate" but corresponding cell value in column B does not hold value "Y".
What formula do I use to populate certain cells (E5:E10 and J5:10) based on match with condition (E3) with cells from and in the ranges C2:C73 and D2:D73 without creating milelong IF formulas? I am almost at the goal... past 10pm here in Thailand and still at the office
View 2 Replies View RelatedI am trying to count the number of unique entries in a sheet, that also satisfy 2 other conditions.
I've attached an example sheet : Example email report.xlsx
The result I need is: The number of unique values in column E, that also have NULL in column G and NULL in column I.
So, in the example, the result would be 7.
I know I can do this by conditional formatting and filtering, but would prefer a formula, and perhaps also a macro that I could apply as the number of rows in each sheet is up to 20000, and each month's sheet will be a different size.
A1:A6 have numeric values 1 to 6.
I want the conditional format values to change Based on the value in Cell B2.
If the value in B2 is "old", then I want these conditional format conditions for A1:A6. Numbers between 1-2=green text, 3-4=orange text and 5-6=red text.
IF the value in B2 is "new" then I want the conditional format conditions in A1:A6 to change so 1=black text, 2-3=green text, 4-5=orange text and 6=red text.
I know how to do multiple conditional formats but i have no clue how to change multiple conditions based on another cells value.
I have a worksheet ("ALL JOBS") with huge row data and the row data will vary every day. My problem is there will be a lot of blank cells which I need to fill manually and its tacking lot of time of mine.
Code to fill these blank cells automatically with some specific texts based on the attached conditions.
I have attached the workbook for more details. FillCellsTest.xlsm‎
I'd like to sum up values based on certain cell attributes. Firstly, I don't know if it's possible to define cell attributes and how to do it. Let me put some example:
I have one column where I've put some values and I'd like to sum up the values only for certain cells in this column. In an "OOP approach" I'd use some grouping method based on attributes, so each cell would have an attribute called ID, for example, and I could select only the cell with some desired ID.
I am looking for a formula in D7
In col E I have a range of numbers
In Col AA I have yes or no
I would like to add all of the col E amounts from line 18-72 where the answer in AA of the same line is no or blank
E AA
18 2.00 Yes
19 3.00 Yes
20 5.00 No
21 7.00
D7 = 12.00
I am using conditional formatting to make cell background's go red if the data satisfys some criteria
Now I want to add the values in those red cells, can anyone make a suggestion as to how I can do this.
I have an excel 2008 sheet and want to add cells in an odd way. Here is the best way I can describe it.
I want a formula to add cells based off a number value in another cell.
So it would be something like
IF M3= 1 add C5 to SUM ; If not then go to next step
IF M3= 2 add F5 to SUM ; If not then go to next step
IF M3= 3 add I5 to SUM ; If not then go to next step
IF N3= 1 add C5 to SUM ; If not then go to next step
IF N3= 2 add F5 to SUM ; If not then go to next step
IF N3= 3 add I5 to SUM ; If not then go to next step....................
I have a sheet which has the following Columns:
Name..........Sales..........Returns..........Net
These are columns B,C,D,E,
Because the data is imported, it shows one row for the person's sales and another for their returns like this:
Dave...........100...............0...............(Blank)
Dave..............0.............-20..............(Blank)
Fred............200...............0...............(Blank)
Andy..............0............-500..............(Blank)
What I am trying to do is get the net sales of 80 in either of Dave's rows in the Net column, then I can hide the duplicate row. However, this is complicated by the fact that names without returns, (like Fred in this example,) do not have a second row. (This also applies to those names without sales, but with returns, like Andy).
If a cell in column B has text in, is it possible for column A to auto populate a sequential number? so basically if data is entered in a cell in column B I want column A adjacent cell to auto populate a sequential log number?
View 5 Replies View RelatedI have a spreadsheet where I am adding up figures in columns.
I need to differentiate between 4 different "types" of cells to sum.
Cells with figures in them are either green, red or have no fill; and some cells contain no figures at all.
At the bottom of the column, I need to total up all the figures in red cells, as well as all the figures in green cells.
The shading of these cells is not permanent - colours are changed as work progresses, so I need the totals to keep up with this.
If it cannot be done based on cell fill colour, is there any other way to do it, other than the usual long-winded way of @sum(..... etc
I would like to highlight cells is two conditions are met:Cell = 0Offset(0,-1)>0I tried the conditional format wizard and entered a formula: =IF(AND($J2=0,$I2>0)) But I keep receiving formual errors, which I understand, because it appears to be incomplete formula. But I am not sure what else I need to add to the formula in the conditional format wizard
View 2 Replies View RelatedIn cell G2 I have a formula that adds the values in column C that are labelled in column B as either 'Apple' or 'Grape'. This is accomplished in the formula using {'Apple', Grape"}. Is it possible to change the formula so that the {'Apple', Grape"} can be replaced with a reference to a range in which the names to be used in the formula will be listed (for example in J1:J3 in the attached file)?
Book6.xlsx‎
I have four cells that I want to sum: =SUM(D3,H3,L3,P3)
I want to EXCLUDE the cell from the sum if the preceeding cell (C3,I3,K3,O3) has a value of "0".