I am trying to create a simple IF/THEN statement to display profit margin for an order form. I currently have the margin formula set at (1-H14/I14). How should I structure my IF/THEN, to where it displays nothing in the cells, that are empty?
View 5 RepliesThere are two worksheets: Finances and Summary. On Finances, there is data input for years, quarterly. There is a cell on Summary that depends on which year is input first, in which case the cells in the formula SUM("cell1:cell4"))/4 is currently changed manually by the user by just checking to see which year data is input first on Finances. I need a macro or a formula function where the workbook finds which year is being used on Finances first, then changes the cells in the Summary formula accordingly.
View 6 Replies View RelatedBelow is the code. It seems to be creating 50 thousand rows below the data already entered in the worksheet "DELPHI DATA". What I need to do is change it to only add those formulae or pasted values to as many rows as already have data entered in them (which may eventually approach 50000, but may remain at only a few thousand.)
Sub Refresh_Current_Month() ...
This is just a sample worksheet. I have got a worksheet with having 3 coloumns A, B & C. Column A contains E Code, while Column B is of time which user will enter. Column C contains the time in Hours.
I have entered one record for example. Now, whenever user enters the value in B3, then formula from C2 should be copied to C3 i.e it should be =B3/60.
I want this to be done using VBA. Pls help me out. I want to use this feature in one of my another files which requries this feature.
I found the following code here.
For data entered into column A, it copies the formulas from columns B:E in the row above to the current row.
It works great except fot the first row (A9) where it copies the header row (B8:E8).
How can I get it to not copy when data is entered into A9?
Private Sub Worksheet_Change(ByVal Target As Excel. Range)
Dim c As Range, i As Long
On Error Resume Next
Set c = Intersect(Target, Columns(1))
If c Is Nothing Then Exit Sub
If IsEmpty(c.Offset(-1, 0)) Or Not IsEmpty(c.Offset(1, 0)) Then Exit Sub
i = c.Row
Application.EnableEvents = False
Range("B" & i - 1 & ":E" & i - 1).Copy Range("B" & i & ":E" & i)
Application.EnableEvents = True
On Error Goto 0
End Sub
i have two date fields....one date requested eg 02/06/09 (cell C10)...another date completed eg 03/06/09 (cell R10)
a third field (cell S10) contains the formula: =IF(R10-C10=0,"less than a day",R10-C10)
so if a request was actioned on the day then it shows as less than a day, otherwise will show how many days it took
but when this formula is draggeddown all other cells show - less than a day
how can i make these cells blank whilst still holding the formula?
also - is there a quick formula to add to show only the amount of wrking days a request took to complete?
I have a spreadsheet that requires a formula in column "e". How can I automaticlly extend the formula each time data is entered in column "d" of the next row.
View 7 Replies View RelatedI'm creating a "universal-fluctuating" vendor inventory return worksheet for a auto parts store that consist of one criteria (cores, warranties, or N/R ) and will return one or two results of core cost and/or unit cost. This part of the task I have accomplished by using a drop down list for my criteria and my results will appear in two different columns using a Vlookup table. The problem is due to inventory fluctuating from cores and waranties on a month by month basis, vendor requiremnts differ for the number of units returned, and last make the boss happy on ink and papers supplies :D I was wondering if it is possible loop my code in a given column where it will move my code to the next row untill I reach a grand total?
View 8 Replies View RelatedUsers will either accept the numerical entry that is in any cell, or enter a different number or Zero.
I have a few users that have not found the Delete Button, therefore they are typing a Space and hitting Enter, for Zero (nothing).
This is causing alot of problems because there are formulas that work off of these cells, and can't because the "data type is different"
I recorded the below (and also tried the commented out part) but it is not doing what I wish.
The ActiveCell will give the MsgBox after I return to that cell (if I do), but I need the Msg to appear after I leave the cell instead.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.FormulaR1C1 = " " Then
'If Target.Cells < " " Then
MsgBox prompt:="Please use the DELETE button or put a ZERO in that cell"
End If
End Sub
This seems like a very simple question but I can't find the answer that I'm looking for. I have a spreadsheet with a drop-down ( validation) list. Based on what the person selects, I then would like to have a message box to come that would direct them that certain cells then need to be filled in.
View 4 Replies View RelatedI have a spreadsheet filled with formulas that depend on a value being entered into A2, A3, A4, etc... So column A starting at A2 is where I will manually input a number and the formulas I have in columns B, C, and D will import information from another sheet based off what is put in column A. In column D the formula I have to import data
is =IFERROR(VLOOKUP(A2,Master!C:M,11,0),"").
This will import another number. Additionally in column D, I have conditional formatting that will return a red, yellow or green light based off the rule I have in place. Everything works fine, the only problem is that column D has a green light all the way down even without a value being placed in column A. I would like to find out a way to keep the cells in column D blank until a value is entered in column A. Also, if I go back and delete the value in column A, I would like the corresponding cell in column D to go back to blank as well.
I would like to present the user with an input box asking him to enter a numerical value greater than 0.
Then I would like the code starting with row 10 and consult the vaues in column O, and only display the rows that have number in it less than or equal to the number in the input box. I was hoping it would involve a filter.
I would also need a macro to display all of the rows again.
If this is too hard, how about copying the results to a new worksheet along with the labels in row 9?
Im looking for a way to select between multiple choices linked to one criteria. Eg criteria beeing Chain x Article 1, which should give me the option to show and select from multiple unique values linked to that criteria (in attached example values 1 or 30 in column AQ marked in yellow). If possible solution should be formulas/functions.
View 2 Replies View Related(Excel 2010). I have 3 cell that contain data. What I need is to compare the 3 cell and return the data that has different value into 1 new cell.
I have attach an example : test.xlsx‎
i am having trouble putting together an IF Formula together with and/or. i need to do the following
if cells k8 and l8 and r8 are empty, then no data should show.
if cells k8 and l8 and r8 is zero, then show zero.
otherwise add all three cells.
i thought i should use if(and... that is all 3 cells must be empty or zero.
=IF(OR(ISBLANK(K8),ISBLANK(L8),ISBLANK(R8)), "no data", IF(OR(K8=0, L8=0, R8=0),"ZERO", K8+L8+R8))
i have tried if(and) and if(or) and no matter what i have tried it doesnt work
I need a formula that will show the total number of times "text" is entered into a column and when sorted by year that would only show the total for that year. The COUNTIF formula i'm using only works for the total, not when sorted by year.
View 9 Replies View RelatedI have 2 different formulas that I need changed in a similar way.
The first formula is for cell AV11:
=SUM(BI11,BP11,BW11,CD11,CK11,CR11,CY11,DF11,DM11,DT11,EA11)+10
Every cell starts off blank.
What I need is for cell AV11 to always start off blank until data is entered into one of the other cells. The problem is that since the sum always needs to be +10 only when data is entered in the other cells, I don't know how to keep 10 from showing in cell AV11 when no data is typed in the other cells.
The other formula is for cell CO39:
=(CU8)+3
I pretty much need the same thing. If no data is entered in cell CU8, then I do not want cell CO39 to show the 3.
I have a formula in a spreadsheet that must be entered by selecting Command (Apple) and Return. This puts parentheses around the entire formula. Example: {=A1+B1}
I have it in a SUM IF formula: {=SUM(IF('Dollars'!$A$2:$A$2546=A116,IF('Dollars'!$B$2:$B$2546=B116,'Dollars'!$E$2:$E$2546)))}
If it's not entered this way, it will not work. You cannot simply hit return for the formula to work.
Since I did this so long ago (4 years), I cannot remember what it's called; why it has to be done that way and how to do the same thing in Windows Excel.
In this file i want the red coloured cells not to show zero (0) or anything unless anything else is entered in the cells above.In other words I want to hide the zero if nothing is entered above.
View 5 Replies View RelatedIn Spreadsheet A Cell H8 is entered as Item Description.
In Spreadsheet B Cell H8 is also entered as Item Description, but if the Item Description in Spreadsheet A has been changed, I need Spreadsheet B to reflect that change, if not it should stay as Item Description.
Need a formula which will allow an error message to pop up in a cell if more than 16 Characters are entered into another cell?
However, I cant put an actual limit on the characters for this cell as if another cell is populated with anything other than bacs then it can allow more than 16. Therefore my formula needs to state something like :
What formula can I use to accomplish the following, if it can be done.
If B9 is 100% all the rest of the % cell inputs would be 0%
If B9 is less 100% ,lets say 50% all the rest of the % cell inputs would be less than or equal to 50% until any sum of those cells equal to 100%
in which case if I enter 50% on B9 and 25% on D9 and 25% on F9 their total sum would be 100%.
If I try to enter a % on H9 it should not allow me to do it
because then the total sum on O9 would be more the the original amount on A9
I have a formula in one of my sheets that updates each time a new value is entered...
=INDEX('HR Reports Marco New'!BI:BI,COUNTA('HR Reports Marco New'!BI:BI),1)
The issue I'm having is that Column "BI" has an =SUM formula already in it and the formula above is going to the last value which is 0 but all the way to the end of the year when we are only in July...
I have a list of names in separate but connecting cells (list 1). I would like to use list 1 as a population from which to select and enter names into other cells (list 2). Is there a way that the names in list 1 would disappear from the available population when entered into list 2?
View 3 Replies View RelatedOne of my co-workers is using excel 2000 and when she enters a formula into any cell, adjecent to a hidden row/column or not, it automaticaly unhides all hidden rows & columns. My PC does not do this. I tried to find a setting to duplicate this and have not found anything yet.
The problem i have is with the sum offset function. The formula i use is to sum up the totals for each page of an accounts document. At the top of each page there is a column with " £ p " in it and i use that as the basis of the formula. This formula is entered in the bottom of every page to calculate the total for each page.
=SUM(OFFSET(F211,-(ROW(F211)-MATCH("£*",$F$1:F210,1)-1),0,ROW(F211)-MATCH("£*",$F$1:F210,1)-1,1))
This works all the time but when text is entered into column F (the column i'm adding up) it messes up with the formula. the formula will instead find the £ p but for a page 2/3 pages before the one i'm calculating on. This formula only faults when text is entered. Another thing; this excel sheet i'm working on is an output from a different computer program. it is outputted as csv file i think.
Like i said the formula works a treat except when text is entered. The obvious way around this not to enter text but that is not an ideal option. I cannot think of another way of calculating the total for each page although i'm sure another exists.
I am working on a sheet called 2005, with "Jan" entered into cell C4. I want to reference cells on the sheet called Jan2005 using a function that points to C4 and adds "2005".
View 2 Replies View RelatedWhen I copy the formula to another cell, It doesn't show the result. But when I double click on it, it works. For example
there r values in A1 as "john" & A2 as "Jack". I write formula =A1 in cell B1. It shows "John". Now I copy this formula to B2, it shows "john" instead of "Jack" . When I double click on B2 & enter, it shows "Jack".
I am trying to create a "Master Sheet" where I enter in the column data and after I have entered my data for each row, I can select the button which toggles the macro to run. I have it built to build new sheets as new clients are obtained. My problem is after I have a sheet that has client's data I cannot get new data to add itself below the data that is already there. I want each client's sheet to keep adding rows as more data comes in. My current macro is :
[Code] .....
Attached File : Data Entry Macro.xlsx
I'm trying to convert the values entered by users into specific cells into a specific percentage. When I use this macro in Excel 2010, I can enter the value to be calculated directly into the cell and the macro runs automatically. When I open the file in Excel 2007, the macro does not calculate properly unless the value to be calculated is entered into the formula bar.
For example, in Excel 2007, if I enter '30' in cell E11, it should come out as '20%'. Instead it comes out as '0.2%' unless I enter '30' into the formula bar, which then calculates properly. It is far more efficient to be able to enter the value directly into the cell.
[Code] .....