Applying IF Condition In Excel In A Cell?

Sep 21, 2013

I want to have a condition which will check if the value in cell is present or not and based on that i have to display result in a cell.

Eg: In Sheet1, in column D i have 4 values (Simple, Complex, Medium Complex, Very Complex)

In Sheet 4, we have numeric values to Simple, complex...Like below

Complexity Estimation
Simple 2hrs
Complex 4hrs
Medium Complex 2.5hrs
Very Complex 6hrs

In Sheet5, we have values defined in % like below:

Requirement Analysis



System Testing


In column G of Sheet1, we have to write a function or formula which we do below calculation.

IF (Sheet1!D3="Simple") then Sheet1!G3=Sheet5!B2(ie.15%) of Sheet4

Similarly i will have Sheet1!D3 values as Complex,Medium Complex,Very complex

View 2 Replies


Excel - Applying Color To Cells?

Sep 19, 2013

I am using two separate workbooks. I am tranferring dates into a new spreadsheet to track projects dates for milestones.

The formula I am using is this: =IF('[BBBBB Dates as of 9-9-13.xlsx]Sheet1'!$CQ$4="", VLOOKUP(W54, '[BBBBB Dates as of 9-9-13.xlsx]Sheet1'!$1:$1048576, 94, FALSE), VLOOKUP(W54, '[BBBBB Dates as of 9-9-13.xlsx]Sheet1'!$1:$1048576, 95, FALSE)).

I would like the cell to turn green if the last part of the formula is true and stay clear if the first part of the formula is true.

I also need to add color beyond just that. I was attempting to apply conditional formatting but am a bit stumped. I want the green to be maintained regardless of other formatting. I would also like to apply to cells that are not color coded green:

Red - if the date is overdue
Yellow - if the date is within 7 days
Otherwise, leave the cell color as clear

Is this possible with conditional formatting? I am not at all proficient in VBA...

View 9 Replies View Related

Prevent Excel From Applying Table Styles?

Dec 1, 2011

Is there a way of preventing Exel from Applying table styles, when converting a range to a table. and leave the formatting of the table intact?

View 1 Replies View Related

Excel VBA - Applying Code To Specific Worksheet?

Jul 23, 2013

I have a number of different modules. Each module does something similar, but different to a specific workbook. Instead of running the macro(s) individually is it possible to have a parent-class that calls on each module, and further to have each module *know* which worksheet it should apply to?

Example below:

Sub Cost_Center_Information_File()
'What it does: applied to a worksheet (there are about 15 worksheets in the workbook) it
'will do some formatting manipulations.


View 4 Replies View Related

Excel 2010 :: Macro For Applying Changes To Multiple Files

Aug 16, 2012

Excel 2010, Windows 7

I have a whole ton of *.dbf files (98 to be exact), that are from ArcGIS shapefiles. These date1_date2.dbf files have point names, and values, as shown below.

Point 1-9999.00000000
Point 2-9999.00000000
Point 3-9999.00000000
Point 4-4.93072701
Point 5-8.90071201

I'm trying to figure out how to transpose each of the dbf files and save them as a text file (tab deliminated). My original plan involved saving them all as text files, then using a convoluted Unix script (I'm what you call a "dirty programmer") to transpose them and then combine them all into a single file. Since there are so many files (and I plan to do this in the future as well), I thought if I could at least automate the text file creation, I'd be ahead of the game. Even better would be a macro that will transpose the data for EACH file, then create a 99 (98 files + header row) row, 5 column text file. Creating the text files (1 text file for each dbf file) automatically .

All the *.dbf files will have exactly the same # rows/columns (2 columns, 6 rows)

This is what I've tried so far.
1)open one of the dbf files in Excel
2)record my marco (using the Personal Macro Workbook option - so it will be available in any workbook)
2a) transpose data (and paste it right below the original data so that the new data is in rows 7 and 8 of the same file)
2b) save as text file (tab deliminated)
2c) stop recording, and end up with this:

Point 1-9999.00000000
Point 2-9999.00000000
Point 3-9999.00000000
Point 4-4.93072701
Point 5-8.90071201
NAMEPoint 1Point 2Point 3Point 4Point 5

So now I want to apply the macro I just created to all open workbooks (after I bring in all the bdf files), but the macro isn't listed when I click the "view macros" button. All I want to do is apply the steps I recorded in the macro to all open workbooks.

View 6 Replies View Related

Excel 2007 :: Applying Conditional Formatting To A Dynamic Named Range?

Jul 10, 2012

I have an Excel 2007 Workbook which is refreshed by a Web Query. I have dynamic named range defined as "Manager" which I've confirmed is correctly identified. This range includes only one column and is formatted as text.

When I try to reference this range in my conditional formatting "refers to" formula, all cells are recognized as blank even though the range clearly contains many cells that are not. For example, there are 90 records containing the initials "PD". If I use the formula =Manager="PD" in my conditional formatting, nothing changes. If I change it to =Manager="" then ALL records are formatted, including those that are not blank.

Stranger still, if I enter the formula =COUNTIF(Manager,"PD") into and empty cell in my worksheet those 90 records are counted correctly. Which leads me to believe it isn't about the data. Conversely, =COUNTIF(Manager,"") returns the correct count of only cells that are, in fact, blank.

I've tried using the OFFSET formula defining my range in place of the name itself for my conditional format formula to no avail.

View 2 Replies View Related

Excel 2010 :: Condition To Create New Cell Value On Several Rows

Apr 5, 2014

I am working on a schedule worksheet, the days are the columns and the staff member names go down the rows. I want to create a new cell value based on a condition on each row. I have been successful working with one row but how to efficiently do the same for each row.? I'm working with Excel 2010.

View 9 Replies View Related

Excel Vba To Copy Positive Number And Paste Negative On Cell Condition

Dec 4, 2013

I am using the following code to update an invoice summary sheet from an invoice sheet but when a Credit Note is issued I would like the Nett� value copied across to be a negative figure. When a credit note is issued the value of Service Invoice Cell L2 = 2 and an invoice L2 =1.

Sub UpdateSummary()
' Updates Summary Sheet with Invoice Details
Dim InvoiceDate As Date, InvNo As Integer, JobNo As Integer, Company As String, Location As String, Nett� As Currency


View 4 Replies View Related

Applying Colorname From Another Cell

May 3, 2006

I have cell A7 Merged and need info in that cell. Example of what I want: Say I click on cell A1 and it is the color Pink, but has the letters blue in it. I would like it to display the word "Pink" in cell A7 not the number. Well I would like when you click on any cell with colors, that it will display the color in Cell A7. When I try different ways all it gives me is the number in the last cell of the square not the color and when I click on other cells it will not give me that cells info.

Private Sub CommandButton1_Click()

Dim x, rng As Range, r As Range
Dim myList
Dim row1 As Integer

x = Application.InputBox("Enter size of square: 2=2 by 2, 3=3 by 3, or 4=4 by 4", Type:=1)
Set rng = Range("a1").Resize(x, x)
myList = [{1,2,3,4,5,6,7,8;6,11,3,10,13,16,38,53;"Brown","Pink","Grey","Purple","Green","Red","Blue","Yellow"}]
rng. CurrentRegion.Clear

For Each r In rng
x = Int((8 * Rnd) + 1)
With Application.WorksheetFunction
r.Interior.ColorIndex = .HLookup(x, myList, 2, False)
r.Value = .HLookup(x, myList, 3, False)

Range("A7").Value = r.Interior.ColorIndex

End With


With rng
.ColumnWidth = 10
.RowHeight = 50

With .Font
.Size = 14
.Color = vbWhite
.Bold = False
End With

.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders.Weight = xlThick
End With

End Sub

View 9 Replies View Related

Applying Multiple Formatting To A Cell?

Dec 2, 2011

In Excel we can apply different formatting styles to the "TEXT" contents of a cell while we are in edit mode. For example we can apply "BOLD" to Brown Fox and "UNDERLINE" to lazy in a string "A quick brown fox jump over a lazy dog.

But when the above string is concatenated from the contents of various cells, excel is unable to apply different formatting (as exampled above) in the concatenated cell. Although we can apply bold, underline etc by selecting the concatenated cell but it is applied to whole string and not to the selected parts of string.

Sheet1 *A1Brown2Fox3Lazy4*5A quick Brown Fox jump over a Lazy DogSpreadsheet FormulasCellFormulaA5="A quick "&A1&" "&A2&" jump over a "&A3&" Dog"

Moreover, MS Word's "mail merge" is capable to apply different formatting to different "Fields" in a single paragraph.

Is there any way so we can apply multiple formatting to the contents of a single cell which is not "Text" but result of "concatenate" in Excel, like MS Word's mail merge or as it is applied in custom format within straight brackets like [Red] etc.

View 3 Replies View Related

Multiple 'IF' Formulae Applying To A Cell

May 2, 2006

is it possible to have more than one IF function applying to a cell eg. if value in cell A1 is greater than 10 then Positive is written but if value in cell A1 is between 8 and 10 then Suspect is written.

View 2 Replies View Related

Cell Equals 0 Changes When Condition Met - Leave Unchanged When Condition False

Mar 24, 2014

I am trying to perform a calculation in a cell that leaves the value 0 if before Feb 2014, changes to the value of another cell in Feb 2014 and leaves the value unchanged if after Feb 2014. I can get to the Feb 2014 value but after that month the value changes to 0.

The Cell formula that I am trying to use is as follows:


A3 contains TODAY()

H114 is the cell being calculated

AA118 is the cell containing the calculated value for the current month.

View 2 Replies View Related

Applying Formula In Cell Y/ Tab Y Only If Data Is In Cell X / Tab X?

Feb 4, 2014

I have a Excel file with three tabs on it: 'Raw Data', 'Edited' & 'Upload Ready'. As you can probably guess, you dump raw data in the first tab, it pulls the second one where it is 'edited', which then goes through to the final one which contains pre-populated information required on each upload.

Now I'm not great with Excel, so when I get the formula to work as required in H2 (Edited tab) I just drag it down to HX as needed by the number of rows used in the Raw Data tab: View image: Excel drag

Is it possible to apply the formula used in H2 in H3:HX only if there is data entered in A1:AX in the 'Raw Data' tab? ... or rather I'm sure it is possible (I would guess using the 'IF' function') but I can't work out how to do it and then apply it to the entire column.

View 5 Replies View Related

Extracting Consecutive Letters From Cell And Applying IF Function

Mar 8, 2013

I'm working on an email database and am looking to sort them into type. I am trying (without success) to use the IF function as follows.

If A1 contains the letters "isd" consecutively, then B1=TRUE

If A1 contains the letters "google", "yahoo", "hotmail", or "austin.rr" consecutively, then B2=true

View 4 Replies View Related

Excel 2010 :: After Applying A Data Filter And Sorting The Data / How To Revert Back To Original

Dec 20, 2012

I'm using Excel 2010 and I applied a Data Filter to a simple table. I then messed around with the drop downs in each column, sorting the data by different criteria. After doing this, is there a simple way to get the table to revert back to its original order/form?

View 3 Replies View Related

Add If Condition Excel Formula To VBA

Mar 24, 2014

I have written this code to add a column to the data. but it is giving application defined error how to resolve it??

[Code] .....

View 4 Replies View Related

Excel 2007 :: VBA - Add New Rows If A Condition Is Met

Feb 16, 2014

how can I write a routine to add rows in one Sheet if a condition which involves a different Sheet is met (excel 2007).

In Sheet 1 is a list of ("liquid") names (cells C4:C26) which will need to be expanded should the IF condition be satisfied. In row 27 I have a subtotal (whihc is using data from columns D onwards), and from row 29 down cells are not empty.

The condition I want to verify is in Sheet 4-column I, where it is reported whether a name is classified as "liquid" or "illiquid". If the number of "liquid" names in sheet4-column I is more than the number of names in Sheet1-C4:C26 I would like to add as many rows as the difference between the two lists. Also I would like to make sure that the subtotals that I have in row 27 (and that will be shifted down when new rows are added) will also inlcude the data in the new rows (the subtotal is taking data from columns D onwards).

Should the code need to inlcude the name of the tabs, Sheet 1 is called Summary and Sheet 4 CDS Data.

View 1 Replies View Related

Summing Fields In Excel If Condition Met?

Aug 23, 2013

for all of column H that are equal, I would like to sum the values in column B, sum column C and Column E...

so that it would appear as:

View 1 Replies View Related

How To Color Excel Cells In Condition

Jan 10, 2014

I want to color cells if it is texted as ULF by green, if it is texted as OS by red in the all row down, it easy but I can't.

View 2 Replies View Related

Excel Formula To Pull Data With 1 Condition

Jun 14, 2014

Looking for formula to extract data from the raw data table list to a new table list when an option is selected from the dropdown list.

Sample data attached and desired outcome in green color.


Product 3

ProductTargetOff TargetTarget Range

View 3 Replies View Related

Excel 2010 :: CountIf For Given Month And Given Condition

Mar 20, 2014

MS Excel Version : 2010
Attached File name : countif for a given month.xlsx

I want to a sum the given condition xxx / yyy according to the month in yellow cells.

View 6 Replies View Related

Prevent Saving Of Excel If Certain Condition Not Fulfill?

Feb 7, 2014

I want this statement translated into a macro.

Do not save file year.xlms if total of column N in sheetR does not match with total of column O in that sheet. I also want a msgbox if this happens.

View 4 Replies View Related

Excel 2003 :: How To Use List Or OR Condition In SUMPRODUCT

Oct 30, 2009

How to include a boolean OR in my SUMPRODUCT formulas.

Software: WinXP SP2 and Excel 2003 SP3

I'll try to type in some data in legible format:

......Col B.....Col C.....Col D.....Col E.......Col F
1...NW OH....WMA.....110%....NW OH...(result)
2...NW OH....SIS.......130%
3...SO FL......PRB........92%
4...NW OH....TRO......104%

I want to sum the contents of Column D when Column B = a given text value located in Cell E1 AND Column C = text value #1 OR text value #2 OR text value #3, and plunk the result in Cell F1.

So for example if I have NW OH in Cell E1 and my values to match in Col C are WMA, SIS, or PRB, I need a result of 240%.

I am using dynamic ranges for the contents of Columns B, C, and D: CALC_AFFIL, CALC_TITLE, and CALC_PERC respectively.

I have tried:

Also tried both the above formula w/o the "--" in front of any array.

Again, the above was tried with and w/o the combinations of "--" in front of arrays.

Once more with and w/o "--" combinations.

View 7 Replies View Related

Excel 2010 :: Plotting Values V1 / V2 / V3 And V4 According To Given Date And Condition

Mar 25, 2014

Excel Version : MS Excel 2010
Attachment filename : <Forum to plot the values.xls>

My requirement is i want to plot the values V1, V2, V3 and V4 from the given condition (A1 / A2 / A3 / A4) and the given date.

If the given date is not available then the formula should take the older date than it not next date. For example if the given date is 10-Mar-14 and the date is not available for the given condition then it should take the older / earlier dates like 09-Mar-14 or 08-Mar-14 like and it should not take 11-Mar-14, 12-Mar-14.

The values should be plotted in the yellow cells.

View 1 Replies View Related

Excel 2010 :: Condition Formatting On Column Of Text?

Jul 22, 2014

I have a column of numbers that are already formatted as text. Some of the data is duplicate which is fine. It's like a list of 6000 numbers. I want to conditionally format the column so that I can fill the cell with a color as long as it stays the same number, but if it changes to a new number make it a different color. I don't need a lot of colors. Two is fine. TI have Excel 2010.

View 4 Replies View Related

Search For Keyword With And Condition And Return To Different Sheet Within Excel

Jul 22, 2014

I've got an Excel document with over 323 entries and I need to search the data to find where ever a certain keyword is mentioned and copy the entire row into a separate sheet within the same workbook. However, I have a list of around 323 keywords and there is AND condition as doing this using Ctrl+F would take some time.

The data is in Sheet "Training Dataset" and the particular column that needs to be searched is Column "A" . "Training Dataset" is the sheet where I would like the found results to be copied to in the column B and C. The list of Keywords that need to be searched for are located in Sheet "Keywords" , starting from Cell C and D.

View 12 Replies View Related

Excel 2003 :: Copying Row To Different Sheet Based On Condition

Feb 16, 2013

I wanted to know if it was possible for me to be able to copy a whole row into a different sheet based on a column value. For example, if B6 = CLOSE, the whole row would be coppied to the sheet name CLOSE. If B6 = OCCUPIED it would be copied to sheet name OCCUPIED. I also need if W7 = to a date thats passed today it would be added to the sheet name EXP PRD. An off topic question, i have this code for column "W":


It works just fine, but I also have "N/A" / "INDEF" in the blocks as well so it's not giving me the correct results. I'm using Excel 2003.

View 8 Replies View Related

Excel 2003 :: Search Condition And Replace Program (VBA Coding)

Nov 12, 2011

I have two work sheets where I have data.

Sheet1 contain daily input table which as follows:

Namesalary Bonus Check
XX1000 2 FN
XY900 1 NA
YY1100 2 FN
ZY1500 3 DP
ZZ1250 2 FN
AA1050 2 NA
AZ 950 1 FN

Sheet2 have table where all the information is saved. So we can say this is database of sheet1. Which store every day information of sheet 1.

Name SalaryBonus

What I want to do is that ---it copy all the data which fullfile condition FN and move to the Sheet 2 which is salary database. Two important things to check is that. IF name already exists in the sheet 2 then it replace old info with the new one. Second thing is to sort the whole table(Salary database-sheet 2) according to A-Z (Name column). I use office 2003. I hope I provide all the information.

View 9 Replies View Related

Cell Formatting - Red Text If Cell Matches Certain Condition Based On Another Cell

Sep 24, 2013

I have one column that contains a monetary amount (column AQ) , and another that contains text reading either "inflows" or "outflows" (column AC)

When AC says "inflows", AQ should be positive, and when AC says "Outflows", AQ should be negative.

I need the text in column AQ (the monetary amount) to become red when the the opposite is true.

i.e. When AQ is negative and AC says "Inflows", AQ should become red. And when AQ is positive and AC says "Outflows", AQ should become red.

View 2 Replies View Related

List The File Name In One Excel Sheet, If Condition Is Met In 1000 Closed Workbooks

Nov 19, 2008

I have 1000 workbooks in a folder, each workbooks format is same.
as of now I open each workbook, and check if values in A10 is equal to B10, that is if the value in A10 is "100" I will check if B10 is also "100" if not I will make a note the file name.

I have more than 1000 workbooks in a folder, It is really difficult to open and check if the values are same. is there a macro, which will check the condition A10 = B10, if not get the file name in one sheet.

View 9 Replies View Related

Copyrights 2005-15, All rights reserved