Applying One Formula To 2 Columns

Feb 8, 2014

So right now I have a formula that I'm using in column "C":

=IF(B4="","",IF(AND(B4=22),"IN RANGE","OUT OF RANGE"))

So basically column C tells you if the number in the B column in within the range of 22-30. Now I want to write the formula in column D and make it apply to columns B and C.

So cells in columns B and C will have a number value in it and I will make column D where the formula is. There will be a number in B or C, but never at the same time. I can't figure out how to alter the formula to do this.

View 3 Replies


ADVERTISEMENT

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 Same Formula To Entire Row

Feb 20, 2012

I have an excel sheet where I do a simple price-discount calculation for a product. Please check the file at:

Code: [URL] ........

A2 shows the price, B2 shows the discount and C1-L1 shows the number of units sold. C2-L2 is where I want to calculate the earnings based on the formula I have in C2. I want to apply the same formula to the entire row so that I can quickly see the earning for any number of units sold.

View 3 Replies View Related

Applying Formula To Drop Down List?

Feb 14, 2013

Cell B1 is to display money value dependent on what item has been selected from drop down box in Cell A1

View 2 Replies View Related

Applying Formula To Many Cells That Already Contain Values

Jun 8, 2014

I want to apply a simple formula to a number of cells in an existing workbook that already contain values E.g.

My column contains the values
5200
1600
4376
in separate cells

I want to divide each value by 1.2 - without having to change every individual cell - can it be done?

View 2 Replies View Related

Applying Formula In A Column For New Rows Inserted?

Jul 23, 2014

I've applied a basic multiplication formula =D12*E12 in a table Column F, also I've filled it throughout but now if new rows are inserted in between or at the end of the table, new cells in this columns doesn't include this formula.

View 1 Replies View Related

Applying Numerical Values To Letters In Formula

Feb 28, 2007

I'm putting together an Environment Threat Assessment for work and want to automate as much of it as I can to alleviate the amount of time ppl have to spend putting values in. My main problem is I have three very important columns: Likelihood, Success Rating and Risk Rating. Likelihood and Success Rating values will be selected from the following:

VL - Very Low
L - Low
M - Medium
H - High
VH - Very High
Each of these is given a value: VL = 1, L = 2, M = 3, H = 4, VH = 5.
The Risk Rating is the result of multiplying the Likelihood and Success Rating. So a Likelihood of Medium and a Success Rating of Low will give a Risk Rating of 6. Therefore, the cells on my spreadsheet would look like this:

Likelihood Success Rating Risk Rating
M L 6
The issue is I want a couple of things to occur. Firstly I want keep the values in my Likelihood and Success Rating cells as letters (ie. VL, H, VH etc) rather than numbers, while still generating a number result in the Risk Rating cells. Secondly I'd like to be able to change the colour of the Risk Rating cell to reflect the number it gets assigned. For example Green for < 8, Amber for 9 – 14 and Red for 15 – 25. I've been trying to do this with array formulas and am basically going around in circles with no success.

View 3 Replies View Related

Applying Formula To Dynamic Number Of Rows For Different Files?

Feb 20, 2014

I'm new to creating macros and trying to create a macro that will perform a formula that I can use for multiple files. However the files are a varying number of rows.

I want the formula to carry out on the entire column (all rows). However when I record the macro (I tried using relative and also tried using absolute references when filling the formula) the formula always seems to stop filling wherever my original file's rows stopped. I wrote the formula for the first row and double clicked it to fill the remaining rows.

For example if the macro I recorded in File 1 goes to row 15, when I run the macro on File 2 which is 20 rows, the formula stops filling at row 15.

View 8 Replies View Related

Applying Formula To Entire Column Without Lengthening Spreadsheet?

Jul 29, 2014

I'm wondering if it's possible to have a formula applied to an entire column without adding unnecessary length to the spreadsheet until someone is actually scrolling down and using more rows. I want formulas to be constantly present in two columns to automatically calculate values as they are entered into other cells in the same row, but ideally without the otherwise empty spreadsheet being X amount of rows long.

I initially had the formula dragged down 15,000 rows, but it would really be much better if it could start out as a smaller size and then expand as the user requires.

View 9 Replies View Related

Applying Formula To Highlight Cells That Don't Follow Standard Format

Feb 4, 2014

I'm trying to find ways of applying a formula in condition formatting to find entries which don't follow a certain format.

to be clear, this is for a reg plate of a car. There are two styles of formats. The X's represent A-Z and # represents numerical values

1. XX##XXX
2. XX####

find a way of applying two test cases (non-simultaneously) so that cells highlight if they don't follow either of the above conditions?

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

Counting Formula (compare Values In 2 Separate Columns To See How Many Times The Same Value Appears In Both Columns)

Oct 13, 2008

I'm trying to compare values in 2 separate columns to see how many times the same value appears in both columns. Ideally I would be able to insert a range function to compare the values in the column "ID 1" against the values in column "ID 2" and return the count of times that a value appears in both columns. For example 2122, 1112 and 1718 appear in both columns and I would like the formula to return a count of 3.

ID 1ID 2
12342122
45671112
89101718
11122678
13144544
15162324
17189987
19201215
21221928
1976
2576
2345
4678

In my actual project I'm comparing 2 columns in the same worksheet. The column are column B with data in cells B2:B10266 against column C with data in cells C2:C18560.

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

Skip X Columns In Formula Reference When Dragging Across Columns

Nov 15, 2009

How do you make the Autofill function increment in blocks, e.g. instead of increment 1,2,3 increment 1,8,15 inthe following example: I have a 2 worksheets, one with days of the year across columns and values down rows, and another that summarises the sum of the corresponding days in weeks, so I have 365 columns in sheet 1 and 52 columns in sheet2. Everytime I update the sum range i cannot autofill it in the summary worksheet, as A1:G1 autofills to B1:H1, but I want it to increment to H1:N1.

View 3 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 ANTICIPATED
= 10% is AWARDED
>10% <90% is IN PROGRESS
>90% is COMPLETED

[code]....

View 8 Replies View Related

Applying Date Range

May 18, 2009

To explain I have two sets of merged data. On one fixed column I have two different sets of peoples age

1 - by age grouip eg 25 - 34
2 - By birth year so 01/02/1983

I know that person who is born between 1975 and 1984 would fall into the age group 25 - 34

I want to convert all the birth years into age groups. Its a lot of data (30,000 rows +) in random order

Does anyone have any idea how I would set this up, as I really want to add "age group" to a pivot table I have.

View 6 Replies View Related

Auto Filter Not Applying?

Feb 25, 2013

Using VBA in a code. I was using this same code for another workbook before and it worked fine.

[Code] ...

The filter works, but it is not applied. after the macro is run, I need to click "OK" on the filter for it to apply.

View 7 Replies View Related

For Each Command Not Applying To Other Worksheets

Jul 8, 2013

My issue is my For Each command is not grabbing the next worksheet and applying my code. Here is my code:

Sub Test()

Dim ws As Worksheet

Worksheets("Report").Activate

For Each ws In ActiveWorkbook.Worksheets
With ActiveSheet.PageSetup
.LeftFooter = "&D"
.CenterFooter = "Test"
.RightFooter = "&P"
End With
Next

End Sub

View 5 Replies View Related

VBA: Applying The If Statement To All Cells

Mar 3, 2008

I use the If - Then statement in VBA to determine a condition which works perfectly fine for the particular cell I reference to, but i need it to apply to all the cells i.e. cells A1:A10?

Sub MACRO1()

If Range("A1") = "PAYE" Then
Range("B1").Formula = "=C2 * .128"
End If

If Range("A1") = "LTD" Then
Range("B1").Formula = "=(C2 - 100)* .128"
End If

End Sub
Sam

View 9 Replies View Related

How To Specify The Sheet Name For Applying A VB Code

Aug 22, 2009

have a code for restricting printing until some cells are filled; however the code is been applied to all the file. I need to apply it to one one sheet.
Thanks for your help.....

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet
If Application.WorksheetFunction.CountA(.Range("A11:K11"), ("A13:K13"), ("A16:K16"), ("A19:I19"), ("J18:K18"), ("A22:K22"), ("A25:K25"), ("B63:B64")) < 8 Then
MsgBox "Please Complete Information"
Cancel = True
Else
'Allow printing
End If
End With
End Sub

View 9 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
Randomize

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

Next

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 A Mid Function To Many Rows

Jun 14, 2007

I need to cut off addresses by 30 characters. I am doing this using the MID function. However, being a newb, how can I apply this function to say D4, D5 all the way to D899, without copy and pasting for 800 times? I did search this first, and couldn't find a resolution.

View 4 Replies View Related

Use Sumifs Function Applying 2 Criteria?

Aug 21, 2013

Excel.xlsx

Attached is the example of the data, I am trying to sum the amount applying 2 criteria, within a particular month and other category. The formula tried is

=SUMIFS(D5:D10,C5:C10,"*June",E5:E10,H1)

However it is giving a value of zero only. I have tried other combinations as well.

View 4 Replies View Related

Applying Macro To Selected Range

Apr 3, 2014

I have a macro which sorts data within a cell. This is working perfectly fine but the only problem with this is that it works only for a single which is selected. How can I apply this macro to range of cells I select using a mouse.

View 3 Replies View Related

Applying Macro To Entire Spreadsheet At Once

Feb 6, 2009

For simplicity, let's assume I have three columns, "Company," "Policy," and "Balance."

Example:
Company A Life $1
Company A Med $2
Company A Dent $3
Company A Disab $4
Company B Life $9
Company B Med $5
Company B Dent $7
Company B Disab $2

According to my post preview, the columns are running together, but I do have it arranged in three distinct columns. What I want to do is program a macro to do the following:

Every time the name of the company changes, I want Excel to insert two rows after the last of the group, and have a summation of the "Balance" column in the first inserted row.

This is what it should look like:

Company A Life $1
Company A Med $2
Company A Dent $3
Company A Disab $4
$10 <- (this should be directly under the $4, but I'm not good with forum languages)

Company B Life $9
Company B Med $5
Company B Dent $7
Company B Disab $2
$23 <- (same with this - should be directly under the $4)

View 3 Replies View Related

Applying A Percentage To A Long Column

Jan 9, 2009

I have a long column of numbers that I want to apply a single percentage to all the numbers in each column. How do I do that?

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

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

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

Design
20%

Coding
20%

System Testing
15%

[code]....

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







Copyrights 2005-15 www.BigResource.com, All rights reserved