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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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
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
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
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
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.
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.
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.
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)
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.
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...
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 :
