i'm having trouble formating a cell to accept either user input or a equation i've made. i would like it to accept the user input and when there is no input to use the equation i have.
View 8 RepliesI am trying to use an "IF" statement to either apply a formula or retain the last value of the cell, is there a way to accomplish this.
E.g. = IF( A1=B1, "retain last value", D1)
last value = the value of the cell prior either to A1 or B1 being changed.
I'm having some trouble trying to get excel to input a formula into a cell. I'm still a novice at VBA right now, so I don't think my problem will be too much of a brain buster.
I want a formula in Cell A6 (and I already know it correctly works) in this format: =E6&VLOOKUP(I6,'FA-Fund Data'!B$1:C$2000,2,FALSE)&J6
Is there a way to retain a formula when you have a drop down options that generates a date stamp? The problem is, i have this worksheet that has a drop down option on column B and generates the Due Date on column A, once an option is selected on the drop down list. After selecting, the output becomes static, but then overwrites the formula of column A, thus i guess making it static. I've had great help from this board helping me with the formula and the vb code. Is it possible? Here's an example of the formula and the ...
View 9 Replies View RelatedI am trying to create a macro which gives me an input box. I need to enter 2 numbers into the input box. It would be as follows:
Input Box Msg 1 - "What is your labor cost?" (NUM1)
Input Box Msg 2 - "What is your productivity rate?" (NUM2)
Then I need the macro to take this info and enter it into a formula which changes according to the row which you are on. (I need the macro to work based on which ever cell I currently have selected). The formula would be this if I currently had a cell on row 10 selected:
=(NUM1*(NUM2*$H10))/$H10
So, if I currently had cell L10 selected, and I ran the macro and put 100 in for "NUM1" and 10 for "NUM2" and my sheet had 20 in cell H10, then the macro would enter the number 1,000 into cell L10. If I had L500 selected, then the macro would enter the final number into cell L500 based on what value H500 contained.
I am using the VLOOKUP function in a cell, however, I would like the value returned to also retain the text format from the table (eg colour and bold). I have attached a simple example (to demonstrate the point) where cell B14 should have the word 'French' in bold red.Example.xlsx
View 3 Replies View RelatedI have the following code below. I would like to amend the code to retain the previous entry in the input box and amend this if the need arises
Code:
Sheets("data").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = InputBox("Amend details if different from data in inputbox")
I am trying to input this formula (=IF(M14
View 4 Replies View Related I would like a cell to have user input. If there is no input then the cell should reference a value in another cell. My strategy was to use a formula in another cell to check to see if the user input cell was empty. If it was empty then the formula propagated the cell. If it had user inputted data then it stayed like it was.
Here is an example:
Begin Invent
1000
Gallons
End Invent
User input or
formula here
Gallons
Received
Gallons
Used
Gallons
I want the user to be able to input a value for the End Invent cell right below 1000. If there is no value I want that same cell to mirror the cell above it with the 1000 in it.
I have a table with 4 radio buttons that assume certain data in three cells when they are selected. I have used the IF command to getthe information to appear in these boxes when the cooresponding radio button is selected.
I want the user to be able to either leave the selection as it is presented to them, have the option to change the data if it doesn't fit their scenerio.
Example: button 1 "Electric" makes the data "$0.12", "$per kWh", & "100%" appear in the three cells. Each button produces different data in these boxes, however if when button 1 is selected, the user's electric cost per kWh is $0.10, I want them to be able to type that in without changing my preset default.
Cell A5 has a Yes_No pull-down list for data validation. In cell B5, I want to display the value 0.002 only if A5 is "Yes". If A5 is "No", I want the user to be able to enter a number into the B5 cell without destroying the formula.
View 4 Replies View RelatedCell A1 is going to be used as my true/false value to trigger the formula. Cell A2 must allow user input to calculate "what if" scenarios however, if cell A1 value = true then cell A2 = 0.
I think I need to place my formula into A3 to allow the user their input to A2 without overwriting my formula. Problem is if I place the = sign in front of IF, the formula does not work the way I need it to. If I don't place the = sign in front of IF excel just treats it as a text entry.
=IF(A1="Actual",A2=0)
IF(A1="Actual",A2=0)
I have inherited this workbook and am just trying to automate the functionality to streamline.
Two formulas which I need in Cells E74 and E75.
Basically the formulas will depend whether the entry in D73 are Yes or No
in cell E74, IF D73 = ‘No’ then E74 = D74 + E70 ELSE IF ‘D73’ = ‘Yes’ then E74 = E70
in cell E75, IF D73 = ‘No’ then E74 = D74 + E70 ELSE IF D73 = ‘Yes’ then E74 = E70
Excel version 2010
I do data entry for a driveway company. I get addresses and phone numbers, and then call the people to offer services. I keep track of my calls on the spreadsheet, color coded for my results, green for leads, yellow for voicemail/no answer, and red for not interested.
I have formulas in cells M2 through M8 to give me my overall stats for the calls I have made, it filters them by color for leads, missed calls, and rejections, and a couple other stats (mostly for my own curiosity)
What I'm trying to add is the cells L10 to M13, I want to be able to input a date, for instance 8/15, and have it run the same stats, just for that day essentially. So, I need it to search the last 2 columns in the table for the text that I input into cell M10, and then run the color based formula on those cells, and total them up in the appropriate cells, M11, M12, and M13.
The 2 Stats tables are the same at the moment in the sample, as I only included one particular street that I have mapped, my actual table is nearly 3000 rows.
I have a template with formulas calculating a default value, but still allowing the user to override the cells with direct input.
I want to use conditional formatting to highlight any cells that have been overwritten, but can't find a way for Excel to differentiate between a cell with a formula or an inputted constant.
I realize there is a VBA "isFormula" function, but I don't want to have to use VBA for this.
When i use
Code:
=IF(ISBLANK(I31);"";I31-IF(ISNUMBER(J31);J31;0))
The cell gives me an error, i think because I31 has a formula that returns no value.
How can i stop the formula if cell I31 gives me no data??
How do I input an IF Formula to prevent Zeros appearing in the TOTAL cell ....
Example: TOTAL Cell (A10) =sumA1:A9
ALSO difference between two sums
Example TOTAL Cell (A20) =SumA10-A19
I'm trying to work out the way of having a cell give a result based on what has been input into two other cells.
Basically I'm doing up a risk management spreadsheet. I need to have in one cell the LIKELIHOOD of an incident, in the second cell, the CONSEQUENCES of it happening and ending up with a RISK MANAGEMENT ASSESSMENT in the third cell.
See attached worksheet for reference. Is it possible (while utilizing the same spreadsheet on a weekly basis) to zero a spreadsheet subsequent to its use. Importantly however, all relevant formulas must remain perfectly intact and will re-establish themselves once relevant data is placed inside an individual cell? In this case, as soon as a “Name” (or even a letter) is referenced inside the “Name” column: H10:H19?
In other words, the entire sheet is blank bar the top date and respective headings. Once any text is placed inside cells H10:H19, the formulas from the associated Row re-applies itself to the “Week-Start” dates, “Week-End” dates and references a default “Phone” amount for ‘$10’? The Data Validation formulas I’m sure would remain undamaged? This would prevent ‘text clutter’ (such as dates extending to the bottom with no apparent referencing or connecting information?
I need to display two separate values from a given input, but not exceed a specified number for that cell. I have this so far except for the maximum number that can be displayed; Example for what I want below ....
View 8 Replies View RelatedI was wondering how you can protect a formula but still allow editing in the cell. Right now I have certain columns locked while allowing others to be edited. I have a formula in one column that needs to be edited if need be but if they make a mistake and hit delete then my formula disappears and it throws the whole sheet off. Is there a way to protect your formula maybe by putting it in a different cell that can be locked and referencing the cell where they can input?
View 9 Replies View RelatedI'm using Excel 2007 and the sheet has 238536 rows containing 26412 ranges.
The first 5 rows in each range contains data. I wish to input into the sixth cell of every range a formula. Not essential but I'd prefer it to be formatted blue.
I attach short s/s to illustrate my need.
i need a macro to insert the formula i have in Column M row 3, and insert it all the way down to the last letter that is contained in column k. So in this example The Last Letter in Column K is AQ, ( i dont physically mean last i mean last in alphabetical order in excel , such as ( W X Y Z, AB, AC , AD ) AD would be last.
So since AQ is last it would insert the formula 43 times, which is AQ. THe AFter tab should clear any confusion up because this is how it looks finished. I use various sheets like this so the last letter changes, so having this macro be dynamic and not attached to a sheet name would be ideal.
I have the following (intense) formula
=SUMPRODUCT(--ISNUMBER(SEARCH(MID(A5,{1,4,8,12,16},3),B5)))>=3
I was wanting to make either 3 input boxes or 1 if thats possible that basically you click and it captures the column you want the answer in, and then the two columns the data is in. It then places the formula down in that column with the answers based on what your input was.
I would want the range to be atleast 1 to 6000 rows (just in case)
I am currently building a template for a project that will allow users to upload raw data which is then converted into a report. Part of this report is a cover page that has a graphic or picture on the front. My question to everyone is this...
Is there a way to put a formula either embeded into the picture, or above the picture, or putting the picture in the background so that the formula can do its beautiful magic and collect the information it needs for the cover?
My goal is for this picture to have the data on top of it without a border so the picture is the background image basically.
In column H: =IF(ISERROR(L4-J4-K4),"",IF(OR(K4="0", K4=""), L4-J4, L4-J4-K4))
So, if formula is going to come up with an error, I don't need to know. If it's not an error: L - J - K. Except that sometimes there isn't information in J or K, and the formula won't calcluate L - 0 - 0, unless I physically enter in zeros.
=IF(ISERROR(L4-J4-K4),"",IF(K4=""), L4-J4, L4-J4-K4))
and
=IF(ISERROR(L4-J4-K4),"", L4-J4-K4))
I don't want the people using this to have to enter in a plethora of zeros in order to force the calculation. What am I doing wrong? I've attached workbook with the relevant information in it. You can see in the middle that if there is nothing entered, it won't work right.
I want a user to be able to manually enter a formula into an input box which will then be used for other code elsewhere?
I have the following code but it does not want to work
HTML Code:
Dim fFormula As String
On Error Resume Next
Application.DisplayAlerts = False
[Code]....
I have a column of hours spent working on a particular task, but I only want to add the hours that fall within a two week reporting period.
Is it correct to use the SUMIF formula? If so how do I write the formula to include the date range desired? Would it go under 'criteria'?
Tried to open the formulation area of the forum but get an error so posting here and when that is working I can move it.
I need to develop a formula that is used in our microbiology laboratory and the formula on paper is attached as I needed scientific symbols.
total plate count formula.JPG
I often have to research long sheets of data, which requires filling data in on my research sheet from several different sources. The research sheet has a file # in A and a vendor # in C. The data sources have the file #s & vendor #s in varying columns, but I use a pivot table to end up with file # in A, vendor # in B and the needed data in C.
The length of the research sheet varies from month to month - often 800-900 rows, so you can imagine how tedious it is to find the correct file #/vendor # combination to plug the data in. (Many of the rows on the research sheet will not have a row on the data source sheet.)
I managed to get an array formula in one of the columns on the research sheet that actually DOES work:
Code:
{=INDEX('Acct Activity'!C$1:C$7,MATCH(A7&C7,'Acct Activity'!A$1:A$7&'Acct Activity'!B$1:B$7,0))}
But the problem is that it returns #N/A in any row that doesn't have any data. This messes up the sum formula in the farthest right column. So, I tried to incorporate an ISERROR in with the formula, but couldn't get it to work; it left all the cells blank.
In the meantime, I got to thinking that, since there are up to 3 columns requiring data on the research sheet & each column's data comes from a different source, wouldn't it be great to have a macro where I could use a couple of Input Boxes: 1 that would let me click on the column where the data NEEDS to go & 1 that would let me tell it (either by typing the worksheet name or by clicking on the worksheet " PREFERRED " where the data come from)!
So, I spent the entire day yesterday trying to make any of that work & can NOT figure it out! I got the macro to pop up the first input box, & can key in the column letter, but clicking OK doesn't do anything. Yesterday, I had it so I could click OK & the box would go away, but nothing else happened & the second box wouldn't work right.
THEN, I started trying to figure out how to do an array formula in a macro & my head nearly exploded! I have a terrible time trying to understand written descriptions if they're in "tech speak", so it was all greek to me.
Here is all the farther I got with the macro:
Code:
Sub ClearingRsch()
' Jenny 10092011
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
.DisplayAlerts = False
[Code] ......