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.
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
I have a cell that I need to make a calculation if certain other cells =Y, or to show "0" if the other cells =N. I can do the calculation for the Y part but how do I add the N part of it? Here is what I have:
In the workbook, 3 columns of importance Column N,S and AC. Concatenate order ("S"&"N"&"AC") - combine it and show value in column AG. Column AC has either 0 or any other number (50,60,100 etc). If it is 0 I need to insert an 'F' at the end of the code in column AG, any other numbr and i need an 'O'.
Column N has contract code, this will be in the middle, usually its alphabets like RF,EF, but sometimes numbers like 21,33, etc but when its a single digit number like 6, i need it changed to 06 (need the 0 infront). Column S has exchange codes, and this has to appear in the front of the code in Column AG. Same as Column N, if there is a single digit number then put a 0 infront of it.
I have a column say column A. It has a pattern of cells where there is a code C10A3(alpha numeric) in say A1 and then in cells A2-A9 there are descriptions (alpha only). This continues uniformally down to cell 300. So every x amt of cells down a code appears and then below this for x cells is a description. What i would like to do is create a macro to concatenate the code waith each of the descriptions and paste it in the adjacent cell to the description. So....
I would like to concatenate A1 and A2 and have the output in B2 (C10A3Global.) And A1 and A3 with the output in B3 etc. This should continues until A10 (where the cell contains alpha numeric characters) where nothing is entered in B10 and then in B11 there should be D05A9Global.... The only difference between the description and the cell is numeric characters...
I need a formula (a VBA script won't work, as it also has to work in a google docs version) that concatenates numbers taken from a specific range in a single row with ", " in between, but only if 2 conditions in the particular column those numbers are in are matched.
Let's say, my numbers are in Row 1, from B1 to N1.
My conditional values are in Rows 5 and 6 respectively, also from B to N.
Values in Row 5 are text strings (Names of people), Values in Row 6 are Dates.
I want the numbers from Row 1 to be concatenated, but only if a particular Name and a date from a specific month are in the same column.
Row 1: 1 2 3 Row 5: Name Name Name Row 6: Date in Feb, Date in March, Date in March
Upon selection of a month and name in a dropdown cell menu, i want the output to look like this:
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 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
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 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 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
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".
I've made a "Budget Calendar" so to speak.., with my bills on the first sheet, and the Months in the following sheets. What I'm trying to do is say, for instance, the day is the 11th on the calendar, (there are two bills due on this day), and I have the bill scheduled to be on the 11th, can I use a formula to return what bills are due?
On the "Bill Dates" sheet, you can see where I'm kind of going with this...
On the August calendar, under the 11th, I want it to say "Internet, gas (utility)" in cell D20, or "Internet" and "gas (utility)" in D20&21, respectively.
I can get E20 to return the total bills due for the day using "=VLOOKUP("11th",'Bill Dates'!$B$2:$D$395,3,FALSE)", and it'd be awesome if I could get it to return them in different rows as well.
I 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.
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â€Ž
A/B/C/D/E/F/G/H/I/J/K April 2008 / August 2008/Jan/Feb/Mar/Apr/May/Jun/Jul/Aug/Sept/Oct....
How can I get excel to fill in a constant number say '3' automatically based on the date range in column A and B. For example, in this case Since the date range is from April to August how can I get excel to automatically fill in 3 for apr/may/jun/jul/aug
# of Project Names that are under Status=Contract (Amount in $’s ) & # of Trks # of Project Names that are under Status=V Verbal (Amount in $’s) & # of Trks # of Project Names that are under Status=No ($ Amount lost) & # of Trks lost # of Project Name that are Lost to (Grandview) that are under Status=No ($ Amount lost) & # of Trks lost
Must be able to add more lines & have instructions so I can do it myself for the future.
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
I am struggling to come up with a formula that will look at one column of data to check for a condition (for example, it says 7. Implement) and then look at another column to see if a second condition is met (this one is a date ie Feb-14) and count those that meet both. The data I wish to check is on a different worksheet if that makes a difference...
I have a problem finding the correct formula for counting matches with conditions between 2 non-equal ranges in Excel. The sheet is a try at making a working schedule template a bit automated.
For Week 1 each cell in the H16:H25 has a drop-down list (originating from BD30:BD50) where a work position can be chosen. The fixed list in BD30:BD50 starts with “<<SELECT>>” which is the default choice for the cells in H16:H25, and then “HOLD” before continuing with various work position names. K16:K25 is shift number 1 on Monday, L16:25 is shift 2 on Monday, and so on until Shift number 6. Then the rest of the days of the week follow (each with 6 shifts). Monday through Sunday (with 6 shifts for each) ranges over K16:AZ25. In the cells in K16:AZ25 the following can be entered: “x” (work), “o”(off), “-“ (leave).
The issue is the formula in each of the K26:AZ26 cells which are to total each of the shift columns . I want to count all the “x” in each column, but ONLY if the positions chosen in H16:H25 matches one of the positions in the list in BD30:BD50. NOT if a cell in H16:H25 displays “<<SELECT>>” or “HOLD” (even if it has a “x” entered in one of the Shift cells).
I need help creating a macro that will search through my excel spreadsheet and for every instance where column A isn't empty it should cut a range of columns from that row and paste them in a different range of columns in the row before it. It should then delete the row that it cut the columns from and keep searching until it has done this for the whole worksheet. I can modify which range of columns are needed, but it has been so long since I've worked with excel macros that I haven't been able to do it.