In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?
let me start by saying that I know an example workbook would be useful here, but the part I'm struggling with is the [managementroster.xlsm] file, and there is A. no way I can release it to the internets and B. its so huge/complicated I couldn't even begin to reproduce a portion of it, scrubbed of data, and hope to maintain its functionality in a meaningful manner.
[Code]....
This formula checks a staff number on this spreadsheet, and then goes and looks at the staff number on the roster. Once found, it returns that staff members roster, but changes any manager codes in the MRC list to Mgr, and changes all other roster codes to Free.
I now need this formula, before altering roster codes to Mgr or Free, to only return codes that are a match for another table (or after really. I don't particularly care, so long as only codes are shown that match data from another table). I think an index/match function would do the trick, but this forumula is already at the edge of my excel ability, nesting another function within it is completey beyond me. The relevant cells for the index/match function would be:
This first Match function targets the column. $E3 is the date required, $BA$1:$DN$1 is the range the dates are entered in Match: Lookup value = $E3 Lookup array = '[ManagementRoster.xlsm]Vacancies!'$BA$1:$DN$1 match type = 0
This second Match function targets the row. $A$4 is the department name, $B$434:$B$452 is the range where all departments are entered
Match: Lookup value = $A$4 Lookup Array = '[ManagementRoster.xlsm]Vacancies!'$B$434:$B$452 match type = 0
Index: array = $BA$434:$DN$452
So I think my final function is
[Code] .....
But I have absolutely NO idea where it would fit within my first formula, or how to code it so that my original formula only reproduces results that are found in both sheets, or anything.
I would like to use conditional formatting to highlight the max value if the constraints are not violated.
For example, Row 91 - Profit Row 94 - Constraint A (750<=A<=855) Row 95 - Constraint B (12<=B<=17) So, I tried to type this in conditional formatting but it didn't work >< =MAX(IF(AND(B94>=750,B94<=855,B95>=12,B95<=17),$B$91:$Q$91,0))
I would like a date in a cell to flag up in a colour (say, red) once the date has expired by a certain period (e.g 7 days). How can I do this (conditional format).
I have an "actual build" worksheet, which allows the user to select which materials were used, material properties (e.g. material grade etc.), including its corresponding length. The user can enter all these in manually, or select the material name from a pre-defined list (Reference worksheet contains this information), and then the associated properties are automatic lookups from the Reference worksheet.
So all this is fine / completed, and the LOOKUP returns its theoretical length from the reference table. However, its actual material lengths will almost never match the theoretical length, so what I want to happen is for a conditional format to highlight the cell, to notify the user that they need to enter a manual value. The auto-looked up length is still useful a proposed build on the worksheet, but having it highlighted to ensure it is manually entered later is the objective.
The other thing is it can't just check if it's a formula, it has to check if it is a formula with a MATCH/LOOKUP function. E.g. it needs to be unhighlighted if someone has a basic formula, ie = 2*0.42 if there two of the item etc..
Hence, I was thinking of having a conditional formula that "format only cells that contain" and then format only cells with "specific text" and "containing" and "MATCH(" text function, however this doesn't seem to work.
If you try to use this it will not work, as excel takes only 7 arguments can someone help me with this. Do any one know macro for the same.
if you use this formule it will work =IF(E18>760,(">760"),(IF(E18>550,("550-760"),(IF(E18>365,("365-550"),(IF(E18>210,("210-365"),(IF(E18>120,("120-210"),(IF(E18>90,("90-120"),(IF(E18>60,("60-90"),(0))))))))))))))
The background to this question is that I'm trying to replicate conditional formatting using a user VBA function, because I require a thick border around the cells (and the conditional formatting within Excel only has thin borders)
In the final function, there will be 9 combinations of formatting {Red, Amber, Green} interior with {Red, Amber, Green} thick borders.
The formatting is determined by a number in another cell (the "target" cell), which returns a value 0,...,8
I've only got as far as filling in the interior for the first combination, but the function returns an error "Application-defined or object-defined error".
VB: Function VBA000_003_SetRAG(strTargetRange As String) 'strTargetRange is the reference for the target cell that contains the value 0,...,8
On Error Goto handler [code]....
There is not a problem with the target range, if I remove the two lines relating to rngCaller then the function works OKExecuting the rngCaller.Interior.Color = RGB(255, 0, 0) command through the Immediates window works OKI've tried calling a subroutine & passing the range across
Same errorChanging the range in the subroutine to an absolute range (eg. Sheet1.range("A1")) also causes the
same errorExecuting the subroutine on its own (with the absolute range) works OK
So my guess is that it's a bug/limitation with Excel VBA when trying to execute commands from inside a user function
When I hit the macro code you see below I get both columns D and E, starting from row 18, to get 'filled down' to the specified spot. Every second row has a conditional format (when a value is entered in column A) to change the row to the color grey, and every row between it has a conditional format (when any value is entered in column A as well) to have the row changed to the color red. The issue here is that the Macro code messes up the conditional formatting and uses the conditional formatting of those two cells, which are being dragged down, for those entire filled-down columns! This is what I am starting off with test1.xlsm and this is what I end up with using the macro code below (or doing it manually)
test2.xlsm
Is there a way for the Macro code to bypass this issue?
Fragment marked in red is currently missing and if I want to use it, I would be nesting too many if functions again. To describe the situation: Row 41 is staff available for task, sometimes there is nobody doing the task therefore function will force value 0 in all variances if there is no staff available.
There are also occasions that despite having a number of staff allocated to do the task, nothing’s been received due to a various reasons, this is where I have designed a function calculating hourly variance against what was received against the planned target for this hour. When there is no staff – everything will be 0 When nothing’s been received – target for the hour will be negative. When there was something received – the difference between last receiving figure and current one, minus target for an hour will provide the hourly rate, but If nothing’s been received for the last couple hours, it will go to the last hour when something was received and calculate hourly rate basing on the difference between current figure, last available figure and target for an hour. And here we are at the heart of the problem – in the last cell I cannot use same function as I would have to nest too many “if” functions, therefore it does not include receiving figure in E44 at the moment.
I have this spreadsheet I'm making for Christmas to keep track of my wifes and my purchases. I'm having problem with a formula tho and I'm not sure how to go about this. Basically I have a "name" column. As you will see. At the bottom I have "total spent on cristina" "total spent on jon"
I need like an "IF" condition that will look at that name column and say, "if name is Jon" add up the amounts in the "total cost" column according to what Jon spent. If name is cristina, add up her amounts.
I want to apply conditional formatting in a cell based on the contents of another cell. I can do it if I just want it based off 1 condition (=b151="sample") but would like to if it met 1 of a couple possibilities.
For example, cell H151 I would like to fill green if cell B151 contained the text "sample" or "example". B151 has a list box with about 10 possibilities but I only want a few of those to turn H151 green.
Is it possible to write a formula that will compare the text in two different cells? And if I can do this can I also have that formuls place a numeric value in another cell? An example of what I am trying to do is: If the text in C2 is equal to the text in C3 place a 1 in C4 or if the text in C2 is not equal to the text in C3 place a 0 in C4.
How do I perform average for a range and be able to exclude outliers. For example I am taking 100 measurements, some are not measureable and recorded as 9999.9. How do I average 100 values, but exclude all the 9999.9?
I imagine this is a simple conditional SUMIF function. I'd like a cell to add values in e.g. column "d" when that row meets certain criterion in column "a".
In other words, I have a column that has times recorded in minutes, and another that says a person's name which correlates with the times. I'd like a cell on another sheet to give a total sum of minutes for each person.
Ideally, part of the function would translate the minute count into hours/minutes, but I think I can figure out how to do that by changing the format in the cell...
I am having many difficulties getting conditional formatting using the match function to work correctly in my excel document.
What I would like to accomplish is as follows in the 3Q09 tab. If a subdivision name is found in C-62 through C-70 and a match is found for the subdivision name in AB-24 to AB-66 I would like it so the AB-24 to AB-66 Subdivision name is bolded for every match. I removed the function formulas from the 3q09 tab I used to have since they didnt work and I do not want to confuse anyone.
This is correctly done on the "Working Correctly" Tab included in the same file, so please view this for clarification if you need it. Why it works on one tab and not the other simply baffles me.
for my thesis i need to create median industry multipliers. i have a list of companies with their industry codes and multiples, but now i need to create a list with medians per industry. is there a function similar to SUMIF for medians?
Currently trying to calculate the cumulative performance for a range of months. For example, I have the below figures:
Jul-12 6.59%
Aug-12 1.13%
Sep-12 2.61%
Oct-12 -3.56%
[code].....
I want to create a product if function where if I'm looking back 6, 12, 18 months etc from a specified month (e.g. Sep 2013), it will calculate the product for the range of % (e.g. Apr-Sep for 6 months, Oct 12-Sep 13 for 12 months, etc...). I would imagine it will be =product(1+if(Column A range=6, Column B range))-1.
I'm new to excel and am attempting to use an IF(AND function that requires the use of two LOOKUP tables for the IF conditions, I have been unable so far to generate the answer I'm looking for.
I have a set of data in two columns, column A which has a series of dates from 1/09/2013 - 30/09/2013 (multiple for each date), and column F which has a series of 4 digit numbers (employee numbers). I also have two columns where I can enter in values for LOOKUP tables, one column for dates (O2:O4) and another for employee numbers (Q2:Q17).
What I am trying to achieve is a True/False answer in a final column for IF A2 = value from column (table) O2:O4, AND F2 = value from column (table) Q2:Q17. For the querry to be true the values in column A and column F must meet both conditions, be from a date in column O2:O4 and also be an employee from column Q2:Q17
Currently I get a #NA error and I know its due to my formula not being anywhere near correct, as far as I can tell it's a result of the LOOKUP part of the formula I'm attempting to create, as it generates a #NA area when the data does not meet the criteria in the columns (tables), I have attempted to include a ISNA section to the formula to alter the result if the data does not meet the two conditions but when applied to the whole data set it only responds with a False answer even if the data meets both conditions.
I behind developed to a time a function of Addition with an only conditional criterion. I would like to extend at least for three criteria, this function I function accurately as the function SUMPRODUCT alone that done in VBA.
Function VlookupAllSum(name As String, IntervalSearches As Range, IntervalReturn As Range) As Variant ' as integer para valores até 32.767 Dim Valor, Nome Dim lin, col As Integer Dim Total Application.Volatile lin = 1 col = lin For Each Nome In IntervalSearches If Nome = name Then Valor = IntervalReturn(lin, col)................
I have 7 sheets named: joe, kelly, meg, nate, dave, tom, sam. To keep it simple, in cell B2 on each sheet there is either an "8" or it's blank. On an 8th sheet I'm trying to determine a formula to use that will display the person or persons if Cell B2=8. So if just meg's B2 has 8 and tom's B2 has 8; the formula would produce meg & tom.