I'm trying to hard-set the value of the current cell (i.e. remove formula and keep value) if there is data entered into another cell. =IF(C14="",IF($M$5="",G13*($I$6+1),G13+($L$6/ COUNTIF(A$12:A$200,"<"&$M$5))),Set the value of this cell and remove the formula.
View 6 RepliesI have workbook with multiple sheets with full of formulas. I need a excel macro which can convert formulas to values based on multiple conditions.
The formulas are in B2 and below.This formula provides results as soon as a value is entered in C2 and one among D2 and E2 cells (see attached excel)I want a excel macro to convert the values provided by formula to actual values as soon as results are obtained.Further, when user delete data in C2, D2 and E2, i want the formula to be working again since if a user enters different data in C2, D2, or E2 the value should get updated.
I found a similar post which addresses some part of my problem but works only with one condition. [URL] ....
I have a column called Average and it have numbers from 0 to 100. I want to set a string on another column based on the value of Average. The condition is
< 50 is negative
= 50 is neutral
> 50 is positive
For example
Column A Column B
45 Negative
50 Neutral
80 Positive
I have three columns of Data. A is vehicle number, B is miles and C is a Date. I want to be able to pull the miles for the newest date when the user types in a bus number next to the formula. I think It can be done with an array formula but I am not 100% sure on how to do it. Also is it possible if they enter a vehicle number and a date that a different formula finds the miles for the most recent date to the date entered.
Excel Help.JPG
I have a file to calculate bonus based on productivity. The bonus percentage is dependant on productivity percentage and the category of employee. I have given in the below attached file, the range of productivity% and the bonus % for each category. I want to build a formula, to get the bonus % in a seperate table. I tried to do the same using "If" condition, but its not working, as the number of nested IFs are going beyond 7. I have highlighed the column where I want to calculate the formula.
View 5 Replies View RelatedEvery day I collect 2 values. The data is placed in a row, with this scenario:
Day1Val1, Day1Val2, Day2Val1, Day2Val2. etc.
On any specific day, either Val1 or Val2 can be the larger value.
I want 2 rows, with the following scenario:
Row1:
MAX(Day1Val1:Day1Val2), MAX(Day2Val1:Day2Val2), etc
Row2:
MIN(Day1Val1:Day1Val2), MAX(Day2Val1:Day2Val2), etc
This is for a Min/Max (stock) type chart.
Entering the formulas for the first 2 columns and then extending doesn't work because it single steps column numbers instead of double-stepping.
I already have 5 weeks of data, (and 4 rows: 280 cells) so I don't want to painstakingly do it manually.
We receive payments in either USD or HNL. I want to be able to have a final price (last column) that puts all prices in USD. I have a reference cell on a separate worksheet (drop down inputs) that we will use to store the data for validations and the conversion rate. We use one conversion rate for all transactions for a fiscal year, so only need to update once a year. Not sure how to make this work.
View 2 Replies View RelatedI looking for a macro that will go through multiple sheets & change specific cells to values if the column header is = to value set in specific cell.
for example
I would like the macro to look at row 3 in each tab (page 1, page 2, page 3) and if the value you is equal to X (parameter input on different sheet) then change the formula to a value in row 6 & row 12 of that column.
I'm attaching an simple example that i looking for this on. The green cells are the one i would like to change to a value.
Book1.xlsx
So I have one sheet that needs to pull data through to another sheet (which is a stats summary)
I have a drop down list containing 4 options all of which have to be counted separately on the stats summary sheet. However I only need them counted when a value is input in another cell in that row.
For example: I select option 1 from down down menu, but I only want this to be counted on the stats page when I enter a date in the "date" cell.
I am wanting to paste formula from N1 till N X (X = varaiable row) where X reliant on Column A Row X. If Column A Row X has character "=====" it should paste N1 Formula all the way down till N Row X which is equal to A Row X containing "=====" .
I might be sounding complicated over here but it is a simple equation.. I have tried to approach this in the capacity i could by condition if Column A row x is blank delete the row.
This task joins a string together based on a number of characters per cell in the range.
I want to isolate one range, Col N, and add an IF condition to it.
There may be other issues preventing this from happening, e.g. the number of IF that exist in the complete formula. I will isolate the current cell and its requirements and then post the entire formula at the end for reference....
I want to total column C, but only where column B contains certain data, like add column C where column b contains abd so that i get the answer 7, Hope ive explained it right.
B C
abd 1
abd 2
bmr 3
abd 4
crc 2
crc 3
bmr 2
I want to insert a line wherever I encounter a number. I have a range of cells in column "A" with invoices listed and at the end of each customer I have the total. At the total line (the number) I want to insert a line. Right now I can't figure out how get the cell value to recognize a number from a non-number data type (bolded area)
View 5 Replies View RelatedI would like to write a code, that can check the name of cell A1 with A2.
If A1=A2, then insert a blank row between these two rows.
I have started with a loop, but somehow it does not work.
Sub AddRows ()
Dim Row As Long
For Row = 1 To 3800
If Cells(Row, 1).Value = Cells(Row + 1, 1) Then
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End If
Next Row
End Sub
I want to delete a row in excel based on a condition.
The condition is as follows:
I have few rows in the excel where the columns 7,12,13,23 are same and column 24 in one row has some number and in some other row the column 24 is blank with orange color filled(the colorindex for orange is 44).
In this case I want the row with orange color filled in column 24 to be deleted.
i looking for function, which will give me a results if its the person good , bad and other (Y). It will look based on column A, but if will be more equal names in this column, it looks in the column surname (B) and return value from column (Y) in this row. But it can be more equal column B.
Here is an example:
Results what i want are in Z1,Z2,Z3
X Y Z
1 sue doe good
2 joe bond very good
3 mark no bad
SOURCE table
A B C
1 john doe good
2 joe black bad
3 sue doe good
4 mark black not bad
5 joe bond very good
6 mark no bad
7 kat doe bad
I have a problem with bringing up the data I need to cells in one row.
In row 1 I have dates:
23-Nov 24-Nov 25-Nov 26-Nov etc.
In row 2 Quantities:
2000 1500 250 750 etc.
In row 3 I have dates again (I need to start working towards these qty's): 20-Nov 22-Nov 25-Nov 25/Nov. I would like in column A of 4 row to sum all the qty's (row 2) if the date in row 3 is the same or smaller than in row 1 column A. But in column B and forward I want cells to sum qty's if the date in row 3 is the same as the one in row 1. I have tried : =sumproduct((A3:D3<=A1),(A2:D2)) for cell A4
and: =sumproduct((A3:D3=B1),(A2:D2)) for cell B4 and consequently for others. It comes back as zero value even if I see matching dates/qty's. Does anyone know what shall I change in my formula to make it right?
I have a "if" statement that goes something like this:
=if(and(Function1),do this, if(and(Function2), do that, 0)
where Function 1 and Function 2 consist of three conditions each.
Now Function 2 has reference to a particular cell (U23), whose value is dependent on other cell references.
If U23 has some real value, I want the above statement to be executed.
However, if U23 displays a "#N/A", I want only Function1 to be executed. i.e. = if(and(Function1),do this,0)
I autofilter a list which contains, among other fields, program numbers in column A, account numbers in column B and dollar amounts in column E. I want to display the filtered/subtotal of column E if I select a certain program. Each program has account numbers ranging from 1000 to 8999.
I used =SUM(IF()) formulas to separately display totals with account numbers below 8000, and >7999 with no problem. But when I got to create formulas to display the subtotal, I encountered difficulty.
I used the following CSE formulas to subtotal sum of column E based on the values in column B, the first formula return 0 (zero), which is incorrect. The second formula appears to ignore the IF statement, and returns the total sum instead of the filtered total.
1. {=IF($B$4:$B$112>7999,SUBTOTAL(9,E$4:E$112),0)}
2.{=IF($B$4:$B$112
function that can find the maximum value in a range, where the range is defined by the rows containing a specific string, i.e:
In column A:
Alm Rød
Alm Grøn
Alm Blå
Special Rød
Special Grøn
Special Sort
...
In Column B I have values for each of the above string names.
Now I would like to find the maximum value for the range with names starting with "Alm".
i have an issue i need to delete entire row if a condition is met, i need syntax if possible to delete if a cell word "begins with" a certain letter ...in this case...letter A
column is "O"
must search threw entire row
I need a formula that will return a total for the number of cases that have been closed within 6 months. Also I need a percentage total for number of cases that have been closed within 6 months. So the conditions are that the case has been closed and within 6 months.
I tried to use a IF formula that returned a 1 if within 6 months and a 0 if outside 6 months and then just SUM() the column, however for some reason that just returns 0. I am using Excel2003
Is there a way to filter data based on multiple OR condition with a like parameter.
I have data which has near about 50000 rows and now i would like to filter on a header called "Activity Type" and would like to fetch result for a text which resembles "from ABC group" or "From PQR group" or "From XYZ group".
I'm trying to set it up so that if a particular cell has any data in it, the contents of a different cell get sent to another. I want the no data/data to serve as a trigger.
As an example, if I put anything into cell A3, I want the contents of cell A1 to copy to H1. I'd also like it to be dynamic so that if I put something into cell B3, the contents of B1 get copied to H1, etc.
I need a macro I can run that will insert a row whenever Column A changes (from PPN A to PPN B to PPN C, etc.) and that will insert string values into the Cells as follows:
In all cases, the insert cell values into column A (PPN) will be the value from the previous cell and column B (CPN) will be 'LABOR'
Here's image of before and desired after:
Before:
PPNCPN
4AQ02SLAG
4AQ02SAND
4AQ02CHIPS
4AQ02PORTLAND
4AQ02ADMIX
4AQ02AUTUMN BLEND
4AQ02OVERHEAD
4AQ03SLAG
4AQ03SAND
4AQ03CHIPS
4AQ03PORTLAND
4AQ03ADMIX
4AQ03BETHEL BLEND
4AQ03OVERHEAD
After:
PPN CPN
4AQ02SLAG
4AQ02SAND
4AQ02CHIPS
4AQ02PORTLAND
4AQ02ADMIX
4AQ02AUTUMN BLEND
4AQ02OVERHEAD
4AQ02LABOR -insert row and populate cells here
4AQ03SLAG
4AQ03SAND
4AQ03CHIPS
4AQ03PORTLAND
4AQ03ADMIX
4AQ03BETHEL BLEND
4AQ03OVERHEAD
4AQ03LABOR -insert row and populate cells here
I have a workbook sheet change condition that, when triggered runs the following code (amongst others).
[Code] ......
What I would like is to amend the above (possibly in a loop?) so that it skips the rows in which the value "New" features in range F17:F190.
So basically Autofill as per the code but leave the values in the cells of the range in which New features in range F17:F190
I have table in which against the Column A data values are added in Column B, C and D.
If Column B,C and D cell values is 0, then I want entire row including A to be deleted. How can I do this in VBA?
Note: Column A data begins from Row 15 and below.
Just to avoid confusion. I want row deletion, only when all the three cell on B,C and D is = 0
I have a file in which I have data from A9:I50.
In the column C i have the type mentioned either MTC or SELF.
If the value in column C is SELF then I want to highlight the corresponding rows from A:I.
[Code] ......
This code is colouring the entire row.
I want it to be highlighted from A to I.
I had an report and the rows of the report needs to be colored based on another cell value. My layout looks like this:
A1B1C1D1AVG
1011121330
14151617100
18192021400
For each cell, i need to compare the value of cell with that value in AVG coloumn and if condition is met, cell should be colored. Eg : If first cell value under A1 is Less than 0.2 * AVG of first row then color of the cell A2 should be red. After coloring the entire rows, then i should check and color the header row based on the color of Rows.
I tried first one, i,e coloring the rows based on conditional formatting and then written VB code to compare the color of rows and colored the header row. But the color index used in conditional formatting and the code are mismatching and the desired output is not coming.So decided to write code for coloring the rows cell.
I have a spreadsheet that I need to cut and paste data from one tab to another but only from columns B:K (as the spreadsheet has formulas that can not be alterred after column K. It must also paste that data for the hidden columns.
The condition on the data to be pasted in based on the tabs along the bottom.
So for example, anything that says Corn Products needs to be cut from the "standard customers" tab and moved to the Corn Products tab, and so on for the additional tabs.
I then need to un-check (hide) Blanks from the filter on the Standard Customers tab.
If possible, I would like run the Subtotals function in this same macro; the conditions for that are as follows
For each change in Shipper, Subtotal Billing for <<month>> (Column S)
Page break inbetween each Change in Shipper, then I would also like each subtotal figure bolded.
Also, this is something I must do every month, and each customer may have more or less lines or no lines at all in any particular month.