# Add Red X In Cell Based On IF Function

Dec 10, 2009
I have a numerical value of in cell B3. A user will type a value into cell C3. If the user enters a value other than the value in B3, I would like a space and a red X to appear just after the value they enter, in red. (The value is black; the X is red.)

Example: 100 X

View 9 Replies
ADVERTISEMENT
Dec 8, 2009

I am trying to return a TRUE or FALSE based on a date in a cell.

for example:

if cell A1 = 07/11/2009

I want A2 to show TRUE or FALSE if A1 is 14 days or more behind todays date.

I have tried stuff like:

=IF(A1=TODAY()-14,"True","False")

but it just always says false. EDIT: I have just noticed that if I change the date in A1 to exactly 14 days behind todays, it returns TRUE. So, it does work. Do I need to add a GREATER THAN in there?

View 6 Replies
View Related
May 8, 2014

I'm trying to obtain a result based on the number of characters in a cell. If the cell content is blank or contains 9 digits then leave blank.

Any other result must provide the outcome of "incorrect number supplied".

I've tried the following formula but it doesn't work.

View 3 Replies
View Related
Sep 5, 2013

A brief rundown on what I am trying to create. My family and I have a NFL football game we do every year to see who can correctly guess the most winners over the course of the season and then the ultimate winner receives a prize at the end of the season. After years of doing this manually, I decided to try and put my Excel skills to use and create a more "automated" worksheet.

After some heavy research, I was able to successfully create a user defined VBA which would calculate a "win" every time a cell is highlighted (in my case the winning team's name). For example, I highlight New England Patriots and when the formula calculates each person receives 1 in their win column.

However, I have now reached a problem I failed to think of. The formula does not take into consideration the picks everyone is going to make. Not everyone is going to select the winning team, so the current formula I have is giving credit for every highlight. Is there a way to add an if statement to this? For example, if person A selected New England Patriots and it's highlighted then give 1, if not give 0?

I know it's usually hard to base a formula off a cell color.

For some reason, I cannot attach the spreadsheet I am currently using. For a brief overview, cell M1 contains the color which I would use to highlight the cells in the range A2:H17. This formula is in cells B21 - B27.

The VBA which I input was:

Function CountCcolor(range_data As range, criteria As range) As Long Dim datax As range Dim xcolor As Longxcolor = criteria.Interior.ColorIndexFor Each datax In range_data If datax.Interior.ColorIndex = xcolor Then CountCcolor = CountCcolor + 1 End IfNext dataxEnd Function

Where the picks which are made should be logged. Whether each person gets a separate color and the VBA is based on that, or like I alluded to above if there is an X here and it's highlighted or something.

View 1 Replies
View Related
Mar 10, 2014

I am working on creating a simple Excel Database of an Engineering Tool Room Inventory for work. However I can not seem to get one particular 'NESTED IF Function' to do what I want it to do. (In advance warning, I may be going down the wrong route and might have used the wrong Formula in Excel but from my understanding the 'IF' function seemed the most plausible.

Issue: All I would like to do is for a NESTED IF Function to be able to say this:

1. When a user inputs data into one column, for example 'Column A', with the following data: 4" Square

2. Then the neighbouring column, for example 'Column B', would have a NESTED IF Function.

3. Which would output a logical response, "A, B, C, D, E, F etc...", depending on what is written in Column A.

If 'Column A' - 4" Square, Then 'Column B' - A

If 'Column A' - 5" Square, Then 'Column B' - B

& So on...

But the NESTED IF Function should only put a logical response if there is certain values within the cell, for example:

User types a value into 'Column A' which contain - 4 & S, Then 'Column B' - A

User types a value into 'Column B' which contain - 5 & S, Then 'Column B' - B

& So on...

Currently to do the above function, I have utilised the following Excel NESTED IF Function:

[Code] .....

& The following happens:

1. User inputs data into 'Column A'

2. The NESTED IF Function then provides an automatic response.

3. However it relies on the data being inputted without the ' " (Quotation Symbol)'

4. & With me defining the exact phrase that should be typed into the cell, for example, "4" Square"

Other: I have attached an example worksheet of the NESTED IF Function in practice, with two tables. One showing what is happening with the code above and one table showing what I would like to happen but with no function.

Example - IF Function.xlsx

View 3 Replies
View Related
May 15, 2014

Here i am having functional code for checking 4 cells data based on conditions

1) if more than 2 cells is empty means the entire row has to delete-how to make it?

2) i want to fill the empty value in relevant cell at the time of execution of function i.e.

for example cell1 cell2 cell3 cell4 cell5

value value blank cell blank cell result(coming based on my condition but the empty cells or not filling)

how to fill the empty cell

I am attaching my file for reference : if_custom.xlsâ€Ž

View 3 Replies
View Related
Sep 14, 2006

how to run a macro from an IF function, if the function is true macro 1 runs if the function is false macro 2 runs.

View 2 Replies
View Related
Jan 8, 2009

I'm looking to create a macro that will take data from an input sheet, and paste it into a cell in another worksheet, based on criteria specified in sheet1.

Specifically in the attached example, the macro would copy the data in cells C8:C10 of sheet 1, then paste them into sheet 2 based on the data specified in cell B3 i.e. it would paste them into the column headed Mar-09. I intend to make this cell a drop down, so that the user can then select the next reporting month and run the macro again to paste the data into the Apr-09 column.

View 2 Replies
View Related
Jul 24, 2014

Looking for a formula to accomplish the following:

I'm trying to populate cell A31 on a worksheet titled "VolumeTotals" with the data in Cell E23 from a worksheet titled "CurrentCustomers" if the merged cells F3-F22 on worksheet "CurrentCustomers" are equal to the word "Contract".

View 6 Replies
View Related
Mar 20, 2009

Another interesting dilemma to solve. Using this formula:

View 2 Replies
View Related
May 14, 2013

Function Haversine has correct value in debugger but in cell it has the same value as Haversine2. Is this a known bug?

Public Function Haversine(lat1 As Double, long1 As Double, lat2 As Double, long2 As Double) As Double

Dim temp As Double

[Code]....

View 9 Replies
View Related
Jul 17, 2008

I have created a List in excel of various tasks. (See attached example.) Each row contains one cell with a function which equals the cell above it and to the right. So the function for cell C3 would be D2. The only importance is that the cell mirrored is always the cell one above and to the right. However, now I would like to be able to rearrange this List, but the “one up and to the right” function will now be all over the place.

Is someone aware of a function which will target a relative location instead of an exact cell?

View 13 Replies
View Related
Mar 2, 2008

I have been looking around have this much code from this site, modified. What I am trying to do is calculate numbers by a position with cells D through K having numeric values. I have 'hardcoded' the cells (D2, E2, etc in the code below), but in reality I only want the current row (so if the formula is on the 2nd row, I want D2, if it is on the 3rd row, I want D3).

My problem is obviously the formula isn't working because I am not correctly tying back to the spreadsheet (Positioncalc.xls). When I put the formula in the spreadsheet it works, but in my script I get 0 everytime.

My Script:

Function Position(rCell As Range, Optional RightPosition As Boolean)

Dim vResult

Select Case rCell.Text

Case "QB"

vResult = (2*D2) + (2*E2) + (2*F2) + (4*G2) + (2*H2) + (1*I2) + (4*J2) + (3*K2)

Case Else

vResult = "Invalid Position"

End Select

If RightPosition = True Then

Position = vResult

Else

Position = "Position not valid"

End If

End Function

So, when I put =Position(A2,True) I expect to see the formula results of those cells calculated based on the position (QB, HB, etc with their unique formulas).

My next challenge after this is to highlight certain cells based on the Position. So if A2 = QB, I want cell D2 boldface and Red, etc. I have seen some scripts on colors and such here, so I might be able to figure it out.

View 9 Replies
View Related
Jul 21, 2009

The following is my existing formula (I admit it is probably very far from what it should be) but I'm looking a way to base the conditional part of a function based on the text within a cell. =IF(E4=OR("WordA","WordB"),(I4),(-1*I4))

There are four words that I use to classify the column "E" for sake of simplicity I will name them as "WordA" "WordB" "WordC" and "WordD". Those words serve as the identifier that I am trying to test. I.E. if "WordA" or "WordB" are present in column E, I want the end result (listed in column I) to be a positive number. If "WordC" or "WordD" are present in column E, I want the end result listed in column I to be a negative number. Currently I have a formula in column I, which is: =((H3-G3)/G3).

Both Column H and G have numerical values. Essentially the whole goal of my process is that if WordA or WordB show up in E, then I want my formula in I to remain "as-is". If however, WordC or WordD are present, I want the end result or formula in column I to be negative.

I am not tied down or restricted to using a particular formula, but just don't know how to set it up either way. Additionally, I plan to copy the formula down in terms of numbers... i.e. I5,I6,I7, etc. so when submitting any advice or if supplying a formula to paste.

View 3 Replies
View Related
Jan 20, 2010

I have random descriptions that have the case dimentions within the text

The descriptions are NOT standard length.

the only thing I can think of is the dimensions in ALL descriptions have this in common:

Length ( x ) Width ( x ) Height

I am looking for the Height value

How can I use the =mid function based on the 2nd ( x ) character?

View 9 Replies
View Related
Oct 13, 2009

On sheet 1 I have a list of 1000 firstnames

On sheet 2 I have a list of 1000 emails,

I need a function that states If a cell in the email column contains a string or value from the names column, it will result in a true statement so that I can separate out the emails that have these peoples first names.

View 5 Replies
View Related
Aug 10, 2014

I'm working on a spreadsheet that I need to return a value to "Unit Price" field in worksheet "Master Inventory" based on matching the "Product" field and the "Construction" field from the "Unit Pricing" worksheet.

In essence, I would like the "Unit Price" field to match the "Product" field from the "Master Inventory" sheet to the "New Product Description" field on the "Unit Pricing" sheet, then match the "Construction" field on the "Master Inventory" sheet to the column headers on the "Unit Pricing" sheet and return the value that corresponds to both criteria.

Ex: On the "Master Inventory" sheet, I would like the "Unit Price" field to match the "Product" (Book Browser) to the "New Product Description" (Book Browser) on the "Unit Pricing" sheet and then return the value where the "Construction" (Laminate) matches the column header (Laminate) on the "Unit Pricing" sheet which would return the value of "$240.00".

I've tried using a vlookup function, vlookup/match function, index/match function and an index/match/match function. I've attached a sample workbook.

View 3 Replies
View Related
Oct 14, 2008

I need help on using the If function. Basically I am trying to calculate the cost of shipments. For example, cost of sending 1 box is 150 (eg cell A3), 2 boxes is 280 (cell A4), 3 boxes is 400 (A5), 4 boxes is 500, etc upto to 10 boxes (cell A12).

What I am looking for is cell A1 to provide me the cost based on the manually entered value of cell A2, i.e. if I enter a number between 1 to 10 in cell A2, then A1 should return a number from A3 to A12, e.g. enter 3 in A2 then A1 should return 400.

Is there such a formula or do I have to enter manually the amounts into an If function?

View 11 Replies
View Related
Sep 5, 2013

copying data from one sheet to another if it meets a certain criteria. I have a long aged debtors report that has several different cost centres and I want to split it out on to separate tabs for each centre. I want a function or marco that will go to row 1 and check if it equals the cost centre name on the other tab in a cell and then copy it and paste it in that tab, if not then it moves down to the next and the next until it gets one that does equal it and then copies it again.

View 1 Replies
View Related
Aug 1, 2009

I have 20 sheets in a work book (1-20) with similar row details(80 rows)...but the columns have amounts for 12 months. I would like to do a sum if function based on months chosen.

For eg: If I select "sheet 1" and choose month as june...I want the sum of amounts from Jan-June.

View 9 Replies
View Related
Apr 22, 2006

I would like to create a UDF similar to the SUMIF function but the UDF would be dynamic in that it would sum based on a dynamic range that would change based on the row the user is in.

background: I have a very large input template for 12-18 months for multiple cost categories. I would like to have the UDF in a specific cell above each category that would show the user what the sum of the range in the current row they are inputing data. Each row represents a specific project/task (along with 12-15 descriptive columns) that makes the freeze pane option unusable.

View 5 Replies
View Related
Jul 29, 2006

I'm trying to find the best Macro or Formula to get this done.

Sheet 1 has a list of salesperson in column "A" and the total numbers they made on Monday (in the same row, column "E"), Tuesday (In the same row, column "F"), Wednesday (In the same row, column "G"), Thurday (In the same row, column "H"), and Friday (In the same row, column "I"). I also have a sheet for each individual salesperson. I need to find a Macro or a Formula that I can use to give me the average of the numbers if "A5" = the salesperson in Sheet 3 then avarage the numbers from "E5" to "I5" and I want the results to show up in cell "D35".

View 9 Replies
View Related
Jan 27, 2008

i have the formula "= sum(A1:A10)" in cell A4 & would like to call macro when the value changes. The code i'm using below work's if I manually type in a value, but isn't working with the formula.

Private Sub Worksheet_Change(ByVal Target As Range)

' Checks for cell value change

If Intersect(Target, Range("A4")) Is Nothing Then

Exit Sub

' If cell value change, calls msgbox based on criteria

Else

If Range("E4").Value = "C" And Range("A4").Value > "30" Then

Call MsgBox1

End If

If Range("E4").Value = "F" And Range("A4").Value > "38" Then

Call MsgBox2

End If

End If

End Sub

View 4 Replies
View Related
Aug 17, 2014

Supposing you would like to find the median of numbers in column D, but only for rows that have the word "Jones" in column A? Is there a way to do this? Obviously I know I could do a sort and simply specify the range myself, but we're dealing with nearly 2000 rows and a LOT of different values in column A

It occurs to me that it would be even better if I could ask excel, in effect, to identify every separate text string in column a and then find the median of the corresponding numbers in column D. In other words I wouldn't have to type any strings from column A at all.

View 2 Replies
View Related
Jun 23, 2014

As you can see in the attached excel doc, I have calculated a column for "Number of Claims Per week" and also "Dollar amount Per claim". This was done using VLOOKUP and distributions. That is not the issue.

The issue I am having is creating the "TOTAL" for the week. So for example, if week one generates 4 claims, I need to be able to add up claim amount #1-#4.

It seems as though this would be a basic function, but I can seem to find a way to add up a certain number of cells, based on a variable generated from a random number.

If you take a look a my excel sheet, I am trying to solve for the "Claim Total" column on the far right. This column should be the sum of (Number of claims * The individual amount per claim). Note that if there is 4 claims in that week, I would have to add up claim 1-4 to get that weeks total.

Question2.xlsxâ€Ž

View 9 Replies
View Related
Jul 6, 2007

I'm a graphic designer and i'm trying to build a spreadsheet that will help me in doing my accounts.

Ok, so i'm trying to set up a function that will generate a price based on three fields:

Lamination:YES or NO

Duplex: YES or NO

Stock: Around 14 different stock options.

I had it working using a nested IF but I ran out of the 7.

I then tried using CONCATENATE using "&" to seperate but excel said the formula was too long.

View 14 Replies
View Related
Mar 28, 2012

I have huge set of data listing almost 20 columns and 400 rows..i am trying to apply a formula for one of them..

Condition: Onshore or Offshore

Data to be retrieved: list of locations at onshore and offshore

Result expected: If a particular cell is selected as onshore then allow user to select only Onsite locations same for offshore

View 1 Replies
View Related
Apr 10, 2012

If the percent in a cell is more than 10% less than another cell I would like it to turn red. I would like the cell to turn yellow if the percent in that cell is within 10% below the percent in another cell, or if the percent is greater than that of another cell I would like the cell to turn green. How do I do this?

To clarify; If cell a1 is 20% and Cell a2 is 21%, I would like the color in cell a1 to turn yellow since it is less than 10% below cell a2. OR if cell a1 is 25% I would like it to turn green since it is greater than cell a2. OR if cell a1 is 10% I would like it to turn red since it is more than 10% below cell a2.

I've looked at conditional formatting and it does not seem to apply, this seems like it would need an if function combined with conditional formatting.

View 3 Replies
View Related
Jun 7, 2013

I am trying to write a code in excel vba where I need to match specific text in multiple columns and if text is matched then I need to insert new blank column after the specific text.

My code is like this.

Dim Col_num As Integer

Col_num = Application.WorksheetFunction.Match("Customer Defined 06", Range("6:6"), 0) + 1

Columns(Col_num).Select

Selection.EntireColumn.Insert

Cells(6, Col_num).Select

This code is working fine in single condition but when I enter another condition then its giving me an error i.e Type Mismatch.

View 9 Replies
View Related
Jul 21, 2005

in the lookup function listed below, it only produces a result for the first exact macth. well, what if the data i am searching on could result in multiple results?

i would like advise on how to modify this formula to bring back the 2nd, 3rd, 4th, etc instance of a match in the lookup array.

the original formula , i found on this site, allows you to combine multiple cells to create a unique reference, and then search in another table containing columns of those combined cells, and results in the intersection of a row and column and that is the data you want returned ....

kind of like a hlookup, but you can use one or more cells to create the unique lookup, and bring back whatever column you want (where as a hlookup is limited to searching the left most column and bring back a column to the left)

here is the formula:

{=INDEX(data to retrieve,MATCH(cell1&cell2,table to find cell1 in&table to find cell2 in,0))}

"data to retrieve" is an array of one column containing the data you want to retrieve

"cell1&cell2" is a combination of cells to make the unique lookup (this could just be one cell if you wanted)

"table to find cell1 in&table to find cell2 in" is a table array to look in to find the look up value

View 9 Replies
View Related
Feb 3, 2008

Ihave 2 functions which are called in my main program which should return a value due to what the user selects on a userform using option buttons and check boxes.

Function getnum() As Integer

If userform1.OptionButton1.Value = True Then

getnum = 1

End If

If userform1.OptionButton1.Value = True Then

getnum = 3

End If

If userform1.OptionButton1.Value = True Then

getnum = 5

End If

End Function

Function getlevel() As Double

If userform1.CheckBox1.Value = True Then

getlevel = 1.2

End If.......................................

View 3 Replies
View Related
Jul 30, 2014

Function to select two columns based on the header and the variable given.

Sample excel file attached for your ref.

Excel sample.JPG

Find the excel file in which I am looking for an formula which will look up variable in Col A for Eg USD and search the same in Row 1 and then will select COL D:E and so forth for other currency.

View 4 Replies
View Related
Aug 3, 2014

What I am trying to do is have a drop down list in one cell and have the next cell bring in the correct price for the item in the first cell. Sounds fairly easy? Well both lists are 200 items.

I have been trying to do this with just three items so far. On sheet two, I have created two lists. The first list in column A contains dogs, cats, mice. The second list in column B contains 1, 2, 3. On sheet one, I have created the drop down list for the animals in the A12 cell. I would like for B12 to auto populate the corresponding number to the animal without having to create an if formula for each animal and number.

View 4 Replies
View Related
Feb 27, 2014

I have two columns in a spreadsheet that I want to use an IF function with. In the first column, I want a pick list containing 5 text options. Depending on which option is chosen, I want a number to auto-populate in the second column.

The numbers are important, because I want to repeat this pair of cells with slightly different text values in the second pair, and then to have a column that multiplies the two resulting numbers and conditional formats the result.

View 14 Replies
View Related
Nov 1, 2008

Indicate that a user-defined function can only be based upon the calculations that can be placed in a single cell. If you have too many calculations to put them into a single cell, e.g., an entire page of calculations based upon a few starting parameters that eventually yield a single value, then how do you reuse this entire page of calculations?

Is there another Excel mechanism that allows an entire page of calculations to used as a stored procedure?

View 3 Replies
View Related
Sep 9, 2012

I have following data to sort/filter

Sector

Flt no

origin

[Code]...

Is this possible with excel functions?

View 1 Replies
View Related
May 31, 2006

I want to calculate a weighted average but I need it to only calculate on the months to date. I have a data validation drop down on a title page that is toggled to the current month each time a report is needed to define what months have actuals. I bring in data for all the months but only the current months have actuals. I need to calculate the weighted average on ONLY the ACTUALS. How can I set the ranges for sum product based on the data validation list on the title page?

View 4 Replies
View Related
Jul 7, 2006

I am trying to find the starting effective month for a workyear. The criteria for the selected month is that the data BEFORE the effective month is all zeros. I manage to get the result if the data AFTER the effective month is ALL non- zeroes. If there is any zero, the data fetch will be inaccurate due to the COUNTIF formula. Is there any other way or formula that will enable me to get the result. File attached for testing and reference

View 6 Replies
View Related
May 7, 2007

I'm trying to make a spreadsheet that will count the number of times a certain incident occurs, for a particular person, for a particular month. The attached spreadsheet is an example of what I need done.

For the attached spreadsheet, I need to find out how many times x employee has been late for x month, and how many times they've been late overall.

You can see one of the many tries I've attempted in the second sheet, but it doesn't seem to want to work. I have to be able to do this without VBA, because of signature issues.

View 9 Replies
View Related
Sep 22, 2009

I have the following info in a table:

name | doc date | doc value

a | 2009/01/01 | IN111

b | 2009/02/04 | IN222

c | 2009/02/05 | IN333

a | 2009/01/05 | CN111

d | 2009/03/01 | IN444

b | 2009/03/01 | CN222

a | 2009/04/01 | IN555

Firstly, I need to find the LAST DOCUMENT DATE for "a" where #doc_value starts with IN*** (invoice). Manually, I can see that it would be "2009/04/01", but my spreadsheet runs 6000+ entries. I need a function to do this.

Secondly, I need to find the corresponding #doc_value for that date (in this case, IN555).

View 9 Replies
View Related