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


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

Copying Formulae Not Transposing Cell Range

Jul 21, 2006

I have a macro which inserts rows at certain places in a data table. This works fine. I then want to copy the formulae from the the row aboove or below (dependent on certain criteria). This sort of works.

I have is that when the row is copied, the row numbers within the formulae do not change relative to the new row. Eg if I copied down "=A6" one row, the formula still says "=A6", rather than "=A7".

I have tried variations of

Selection.PasteSpecial Paste:=xlPasteFormulas

I also tried the AutoFill method (but this didn't work at all when I tried it with a whole row)

Previously, I would have selected ranges and then pasted on the active sheet. However, I am trying to re-educate myself in writing macros that do not 'select' ...

View 5 Replies View Related

Applying IF To Multiple Cells

Jan 9, 2014

I am trying to update a project tracking sheet at work and I am having trouble. I have a list of construction projects (approx 130) along with details on the project, one detail being % completed. I am trying to create a summary of the projects at the bottom of the page, showing the total value of all projects, according to the % completed.

= 10% is AWARDED
>10% <90% is IN PROGRESS


View 8 Replies View Related

Applying Same Formatting Across Multiple Sheets

Aug 20, 2007

There is currently data on each sheet in my workbook. I am trying to get a macro to Copy the formatting of sheet "Statement" and paste the formatting on all other sheets in the workbook, except Sheet.actual

There is also a picture @ the top of sheet statement, that i would like copied and pasted to each sheet except sheet.actual.

Also, on all sheets except for STATEMENT & ACUTAL, i need the formula in cell C11 =vlookup(F14,cardnum,2,false) and in cell A11 = Name :

View 9 Replies View Related

Applying Macro Code In Multiple Sheets?

May 23, 2013

i have a macro code but i don't know how to apply it to all sheets in the same workbook

my code is

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Range("D5:D100"), Target) Is Nothing Then
Target.Offset(0, 1).Value = Now() [code]....

View 1 Replies View Related

Applying Formula Across Columns For Multiple Rows?

Mar 3, 2014

I have a formula that I wrote in B2 and then applied vertically to a large list of rows (100+). I applied it vertically by simply double-clicking the cell with the formula, and it cleanly applied the formula to every row in Column B.

This formula now needs to be applied horizonally across columns, for each respective row. I need a way to do this in one swoop, as it would be very time consuming to manually drag each cell in column B.

I've attached a sample spreadsheet with a simple formula just for the sake of an example.

View 3 Replies View Related

Applying Conditional Formatting To Multiple Cells?

Apr 25, 2012

If you have a range of cells with values in them (Q2:AZ2), you can use conditional formatting to identify the top x% of the group and it would essentially treat each cell in comparison to the group. I need to identify if a cell in the group is greater than 3*Stdev(Q2:AZ2). Do I have to create conditional formatting for each individual cell or is there a way to apply a function to the group that would recognize each cell the same way that the canned conditional formatting rules work?

View 1 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

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

How Do I Prevent "#VALUE!" Results In One Cell From Preventing Other Formulae Dependi

Mar 2, 2009

I'm using an IF formula – BV2 =IF((OR(BQ2="yes",BU2="yes")),"Yes","No") - which just shows "#VALUE!" whenever BU2 shows "#VALUE!". Is there any way round this so that, if BQ2 showed yes and BU2 showed value, BV2 would say yes?

View 9 Replies View Related

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 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

How To Validate A Formulae

Jan 30, 2009

I have a database on one sheet and a 2 count if formulae recording information on the next to be exact one formulae counts the number of monthly values and the other count yearly values. I want the sum of these formulaes to be equal or less than 25. and to show an error if the sum of these is mor than 25.

View 9 Replies View Related

Adding Values Not Formulae

Apr 30, 2009

I am slowly working my way thru Excel succesfully but have come up with another one I can't find an answer for..... YET

I simply want to subract the value of two cells, however the values of those cells are produced by formulae. Hence I get the #Value! error.

Once again I know there's gotta be a simple solution, I just can't locate it

View 9 Replies View Related

Convert All Formulae To Values

May 7, 2008

I need to add a piece of code to a spreadsheet that will turn all formulae within into values. The number of tabs in the spreadsheet is likely to change over time but I want the code to apply to all the tabs - without needing to change it if a tab is added/ deleted.

View 3 Replies View Related

Evaluate A 'dotted' Number Using Formulae (not UDF)

Apr 12, 2006

Is there any way to 'evaluate' a dotted number (similar to an IPv4
address but not just those) using standard worksheet formulae (ie no
UDFs, VBA, or ATP functions)?

To make it a little more complicated, I need to be able to evaluate
not just IP addresses (although that would be a use) but also a
'number' such as 9.0.2 compared to 8.7.5 so that they can be sorted
and / or compared.

The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:

This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address.

The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:


View 10 Replies View Related

Formulae That Applies To All Sheets On Opening

Jan 14, 2008

is it possible to have a sheet that i can type in formulae and the formulae will automatically copy itself into the same cells on any new sheets i open?

View 13 Replies View Related

Formulae To Find Last Used Row In WorkSheet: Aladin's UDF

Sep 11, 2007

I found this post in the archive section. Its purpose is to find the Last Used Row in a Range, "A:A".

Using it, I get a #Name? error. I have placed the UDF in a module, placed the formulae in K1.

I have mixed data from A1:A34.

Function Used(r As Range) As Range

Dim q As Range
Set q = r.Parent.UsedRange.Cells(r.Parent.UsedRange.Cells.Count)
Set Used = Intersect(r, r.Parent.Range(r.Parent.Cells(1, 1), q))
End Function


View 9 Replies View Related

Macro With Array Formulae Has Knocked Me Down

Jul 31, 2008

Have this workbook with over 34 worksheets. Exactly 31 sheets have over 700 rows & 5 columns of array formula. The cell references for these formulae are on "Sheet2" and the values in Sheet2 get populated after the macro is executed for Sheet1.

The macro when run, takes helluva time to complete and populate the values in these 31 worksheets. If I turn off the calculation, it throws out a an exception and on the top of it I have noticed sometimes that the computer hangs itself and becomes DEAD. The only option I got is to press the Reboot Button.

The same macro when run on less than 100 rows, works like a treat ! Could someone help me resolve this high density problem, the Excel way (I am not in a position or a situation right now - to use ADO Recordsets to rectify this problem and neither do I know how to use it). So if your suggestion asks me to use the ADODB then unfortunately it will not be helpful to me at the moment

I have tried by all means and now I am stressed out.

Sub try()

ActiveCell.FormulaR1C1 = "=R[-1]C"
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

View 9 Replies View Related

Extract Portion Of String Using Formulae

Apr 11, 2009

If I have a string of the form:


I would like to extract the ABC term, i.e. the term that liues in between the 2nd and 3rd "_" in the Workbook name (in the above example "Test_ABC_2009_v2.xls")

View 9 Replies View Related

Stopping Auto Updating Of Formulae

Sep 20, 2006

Everytime that I insert a row into a worksheet which a number of formulae refer to, those formulae get updated automatically in such a way that it is not in my favour.
The formulae refer to rows 2:2000 on a number of different columns. Adding a new row shifts the references to 3:2001. I presume that I can stop this from happening


= SUMPRODUCT(('List of DD Donors'!E2:E2000)*('List of DD Donors'!F2:F2000='DD Tally'!E6)*IF(DATEDIF(('List of DD Donors'!G2:G2000)-1,DATE(2006,3,31),"m")>12,12,DATEDIF(('List of DD Donors'!G2:G2000)-1,DATE(2006,3,31),"m")))

changes to...

=SUMPRODUCT(('List of DD Donors'!E3:E2001)*('List of DD Donors'!F3:F2001='DD Tally'!E6)*IF(DATEDIF(('List of DD Donors'!G3:G2001)-1,DATE(2006,3,31),"m")>12,12,DATEDIF(('List of DD Donors'!G3:G2001)-1,DATE(2006,3,31),"m")))

View 4 Replies View Related

Referring To Formulae In The Choose Function

Jun 27, 2007

I have a number of different formulae I want to invoke according to a choice. I am able to request the formula I want by entering them ‘in line’ as value1, value2 etc into the CHOOSE formula … e.g. =CHOOSE($H$1,IF(N3>O3,1,""),IF(N3<O3,1,""),IF(N3=O 3,1,””))

but this is cumbersome and confusing since I have a growing number of choices and some of the formulae are quite long. I want to enter them in a list and extract the formula which corresponds with the Index_num in the CHOOSE formula. I also need the cells in the formulae to be relative so this rules out using NAMES to refer to the formulae.

View 4 Replies View Related

Macro To Calculate Formulae Stored As Text

May 8, 2008

I would like a macro which works on the selected cells:

A number of the selected cells contain formulae with an apostrophe in the beginning.

I need the macro to remove the apostrophe and calculate the formula in those cells..

View 9 Replies View Related

For Loop For Changing Formulae Based On Other Sheet

Jul 27, 2006

I want to write a For loop so that it checks the range G2:R2 of Sheet1 for the first non-zero cell in that range. When it comes to the first non-zero cell, it will change the formula of A2 on Sheet2 to have the SUM of that non-zero cell and the next two cells to the right. Example:

G2 = ""
H2 = ""
I2 = ""
J2 = "3"
K2 = "4"
L2 = "8"
M2 = "9"
N2 = "2"
O2 = "5"
P2 = "3"
Q2 = "11"
R2 = "7"

A2 = SUM('Sheet1'!J2:L2) --->"15"

View 5 Replies View Related

Use Values In Adjacent Cells In Formulae Reference

Nov 24, 2007

I'm trying to write a function that returns, from a different sheet, the value in column C and row 6*current row.

For example, if the function is typed into cell B5 in Sheet 1,
the resulting value would be whatever is in cell C30 in Sheet 2

I tired the following but it gave me an error

View 2 Replies View Related

Force Formulae Recalculation & Return Day Name From Workday Function

Feb 27, 2008

I'm trying to make a schedule of deposits made for the month of March 2008. (Deposits are made on a daily basis.) With this, I wanted to know when the deposit would clear with the bank using the WORKDAY formula given that I have to count 6 banking days after the date of deposit (Saturdays and Sundays excluded).

I'm using Mac and I recently upgraded to Office 2008 (never tried WORKDAY formula in other versions). I am aware of the syntax used for this formula and it works just fine in other cases. But I noticed that with start_dates falling on a Thursday or Friday of the week, Excel would give me a result date that falls on a Sunday, which is odd given that it's supposed to ignore non-working days or weekends. (I haven't even gotten to inputting holidays yet.)

View 6 Replies View Related

Sort Formulae Returning Nothing ("" Empty Text) To Bottom

Mar 26, 2008

I have a sheet with a reference field that I want to sort. Each cell in the reference field has a formula in it, which specifies that the cell remain blank unless there is a value in the cell. The values are either text values or number values. e.g. =IF(ISERROR(S4),"",VLOOKUP(C4,'Rider Data'!A:G,6,FALSE))

When I sort the field in ascending order by the column that contains the formula above (using a macro written for this sort procedure), where the expected value is a text value, all the blank cells are sorted ABOVE the cells with text values in them, rather than below, even though they are blank cells. However,if I sort on a different column which contains time values or numerical values, the sort function works just fine. I have tested the VBA code that produces the erroneous sorting and confirmed that it happens only when sorting on columns that contain text values. I would like to sort text values in ascending order with blank cells BELOW the sorted cells.

View 3 Replies View Related

Applying Percentages

Jun 17, 2008

I have a table of data with names across the top and number of weeks down the left. The table lists amounts of money those names earned during the respective weeks. It looks something like this:

Mike Dan Bill Carl
400 500 600 700
800 900 1000 800
700 600 500 400

In a seperate worksheet within the same workbook, I have 3 columns of data. The first column is a name. The second column is a criteria. In the third column, I am trying to look up the value of 8 specified weeks of earnings of the name I put in column 1 and multiply those earnings by a percentage based on the criteria in column 2. Then sum the products.

I have a table of the 8 percentages I am trying to apply. There are 7 different percentage applications.

So if I am looking up Mike's earnings during those 8 specified weeks and the criteria in column 2 is "B", then I want to apply the appropriate column of percentages to those 8 looked up earnings, Multply them and then Add them.

Up until now, I have been trying to use some hybrid of HLOOKUP, SUMPRODUCT, and IF.

View 9 Replies View Related

Copyrights 2005-15, All rights reserved