I would like to format column "B" based on the alpha character in column "A". There are three alpha characters, "A" with the format of $ 00 "H" with the format of $ 0.00 and "P" with the format of percent %. I want to format column "B" prior to entering a value.
Column "A" is a pull down menu containing only the values listed above (A,H,P)
I have a spreadsheet with 4 voltage value series (Bus voltage, two capacitor bank voltages, and a meter voltage). My issue is that I would like the markers on the chart for the capacitor bank voltages to be formatted according to another open/closed column of the capacitor banks. I would like the markers to be hollow if the capacitor bank is open and filled is the capacitor bank is closed. Is this possible without using VBA?
View 5 Replies View RelatedI have the following code. I am trying to conditional format a row based on the value of the A column of the row.I have a total of 3 rows. I have to use VBA because in time I may have 4 rows of data. The problem is I after the first do while loop the code stops and the code doesn't proceed to format row 2.
View 2 Replies View RelatedIn the same worksheet, I would like to format column "U" based on the alpha character in column "T" and also format column "AA" based on the alpha character in column "Z". There are three alpha characters that will be used in columns "T" and "Z" ( upper case and lower case), "A" with the format of $ #,000 & "H" with the format of $ 0.00 & "P" with the format of percent %. I want to format column "U" & "AA" prior to entering a value.
Column "T" & "Z" are a pull down menus containing only the values listed above (a,A,h,H,p,P)
I had asked and received an answer regarding the above pertaining to columns "A" and "B" and was given an answer that works, however, today my boss changed the configuration of the spreadsheet and for the life of me, I could not get the code to work using the new parameters.
I'm trying to format a singe cell based on if a row of dates is aged more than 3 days.
Column C is for Load Date. Rows C2:C100 may or maynot have a date in them. Cell C101 is a count (=counta(C2:C100) of how many cells above contain data.
I'm wanting to format C101 to turn red if ANY of the dates in C2:C100 are older than 3 days from the current day.
Is it possible to format a row of cells (B7:KX7) depending on the value in column (G7:G1000) changing?
For example column G might have the following value entered into them.
A
A
A
B
B
B
C
C
C
So all the rows containing the letter A would be formatted one colour, then it would notice it changed to B and format all the rows with the letter B in, then C etc... The letters would always appear in blocks of 10 or more (representing students in a group) so it wouldn't go A,B,A,B,C,D,E
I am not bothered about the letter being tied to a certain colour, it just needs to change when the letter changes. I'd have no more than 10 different variables in this column, meaning no more than 10 different colours to format. Formatting would be black text with different coloured backgrounds.
I need to make an entire column formatless if the cell in row 3 contains no text. I want the whole column to just appear like a normal formatless column, but if there is text in the row 3 cell, I want it to be the way I have it formatted right now.
View 1 Replies View RelatedI want to sum values in a column only if the year of the date in the adjacent column is <= to the year in cell F61. the column to evaluate is in the format d/mm/yyyy, and F61 is just yyyy. What I want to accomplish but it doesn't work:
=SUMIF(year(I9:I57),"<="&F61,H9:H57)
I'm trying to conditionally format rows of data based on duplicates in the first column, then filter the results. I have a table of data with mutiple variables assigned to different "headings" that looks a little something like this:
A
B
C
D
[Code]....
[selects the table, then GoTo Special selects the blanks, fills in the blanks with the cell above, then Copy and Pastes As Values the entire table again to fill everything in]
I’m stuck on how to input a formula into the conditional formatting window that will:
Format the text to white in columns A, B, and E based on there being a duplicate above that row in Column A only. i.e. conditionally format the values with a * below:
A
B
C
D
E
[Code]....
If I use the conditional formula I found: =A1=A2, then cell E3 gets made white text when I don’t want it to, hence the “referencing column A” part of the question (Column A is always a unique ID number whereas Column E can have a duplicate in the row above). [Edit: Why can't I type Enter or put a line break here... I'll try re-edit at home...] When I go to filter on Column C for YYY again, the conditional formatting needs to realise to un-white the text, which is my next headache because it means conditional formatting that acts relative to hidden rows as a result of a filter... Oh and did I mention the client wants this done in 2003? This is an afterthought though – as I can force them to use 2010 if need be
Wondering if it is possible to conditional format cells in "Jan"-"Dec" columns based on "Next" column data?
What I need is (lets say fill colour to differ) in one cell per row, based on latest input and "next" data.
Month Avg
Date
Jan
Feb
Mar
Apr
May
Jun
Jul
[code]......
So for example:
row2: May is latest input so need fill in cell +2("next" column data) from may. In this case July
row3: Feb is latest imput and "next" also 2. Result should be Apr
row8:May is latest input, "next" is 6. Res should be Nov.
Finally, only one cell per row to be highlighted so need to overwrite colour fill when new data input in a cell already colour filled.
I have a price list from a friend. The price list has to have every other row shaded. This part is no problem with the conditional format command.
The real trick, at least to me, is the background shade of the row is based on the first two letters in the left most column.
For instance:
Column A
CPST4
CPST5
FLADS3EC
FLADS2EC
FIL4
CP rows would alternate with one color while FL rows would alternate with a different color and FI rows would alternate with another different color. All other rows would have no shading.
MacOffice Excel 08 is used so no VB code can be used.
I have been trying to format the rows on this sheet to color scale red based on the number of repeat text strings in Column E. Referring to the attached example sheet, '321/312.2/321.3' appears the most times and the goal is to color code the rows it appears in the deepest shade of red, then the next highest occurring string would shade the rows it appears in a lighter shade, etc, in descending order. Our team currently does this across multiple sheets manually everyday and it would be a real time saver if we could get excel to do this automatically.
View 14 Replies View RelatedI am trying to figure out a way to condense my code (improve my code) Right now I am using a Macro in Excel to go through a workbook by selecting each column individually and if there is something in the header then formatting that column as text. The problem with the way I am currently doing it, is that due to size limitations on the VBA code, I can only select columns A to Z, and if there is more data in the worksheet it is not formatted. Also I would like the code to check the header and if the word date is in there, format it as a date instead. Also if there is a way to delete any invalid ranges in the workbook
Sub Format()
Dim VarFileName As String
Dim VarPath As String
Dim VarSavein As String
Dim wsheet As Worksheet
VarSavein = Sheets("sheet1").Range("C2").Value
VarFileName = Sheets("sheet1").Range("A2").Value
VarPath = Sheets("sheet1").Range("B2").Value
Workbooks.Open VarPath & VarFileName
For Each wsheet In ActiveWorkbook.Worksheets
Sheets(wsheet.Name).Select
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:= Array(1, 2), TrailingMinusNumbers:=True..........................................
I've a worksheet that is 3750 rows of cells from A to DT and contains only numbers with the exception of a header row. The rows are in groups, primarily 4 rows, although there are exceptions - these rows are separated by blank rows. Small example as follows.
0:001:002:003:004:005:006:007:008:009:000000-296567-6-1-400000286567-300-230000715557-16-11-40-18141755226-348405-362203081417192125-893581061800-2443-11124-289326-81318-1105027854331331211318-10216-161235526785691450451115951-17-1882505656683093102671056-191-33-2710000554740971-61-78-34224107-169-128-17090179-85220-55-63-1-162-140-242-235328266319106113-214-64-148205-4108120-142076-167-60-68-6418081102-800000000000-3099695807185-80356138-123194186210677727125-23233092-2942944748055564476-422051-224-34461486649597117-84209-22-251-94
I have been trying to conditionally format each group of rows and each column and color the cell in each column of four (or less) numbers in each group that represents the maximum number.
It was easy enough to come up with the conditional formatting BUT it will take forever to do this manually. I've been told to use a macro but I know nothing of programming. Has anyone seen or heard of a macro that might already be written to perform this onerous task.
Trying to compare a list of names in 2 columns. Column A has a short list of names I'm looking for in the long list of Column B.
How can you create the macro that does...
If any value in Column A matches any value in Column B
Then format (bold, highlight, etc...)
I have a column of cells with values - 0.2%, 0.32%, 0.22, 0.5 etc. The cells with % symbol are in ' Percentage, 2 decimal' format while the plain numbers are in 'General' format i.e. column contains cells in either of these formats. I need a macro where I can specify the column and it will select the cells with the % format, convert it to 'General' and multiply the result by 100 eg. 0.2% converted to 'General' becomes 0.002. When multiplied by 100, result is 0.2 i.e. is displayed without the % symbol.
View 7 Replies View RelatedI am trying to get excel to search a workbook/(or worksheet if easier) for a matching unique value and fill in its associated data. My first workbook has the SKU (A) filled in but not the UPC (B). My second workbook has both the SKU (A) and the matching UPC (C) filled in.
I need to take both workbooks/(worksheets), compare the SKUs, and if a matching SKU is found, extract the UPC from Workbook 2 and fill in the UPC field in Workbook 1, and if no UPC is present in Workbook 2, then it leaves the cell in Workbook 1 blank.
Any way to construct a formula in excel that will look at a reference in one column and find the latest date from the data in an adjacent column for that specific reference?
Below is an exctract from a much larger sheet of the columns in question.
The result in the last column should be 21/05/2014 for anything with D.O.001 in the second column and 15/05/2014 for anything with D.O.002.
Date Decision agreed
Disposal Order
Latest Decision date for D.O.
06/05/2014
D.O.001
[Code] ........
If I have a table as noted below with the following assumptions:
- this table will likely grow
- the 'Include' column data will change based on external criteria/formulas, so the 'Include' column will not be sorted.
- Macros aren't an option as this sheet needs to be macro free.
A
B
C
1
Item
Calories
Include
[Code]...
How do I build a formula that I can place in a data validation drop down to only include 'Item's that have Yes indicated in the 'Include' column?
I've been researching this and found answers if the 'Include' column was sorted via offset, but I haven't found any to sift through when unsorted. I feel like there is a simple answer to this that I am missing. Here is the sheet --> ExampleSheet.xlsx
I'm trying to write a code that calculates 2nd column based on 3rd column's number or vice versa based on the condition set on the 1st column. Below, there are two procedures. "SimpleCalc" and "SimpleCalc2". I first wrote SimpleCalc2, but it isn't working, so I worked around the error by writing SimpleCalc, which selects a cell and moves around by offset. I personally find it hard to read and error prone as I develop more logic around it.
I'm trying to develop more function based on this code, so I need to make it neat and flexible. what I'm doing wrong in SimpleCalc2? Or do you have any suggestion to improve the code "SimpleCalc"? I'm using Excel 2003.
Sub SimpleCalc()
Dim SimpleMethodRng, SimpleMethod As String, i As Integer
ActiveWorkbook.Worksheets("Dashboard").Range("P5").Select
SimpleMethodRng = ActiveWorkbook.Worksheets("Dashboard").Range("N5:P12")
For i = 1 To 8
SimpleMethod = SimpleMethodRng(i, 1).........
I'm trying to figure out if there is a formula I could use that will calculate the average of a group cells in one column based on the condition of another column. It's hard to explain, so I will show an example. All the data is on a one worksheet and I'm trying to show totals and averages on another worksheet. Location, Days
17, 4
17, 3
17, 5
26, 4
26, 8
26, 10
26, 7
On a different worksheet I would want to know what the average days are for each location. So is there a formula that I could use that will look at column A for a specified location number and then average all the days in column B for that location? I'm using Excel 2003 and have tried using the Average(if) but with no success.
Autofill column B with sequential values based on whether value in column A changes its value.
I would like to autofill column 'B' with sequential values (i.e. GenoMap1, Genomap2, Genomap3,... GenoMap10, GenoMap11, GenoMap12,... GenoMap104, GenoMap105, etc...), but changing to the sequential GenoMap# only when the value in column A changes.
This is what I imagine.
A1 "Alfiero", B1 "GenoMap1"
A2 "Alfiero", B2 "GenoMap1"
A3 "Alfiero", B3 "GenoMap1"
A4 "Allocati", B4 "GenoMap2"
A5 "Amaranto", B5 "GenoMap3"
A6 "Amaranto", B6 "GenoMap3"
A7 "Amaranto", B7 "GenoMap3"
A8 "Ambrosiano", B8 "GenoMap4"
A9 "Ambrosiano", B9 "GenoMap4"
A10 "Ambrosiano", B10 "GenoMap4"
A11 "Ambrosiano", B11 "GenoMap4"
I listed examples above of GenoMaps higher than 10 and 100 to show how I need them numbered.
I'm using MS Excel 2007 in Windows 7.
How would I count unique dates associated with another specific value on a worksheet?
For instance, Lets say I want to count each day a particular person makes a sale.
Example:
Sales PersonSale IDSales Date
Don 1001/6/2014
Don 1401/7/2014
Don 1601/8/2014
Jack 1011/6/2014
Jack 1021/6/2014
Mike 901/5/2014
Mike 1031/6/2014
So on another worksheet, I'd have:
Sales Person Days with Sale
Don 3
Jack 1
Mike 2
The Formula would go into the "Days with Sale" Column.
I have the following sample data set and I'm trying to return the appropriate column header based on criteria (i.e. DDD) and a number value which will be somewhere within the range of the table. In example below, the value returned should be Header 2 because the value is greater than those in Header 1 column (range H9 to L26).
Here's data table:
CriteriaHeader 1Header 2Header 3Header 4
ZZZ5.0015.0050.00130.00
ZZ5.0015.0050.00130.00
Z5.0015.0050.00130.00
YYY5.0015.0050.00130.00
YY5.0015.0050.00130.00
Y5.0015.0050.00130.00
DDD5.0015.0050.00130.00
DD5.0015.0025.0075.00
D5.0015.0020.0065.00
RRR5.0015.0015.0045.00
RR2.5010.0010.0030.00
R1.503.0010.0025.00
UUU0.751.505.0020.00
UU0.751.505.0020.00
U0.751.505.0020.00
P0.751.505.0020.00
T0.100.105.0018.00
CriteriaNumberValue
DDD10.00Header 1>>>=INDEX($I$9:$L$9,MATCH(I29,INDEX($I$10:$L$26,MATCH(H29,$H$10:$H$26),)))
I have two columns. Column A had numbers and column B has names. I need a count from column A for each name in column B.
View 1 Replies View RelatedI think I am working all around this in Excel 2003 and feel like I am in the neighborhood, but can't seem to quite get there. On sheet1 I have 2 columns of 100s of rows. All of the cells in column A will be filled in with a date as time goes by (1-May-09 for example). Several of them may be 1-May-09 as a matter of fact. In column B, some of the cells will have a number in them and some of them will be empty. On sheet2, I want to construct a formula that returns a count of cells in column B that have a number in them based on a specific date in column A. For example:
AB
1-May
1-May777
1-May
1-May
1-May
2-May999
3-May
2-May
1-May
2-May111
1-May
If I could get the formula right, it would return an answer of 1 for 1-May in the example, because only 1 of the 1-May entries in column A has a number in the adjacent cell in column B. A formula for 2-May would return an answer of 2 since only 2 of the 2-May entries in column A have numbers in the adjacent cell in column B, and so on for the month. If I understand things right, there are too many arguments for COUNTIF. It seems that SUMPRODUCT should work, but I can't make it so.
I have a worksheet (Sheet1) that is constantly growing with information. I have several categories under the "Category" column and then the various amounts under "Amount" column. I would like to have Sheet2 be able to keep a running total of the "Amount" column for each category as it increases in entries. I've attached an example sheet.
Example.xlsx
I am in need of restructuring a spreadsheet of addresses for mailing purposes. (I tried to find something similar answered previously, but nothing seemed to work for me.)
I have attached a small example spreadsheet below, but our spreadsheets can be hundreds or thousands of rows in length.
I need a macro that will look for the "PO Box" addresses under the column headers named "ADDRESS2" and "ADDRESS3".
The PO Box addresses will need to be moved under the column header "ADDRESS1" within the same row.
It will need to overwrite the text that is already under "ADDRESS1" and delete the text from the "ADDRESS2" and "ADDRESS3" columns - UNLESS the text in 'ADDRESS2" is a PO Box AND "ADDRESS1" begins with "c/o".
If the data in "ADDRESS2" or "ADDRESS3" is anything other than a PO Box it will remain the same. As will "ADDRESS 1".
Basically if there is a PO Box it needs to be in the column named "ADDRESS1" and overwrite anything else that was there. The exception will be for PO Boxes that are in c/o someone else, the PO Box will then need to be listed in the column directly after the column that has c/o.
If the c/o exception will be too difficult the code could just highlight those scenarios and we could fix them manually. We usually do not have a lot of them, but enough that we need to be mindful of them.
The different scenarios are listed in my sample spreadsheet.Also, the code will have to use the column header names in row 1 because those headers are not always in the same column.
I have a pivot that includes customer data and I need to create an average for each row based on the first populated column for each customer. After one month of units appears for the customer, I need to calculate the average going forward, and if there are blanks after that they should be treated as 0 in the average. Below is an example of my description of the pivot.
Jan Feb Mar Apr
Customer #1 1 1 3
Customer #2 1 3
Customer #3 2 2
So, the average for customer #1 would be calculated from Column B to Column E, and factor column D as a 0 in that calculation. Customer 2's average would start in Column C and go to Column E, and factor column E as a 0 in that calculation. Customer 3's average would start in column D to column E.
I have data like this,
Member
chr
location
web
1
50000
[Code]....
Now i want to select member column values based on the differences in values between members of location column should be 50000, it means if select web then my next selection should be SEB because next value (i.e web + 50000) is 100000 and it is matching to SEB in members column.
expected results
web
1
50000
[Code]....
I have a form in which users will manually enter a date in Column A. I would like to create a formula in Column B which will add 4 days to the date based upon Column A. However, the 4 days should only be added to a select set of dates which I would like to specify. If the date is not found in this select set of dates, then the result in Column B should be the same date as Column A.
View 3 Replies View Related
