How Do I Allow User Input In A Cell With A Formula
Feb 19, 2009
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.
View 9 Replies
ADVERTISEMENT
Sep 15, 2014
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.
View 5 Replies
View Related
Jan 13, 2010
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 Related
Apr 14, 2014
Cell 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.
View 5 Replies
View Related
Jan 8, 2009
I'm trying to create a formula similar to this: =sum(B7:B & 8*A2+3). What I want the formula to do is start in B7 and go to B###. The cell number is based on user input. So if the user enters "24" in cell A2, then 8*24 = 192, 192 + 3 = 195, so the formula would be: =sum(B7:B195). Can I do this in a cell, or do I need to run it in a macro?
View 2 Replies
View Related
Jan 8, 2009
I'm trying to create a formula similar to this:
=Calculations!(Indirect("N"&A2)-Indirect("AB"&A2)+Indirect("AA"&A2)
The idea is that the user will enter a value in cell A2. That number will determine the appropriate rows in the formula above.
View 6 Replies
View Related
Apr 4, 2008
I have a cell B10 which contains a formula.
I would like a macro which copies this formula across the same row.
However, the macro should prompt the user to select a range.
Based on the range selected and in particular the columns in this selected range, I would like the macro to copy the formula only for the columns specified in the selected range.
View 9 Replies
View Related
Dec 1, 2009
I have a cell which team members enter their team name in to. For example FR1 and RA5. I set up data validation to only allow four characters so they can enter RA10 FR15 etc. However I have now found some are entering FR 1 with a space between the team name and number. This then means my formulas further on are not picking up the cell correctly. How can I have the space between the FR and 1 removed automatically upon user input?
View 4 Replies
View Related
Aug 14, 2013
I need a cell to display a default value (something like "Input Here") which a user can overwrite with a value. When the value is erased, the cell should return to the default. Preferably, it'd be great if the cell reset to the default value whenever the workbook is opened as well (i.e. won't save the user's input value).
View 2 Replies
View Related
Jul 30, 2009
I am working on a spreadsheet and would like to create a cell that allows the user to input a number (1 to 5) which will then equate to a percentage(%). I would then like this to deduct that percentage from a total number...
To try and explain this.. Should the user input 1 into the cell, this would then equate to 3% - this would then deduct from a total that percentage. Input 2 = 5% etc..
View 12 Replies
View Related
Feb 9, 2010
I have a user form that has a combo box "City" two text boxes one called "Flight" and the other "Date". What I'm trying to do is to prevent the user from saving the data input from the user form if any of those three fields is left blank. The code that I have so far checks all of the required fields, if any are left blank a message notifys which field(s) is left blank and return the focus back to that field. But the rest of code also fires.
What I really need is either to stop the code if any fields are blank and return the focus back to the blank field, the user completes the field(s) and clicks the save again, or better yet, pause the code until all the required fields are completed and then complete the save. (There is actually another 200+ lines of code in this sub, but I deleted it to keep the post a little shorter.)
View 2 Replies
View Related
Jul 24, 2014
I 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.
View 9 Replies
View Related
Nov 27, 2013
I want to restrict user from entering alphabets like I,O,Q and S in a cell. Can we do this using data validation or any formula because I don't wan't to use drop-down list or refer to values in other cells either.
View 3 Replies
View Related
Feb 17, 2010
I am trying to check a user input to see if the user has entered the date correctly. I am using the code below but it keeps giving me a "Type Mismatch" error.
View 2 Replies
View Related
Jun 26, 2014
So my issue this time is that I have a bunch of columns that correspond to a month. If the user wants to view data from that month column then they have to link to that cell specifically (the worksheet being used is different than the one in which the month columns are)
My goal was to create a single cell in which the user typed in a month abbreviation such as jan, feb, mar.. so on and so forth. From there they correspond to a vlookup table which tells you which month is in what column (because that part never changes) so jan is in column c and feb is in column d... continuing until december is in column n. So the vlookup corresponds to the table and that works fine, type jan into the cell and the vlookup returns c. So i think this will work.
My problem is that in the cell I am typing this in I want it to be a part of a whole cell reference such as c5 or d7 where the 5 or 7 is a constant (I have that part down) however the reference is to another sheet. So in sheet 2 I want to reference sheet 1 i.e.
Formula: [Code].....
<-- is the desired return.
Right now (in sheet 2) I have
Formula: [Select Code] .....
Which returns the desired d5 but as text and not referencing sheet one. I tried
Formula: [Code]....
But this makes the vlookup malfunction (i think it starts looking in sheet 1 for the month cell but it is not and can not be there.
SO how do i force a vlookup to turn into a cell reference, and how do i make that cell reference come from a different worksheet without causing the vlookup to malfunction.
Attached File : examples.xlsx
View 2 Replies
View Related
Apr 17, 2013
At the top of my spreadsheet, a day of the week is selected via Dropdown.
Below that in a table, the days of the week are split into hourly slots.
I want to write a macro to jump to the first cell in the bottom table containing the day selected in top cell.
View 9 Replies
View Related
Apr 23, 2009
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
View 3 Replies
View Related
May 14, 2008
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 Replies
View Related
Jan 24, 2014
How do I pause my macro until the user selects a chart in the worksheet? Or Keep prompting a message to the user till he selects the chart and when a chart is selected run the macro.
Step 1: Check if a chart is Selected
Step 2: If yes, run the macro
Step 3: If no, prompt the user with a msgbox of Retry/Cancel.
Step 4: If the user selects Retry, wait till he makes a selection.
Step 5: If the selection is a chart, run the macro. If the selection is not a chart prompt the same msgbox again and keep looping it till he selects a chart.
Sub test5()
Dim chtSelected As Chart
Dim UserResponse As Integer
On Error Resume Next
Set chtSelected = ActiveChart
[Code] .....
View 2 Replies
View Related
Mar 28, 2009
I would like to do a Macro which requires user to go to a specific line which is variable in each work sheet. I would like to be able to enter the number and then delete all lines below that point and then continue with the balance of the macro.
Is there an easy way to pause the Macro and then restart it? Or is there a code that I can insert in the Macro to locate a specific Cell and then delete all lines below the next line?
View 4 Replies
View Related
Apr 15, 2009
In my macro, I'm trying to use an input box to determine the factor to multiply another cell by. I also want to only allow entry of 6.0 or less, and only allow, (but not require), a single decimal place. Here's what I've got so far, but it's not working:
View 2 Replies
View Related
Mar 2, 2012
I would like to make a button on my toolbar that will let me autofill whatever cell I've selected down to whatever row I designate. I would like to have a user input where you put in the row number and it autofills whatever formula down to that row in the column selected.
I know how to do a normal autofill, but I don't know how to do a user input or have it input into the autofill process.
View 5 Replies
View Related
Jul 14, 2006
This code works fine if i use static ranges, but i would like the user to input the column in which the data is stored.
'Get data for the locations of the gaming license numbers needed for the comparison
varPlayerHost = InputBox("Please enter a single letter for the" + vbCrLf + _
"Column that the Player Host License" + vbCrLf + _
"numbers are in.", "Player Host Number Location", "H")
varHostLicense = InputBox("Now enter the column letter for the copied employee" + vbCrLf + _
"license numbers", "Employee License Number Location", "U")
'Set the ranges for the data to be compared
'This is where I am getting the error???
Set rRangeA = Range([varPlayerHost,1], Range(varPlayerHost, 65536).End(xlUp))
Set rRangeB = Range([varHostLicense,1], Range(varHostLicense, 65536).End(xlUp))
'The actual comparison and deletion of record that match the license numbers copied.
'When this function finishes you will be left with a spreadsheet that only has patron
'information left for the patrons that have an invalid host number.
For Each rCell In rRangeA
If WorksheetFunction. CountIf(rRangeB, rCell) > 0 Then
rCell.EntireRow.Delete
End If
Next rCell
This exact code is kicking an error
"Run Time error 1004"
"Method 'Range' of Object '_Global' failed"
Not quite sure where i went wrong.
View 9 Replies
View Related
Aug 10, 2006
If anything else is entered into the input box a message box (MSGbox) with a vbCritical button will appear with the following prompt "You entry (insert entry data here) is inccorect." I don't know the code for "insert entry data here".
View 4 Replies
View Related
Jan 18, 2012
I am trying to input this formula (=IF(M14
View 4 Replies
View Related
May 2, 2007
I want to administrate a short training test. The user has chooses a correct answer A-E, off a drop down menu. I want to set it up so the user can't answer multiple times until they choose the correct answer.
View 14 Replies
View Related
Feb 4, 2014
I am trying to build a user-friendly Excel database that takes the user's input from various cells and logs that data input by the user in successive rows to the side. Please see the screenshots of my current spreadsheet below:
Compressor Database.jpg
From the screenshot above, I would like for the user to input their data for the Date of Last Inspection, Current Hours, and check the boxes for any services they performed in the orange/pink "Input" box on the left. Currently I have the spreadsheet set up so that the orange/pink "input" areas recognize data put in to the yellow "Data Log" cells to the right. I am trying to get the yellow "Data Log" cell area to store the Date and Hours data put in by the user in successive rows like a database, but I am not sure how to do this. I was thinking of putting a button in the says "Log Data" which would, when clicked, store the data in the "Data Log" cells. I am unsure if I need to use a Macro or whether Excel has a simple, built-in function that can be used. Eventually I have plans for the "Date of Last _______ Change" Gray boxes, but would like to take this one step at a time.
I am not well versed in VBA and Excel Macros, and I am currently using Microsoft Excel 2007 (yes, I know it is old). I do have C# and Matlab coding experience, however I would like to do this in Excel to make it easier for the User as it is a well known program.
View 14 Replies
View Related
Jun 18, 2007
I want to present a user with a list that they can make a selection from and enter their selection in a cell. Sounds easy but.........
View 9 Replies
View Related
Jun 14, 2008
I have a very simple macro for recalculating formulas and printing the page.
I need a way to show an input box where the user can say how many times to repeat the process i.e. 2x = recalculate, print, recalculate, print.
View 11 Replies
View Related
Oct 31, 2008
I am writing a macro that will perform a loop operation to export files. I want the user to specify the path, and the macro will save each exported file to that folder.
I did a number of searches and I can't find exactly what I'm looking for.
I think I want to use ChDir after using CurDir. But I don't know how to code the macro so that it asks the user to specify the location.
View 4 Replies
View Related
