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
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.
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 RelatedI want to learn VLOOKUP formula in this following problem.
VLOOKUP($A3,Sheet2!$A$2:$Q$13,$D2,0)
I am attaching the file for the same.
I'm trying to use this formula but I get the error "too many arguments". How can I do the same as the formula without an error:
=IF(ISNA(VLOOKUP(B12,GYROLOK316ANDM!A12:E2000,3,FALSE)),VLOOKUP(B12,GYROLOKBR!A12:E2000,3,FALSE), VLOOKUP(B12,VALVESFILTERS!A12:E2000,3,FALSE),VLOOKUP(B12,GYROLOK316ANDM!A12:E2000,3,FALSE))
I added in the VLOOKUP(B12,VALVESFILTERS!A12:E2000,3,FALSE) - when I used it without that it worked. But I have 3 sheets I need the the VLOOKUP to look over and this formula that I copied off another thread only had 2 tabs.
I'm trying to enter a series of formulas into a worksheet using vba. However, this code is giving me Run time error '1004', along with the description in the title. This is the first formula (they're all relatively similar).
View 2 Replies View RelatedI 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?
Currently I am using a VLOOKUP to return a value, and I am trying to apply a calculation formula to the returned value. Below are both the VLOOKUP formula, and the calculation formula. Each of the formulas works well while in separate cells, however am I able to combine the two formulas into one cell?
Formula1.
=VLOOKUP($G20,'P&L FEED'!$C$2:$K$100,2,FALSE)
Formula2.
=($D20/$I20)*(-$H20)
So first I am using the VLOOKUP to return a value from a range, and that returned value I want to apply the second formula to.
Is it possible to join both formulas into one cell? IF so, how?
I get new worksheets on a regular basis which are of the same format (same columns with headers) and I have to perform both vlookups and sumifs on them. Their are about 12 different tabs I perform the vlookup on based a vendor name in A2 and its corresponding tab. I am tired of typing the same thing over and over again.
What I'd like to write is a Control Panel with all the equations so I can just copy/paste them in when I get a new sheet.
How can I write a vlookup which would search for the value which is one cell to the right of where I put the formula in?
Similar to:
=vlookup(current cell over one, 'vendor1sheet'!A:C,3,0).
I would then want to copy it down the worksheet. Currently type in column E everytime I get a new sheet:
=VLOOKUP(F2,'Vendor1'!A:C,3,0)
The catch
Column A has the vendor name, there are about 12 vendors, each with a different tab with related data which I am searching through.
Ultimately, I'd like to have an equation that would do the vlookup (and sumif) as described above and also use the vendor name in column A to look on a particular tab.
Something like:
=vlookup(one cell to the right,'VendorTabFromVendorNameInColumnA'!A:C,3,0)
I was going to use nested if statements on a control panel sheet with a column for the vendor name and a column for the vlookupformula and than paste a single formula all the way down column E:
In column E, every row:
If(A2=vendor1, 'controlpanel'!vendor1equation), if(A2=vendor2, 'controlpanel'!vendor2equation), etc.
Again, the vlookups would all check for one cell to the right of where this nested if statement was (located in column E, looking in F).
I am trying to create one formula that can be copied to other cells to get varied results.
Here are the formulas
1. =IF('Calculated Rental Fees'!H4='Daily Rentals & Charges'!$B$28,'Daily Rentals & Charges'!$B$30,VLOOKUP('Daily Rentals & Charges'!$A$20,LATECHRGETBLE,2,FALSE)*C4
2. =IF(AND(I4>='Daily Rentals & Charges'!$A$36),I4='Daily Rentals & Charges'!$A$38),VLOOKUP('Daily Rentals & Charges'!$A$22,LATECHRGETBLE,2,FALSE)*C4)
Basically i am trying to calculate late fees for a rental company using the if and vlookup functions.
I'm wanting to lookup the content of a cell, which is a string, "Name", resulting from a formula within that cell.
For example the first code is referencing the cell output from the second formula.
Code:
=VLOOKUP(N2,RandomTab!$A$2:$E$60,5,FALSE)
where N2 is
Code:
=UPPER(VLOOKUP(B2,OtherTab!$A$1:$H$68,8,FALSE))
presently it, the first VLOOKUP, returns #N/A
I have a VLOOKUP formula in a cell that refers to another cell that has a formula in it.
When I type the actual numerical result (rather than the formula) in the formula cell, the vlookup works. When the formula (that equals the same number) is in there, the vlookup cell is returning #n/a.
I am trying to insert an if statement in a cell. Part of the reference is in cell as4.
I want the if statement in cell as5
[Code] .......
I get a run-time error '1004':
Application-defines or object-defined error
The code works fine without the if statement, i.e.
[Code] .....
I am trying to create a lookup formula where the cell value to match has a formula behind it.
For example: =INDEX(AR7:AR371,MATCH(G28,AE7:AE371,0))
The trouble I'm having is that the value in G28 is derived from a formula and the Index Match formula then gives a #N/A result. If I change the value in G28 (a date) manually, so directly enter a date, the Index Match formula works ok.
Is there any way of getting the Index Match formula to work, or Vlookup would also do although that at the moment that has the same problem with G28 having a formula behind it.
If it can not locate the correct responce I want it the formula cell to be blank. How do I adjust my formula to do this?
This is the forumal I am currently using
=VLOOKUP(A26,Subs!A:F,5,0)
I have also tried this one as well
=IF(ISNA(VLOOKUP(A6,Subs!A:E,5,0))+(VLOOKUP(A6,Subs!A:E,5,0)=""),"",VLOOKUP(A6,Subs!A:E,5,0))
I have a VLOOKUP formula, so when the cell above (C5) has a value, it returns the value from a defined list. Simple so far and it works great when I manually input the value in the cell (C5).
The problem occurs when I change C5 to contain an AVERAGE formula? Any ideas why it will only work when there is a plain old value in there, but not a formula?
This is the VLOOKUP formula I'm using:
I have one sheet from which work is assigned to 10 different employees. Its format is something like the following:-
document number assigned to status remarks
12345678 john
45678910 steve
11247511 john
The individual sheets are then saved and assigned to all the employees.
document number assigned to status remarks
12345678 john approved
11247511 john denied info req
Now I want to update the data in the sheet by something like this
document number assigned to status remarks
12345678 john =vlookup(A1,[empname.xls]Sheet1!&A:$D,3,0)
45678910 steve
11247511 john
I got to know that indirect can be used for this.
I am trying to reference a named cell in a VLOOKUP formula as the table_array parameter.
The Named Cell "Input1" contains a table array definition of '[Test.xls]Sheet1'!$H$10:$J$100
When I try VLOOKUP($D3,Input1,3,0) - This returns #N/A as a result.
When I try VLOOKUP($D3,INDIRECT(Input1),3,0) - This returns a #REF error.
When I copy and past the contents of the Input1 named cell directly into the VLOOKUP formula, it works so I know the array reference is valid.
I am not proficient in VBA so I would like to accomplish this using basic Excel formulas if possible. how I can reference the named cell and have the cell value returned in the VLOOKUP function?
I have created a vlookup and it shows as a formula not as data. I can do 'text to columns' to correct it, but I need to drag this vlookup to lots of different cells, then change it slightly in each one.
Every time I make a change, it reverts to the formula and I have to do 'text to columns' again.