If I have an assortment of odd and even values in range a1:j100, using the Array formula {=If(Mod(a1:j100,2),"",a1:j100)} will clear out all the odds, leaving the evens.
Is there a way I can add to this formula to color the qualifying cells (odds in this instance)? I know Conditional formatting is perfectly suited for the job but I am looking for a departure from this.
Is it possible to write a formula that will sum only those numbers in cells colored yellow, for example?
View 4 Replies View RelatedIs there away to count cells with 1 or more different fill colors in a column?
View 9 Replies View Related{=AVERAGE(IF(('Sales Reports'!$A$1:$A$5000=$A3)*('Sales Reports'!$C$1:$C$5000=$C$1),'Break Reports'!$D$1:$F$5000))}
and am copying it to other cells using this
Sub copyformula()
With Sheets("Sheet2")
.Range("B3:B5000").Formula = .Range("B3").Formula
End With
End Sub
It does the copy but removes the { and } thus rendering the formula useless to me. How can I accomplish this task and keep the formula as an array formula?
I tried to drag the formula down and recorded it as a macro but when it runs it takes far to long.
Essentially I have inherited a spreadsheet which is crammed full of complex array formulas.
No problem in itself as I understand array formulas pretty well (or so I think!). My problem is that when i want to ammend some of these, i cannot easily see what range I need to highlight in order to edit them i.e. I obviously cant do a single cell as its part of an array. I hope I have made myself clear?
So my question is this.
When the cursor is sat in a cell that is part of an array formula, how can I easily find out ( ideally by the array formula range being highlighted in (say) red ) what cells are included in the array formula?
Would conditional formating do the job?
I am trying to use the IF formula or a similar VBA/Macro to color certain cells. We deal with 16 different sand types that come in on rail cars. I want to put in the sand type in a cell; EX: C11 has text "30/50BH" if this statement is true to color cells A11-D11 yellow. If it is not a true statement to check for the next sand type, "20/40BH" and go on from there. If I can get an example of what to do I can build it for the 16 sands we have.
The formula I have in mind would be something like
=IF("30/50BH",[colorA11:D11,Yellow],[IF("20/40BH",[colorA11:D11, Purple], .......
I'm sure there is a less brute force method of doing this, but my knowledge of programming and excel is limited. I am using the 2010 version of Excel.
I have a sheet called "Sheet1" in workbook "File1". In this sheet, I have a lot of formulas and some of them involves linking to external files located in E: Finance folder. Is there a way to automatically highlight those cells containing a formula that references to external files?
View 3 Replies View RelatedI have an array formula that takes in a bunch of dates and returns the next date that is not in the past:
{=MIN(IF($N$4:$N$28<TODAY(),999999,$N$4:$N$28))}
This works great, but I'm wondering if there is a way I can write it to work on cells that are not adjacent to each other. For example, can I have it operate on only cells N4, N7, N15, and N22? Is there an easy way to do this?
I have spreadsheet that displays many blank cells. I would like all the information from this sheet (example below) displaying on another spreadsheet but without the blank cells.
On the example below I've shown how I want my sheet3 to look. (Cells B19:I22)
The sheet where I want to remove the blanks is Sheet2 B2:AE367
Is this possible using array formulas or macro?
******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 11.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB1=BCDEFGHI1RedYellowBlueOrangeBlackGreenWhitePurple205/01/2008 27/01/20083 21/04/2008 4 31/03/2008 5 15/03/2008 617/01/2008 03/07/2008 7 22/08/2008 8 9 10/05/2008 25/09/2008 10 09/06/2008 28/02/200811 12 11/09/2008 1307/05/2008 12/11/2008 14 01/07/2008 15 16/10/2008 16 14/10/2008 17 18 19RedYellowBlueOrangeBlackGreenWhitePurple2005/01/200821/04/200831/03/200815/03/200803/07/200814/10/200825/09/200827/01/20082117/01/200810/05/200809/06/200822/08/200816/10/2008 12/11/200828/02/20082207/05/2008 01/07/200811/09/2008 Sheet2 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
If I have the following data set in cells A1:C9:
IDCommentFirst Unique
109876Low OilTRUE
109876Checked On 12/12FALSE
109877Checked on 12/15TRUE
109878Correct LevelTRUE
109877Correct LevelFALSE
109878PerfectFALSE
109877NewFALSE
109878Correct LevelFALSE
In the First Unique column (column C) I have formulas. In C2 I have this formula (and then I copied it down):
=COUNTIF(A$2:A2,A2)=1
In cell E2, I have this formula to count unique occurrences:
=SUMPRODUCT(C2:C9*1)
In cell G2 I have this formula to extract unique records (entered with Ctrl + Shift + Enter):
=IF(ROWS(G$2:G2)
I have a long list (>1000) of increasing temperatures in column A. I use an array formula, {=MAX(A3:A1029-A2:A1028)} to find the largest change between two successive values. What I need to do then is obtain the two values that give rise to that difference (for instance, they might be in A678 and A679). I can't figure out a formula for this.
I can manage it if I insert a "difference" column in B, and use:
=INDEX($A$2:$B$1029,MATCH(MAX($B$2:$B$1029),$B$2:$B$1029,0)-1,1)
=INDEX($A$2:$B$1029,MATCH(MAX($B$2:$B$1029),$B$2:$B$1029,0),1)
but I'd rather keep the number of columns to a minimum.
I need to be able to get a macro to do this. I recorded the macro. The conditional worked, but when I cleared it and then ran the macro, it highlighted all of the wrong stuff. Here is what I want to do: In column(AJ) I have cities with Prefixes or not In another column (AV) I have Criteria1 and in yet another, I have criteria2 (CB)
City
(AJ) Criteria1 Criteria2
SCRNRTH_SCRM1 FALSETRUE should fail test
A9_SCRNRTH_SCRM1FALSETRUE
A9_SCRNRTH_SCRM1FALSETRUE
A9_SCRNRTH_SCRM1FALSEFALSE should fail test
SCRNRTH_SCRM1 TRUETRUE should fail test
A9_SCRNRTH_SCRM1FALSETRUE
SCRNRTH_SCRM1 FALSEFALSE
A9_SCRNRTH_SCRM1FALSEFALSE should fail test
A9_SCRNRTH_SCRM1FALSEFALSE should fail test
A9_SCRNRTH_SCRM1TRUETRUE should fail test
A9_SCRNRTH_SCRM1FALSETRUE....................
I have an Excel document which receives data dump from a weather website cells B1:I20. I need the temperature and dew point from cells B3:I3, however the data is returned in a "Temperature / Dew Point" format. To use the data, I separate the Temp and DP into their own cells B22:I23 by:
View 2 Replies View RelatedI'm trying to find a single conditional formula which summarizes multiple text occurrences in a range of cells. I managed to summarize the occurrences in a single cell, but my challenge is that I'm looking for a single (array?) formula for this.
I have a worksheet with column A which contains the condition and column B which contains a text field. I'm looking for a single formula to summarize all the 'AB' occurrences in the cells B4..B11 where the column A is Apple. I managed to do this by using an additional column (column C), however as my worksheet contains over 10000 rows, I would like to use a single conditional (array?) formula summarize the AB occurrences (multiple ' AB's can occur in a single cell) in stead of having to use an additional field per row.
I have attached a sheet as an example. I'm looking for a single array formula in cell C11 which summarizes the 'AB' occurrences in cells B4..B11 where column A is Apple.
I got the following loop that is very slow. The main way (I think) to speed it up is to avoid looking at all the 65000 cells. The columns used in the calculations have data varying from a few hundred cells up to (rarely) dozens of thousands. I assume it would be better if I could replace the 65500 with something indicating the "end" of the data in the column. Also, is there another way to do this process without using a loop?
I am trying to fill in a number of rows with array formula's in the adjucent cells. I use the loop because the number of rows depends on a selection of another parameter which sets the row number before calling the loop.
Lets say I have a column, and i have some dates in that column. between each date entry are blank cells, an undetermined ammount.
ie:
------------
column1
------------
(blank)
1/1/2007
(blank)
(blank)
(blank)
2/5/2007
(blank)
(blank)
3/7/2007
(blank)
... etc ...
I would like some type of array formula, that could
agegrate that whole column into a new column, removing the blank cells.
so the new column would be:
--------
Column2
--------
1/1/2007
2/5/2007
3/7/2007
I was thinking something like... but this doesnt get rid of the blanks...
(this is using google spreadsheet, but ARRAYFORMULA, is the same as hitting "CTRL-ENTER" - in excel.
=ARRAYFORMULA(if(isnumber(A9:A17),A9:A17,0))
I have attached the relevant spreadsheet for which I need to alter the color of the columns based on Site number ( Sheet 1). % Mortality will be represented in the Y-Axis, and the Site numbers would be on the X-Axis. All columns (% Mortality) except one will be of the same color, and the one of a different color will indicate a specific site. As an example, site 86 is colored differently. The way I require the chart to look is shown on Sheet 1.
After reading through some great posts on Ozgrid, I managed to do this using conditional formatting (Sheet 2), but that sort of falls short because I am required to add a data table to the chart, and the parameter that is indicated by the column bars happens to appear twice in the data table.
I was wondering if this can be automated maybe using VBA, but with the possibility of simply matching the color of columns with the font color of respective entry in the data series.
I am looking for a very simple script that will achieve the following:
On the clicking of a button, Select and shade in a cell yellow, delete the yellow shading of the previous cell. The shading & selection should move up a column of cells, 1 at a time, in the following order:
From B10 to B9, then B9 to B8, B8 to B7 etc until the selection and shading is at B2. Once it is at B2 subsequent clicks will simply keep it at B2 (the top). Thus after 8 clicks the shading & selection should travel from B10 to B2, with only 1 cell being shaded yellow and selected at any one time.
I have a spreadsheet that i download from the net daily, which is seperated into columns of information.
I want to be able to look down a column and mark a cell in a seperate column if the cell font text is red.
For example looking down column A ... if the font text of a1 is red then mark the cell background colour of T1 red - if a2 text colour is red then mark the cell T2 red .... etc etc.
If the font colour in a1 or a2 ... etc etc is any other colour then do nothing.
I have 5 columns I wish to look down and mark in 5 seperate columns - I have tried to do this by conditional formating but don't know the fomula for checking font colour.
Say I have 3 columns of data: A1:C10 and I want to run a Match() function on them all together to see if I get a match any one those cells, say the value of have in X1.
Since, Match only allows a One-Column lookup array.. is there a way to "concatenate" or "append" the 3 columns together within a formula so now I would be looking to Match in an array that is 1 column * 30 rows?
Basically want to convert =Match(X1,A1:C10,0) to =Match(X1,A1:A30,0) without moving around the raw data in the sheet.
And I want to avoid doing an AND or OR formula that uses 3 separate MATCH() for each column.
I have a hunch that the MMULT or MMULT/TRANSPOSE functions are involved, but can't seem to get it right.
Code:
Sub TabColor()
Dim mySheets As Worksheets
Dim mySheet As Worksheet
Set mySheets = Worksheets(Array("sheet1", "sheet2"))
For Each mySheet In mySheets
With mySheet.Tab
.Color = 9
End With
Next
End sub
I'm getting a type mismatch at line 4. Can I not group the sheets like this? This is just a test program, my real one would have 10 or more tabs in the array.
I need to have 3 arrays for a column of data, the first holding each cell value, the second the Row number and the third, the Interior.Color.
Currently I am having to assign each row and interior colour to a variable with a | between each then use the split function on that variable to assign it to an array (no problem with each cell value as that can be assigned directly to an array.
Code:
For Each rC In Range("a2", Range("a" & Rows.Count).End(xlUp)).SpecialCells(12)
If sO = Empty Then
sO = rC.Interior.Color
Else: sO = sO & "|" & rC.Interior.Color
End If
If sR = Empty Then
sR = rC.Row
Else: sR = sR & "|" & rC.Row
End If
Next
c = Split(sR, "|")
v = Split(sO, "|")
x = Range("a2", Range("a" & Rows.Count).End(xlUp)).SpecialCells(12).Value
As there are over 10,000 rows involved loading the variables is taking a considerable amount ot time, whereas the direct loading of the value is very fast. So I want to be able to load all arrays directly.
I have tried various methods to get Row and Interior.Color to load directly into arrays c and v but without success so far.
Is there anyway to recreate this formula w/o it being an array ?
{=IF(C3="","",IFERROR(INDEX('Master List'!$B$1:$B$2000,MATCH(TRUE,ISNUMBER(SEARCH('Master List'!$A$1:$A$2000,C3)),0)),"ADD TO MASTER"))}
I just wanted to concatenate a few cells using an array formula like this:
{=SUM(IF(B2:J2="";0;B2:J2))}
Well, this does not work. I have no idea why it fails. Any solution to this without scripting?
i need to replicate what i did using array formulas with VBA macro (array variable). to make things clear and simple i created an example for illustration only. look at it & u will find what i did & what i need to do ,much of it
in writing so that i accurately describe my problem. attached is my example
I am trying to copy or edit a cell thats has the following formula (see below), I keep getting this message "You cannot change part of an Array!"
=If(ROW($A2:$A8)-ROW(A2)+1>COUNT(W2:W8),"",INDEX($A:$A,SMALL(W2:W8,ROW(INDIRECT("1:"&ROWS(A2:A8))))))
I am using Excel 2010 and basically i am trying to fill a range of cell with a green color if any value was enter in a specific cells. Example: I would like to fill range: A10:c13 with a green color (regardless of the cells content in this range) if a value was entered in cell C10 or C11 or C12 or C13.
I've tried conditional formatting but unfortunately I'll have to apply formatting for every cell and for a range of over hundred cells is not efficient.
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 have a similar question regarding coloring of empty cells between two cells with specific numbers as posted in below thread : [URL] .....
This is exactly what I want. But here I have some problem with formula.
=COUNTIF(A1:Z1,1)=1
Above formula could pick cell value 1 and color the empty cells in a row between the cells having value=1. But if I want to use the same formatting for other numbers like 2 or 3 it doesn't work.
For example in row(A1:Z1) I have A1=1, D1=1, and F1=2, K1=2, and O1=3, U1=3
I required multiple conditional formatting that could color the cells based on values first from A to D then from F to K and finally from O to U.
I'm using above formatting it works with cells having values 1 but its not working for 2, 3 or any other cells values.
I have already tried replacing 1 in above formula with 2 and 3 but it doesn't work.
how can I automatically fill cells with certain colors based on the value of the cell. (i.e. I want to search an entire workbook and fill cells with values between 80 and 99 green, 60 to 79 yellow and 0 to 59 red.)
View 9 Replies View Related