Macro To Add Nested Formula To Cell With Variables
Jun 20, 2008
I want the range in the VLOOKUP below to display its actual value - ie "'6 June 08'!$A:$C" in the formula rather than the actual variable name 'strResult'.
Sub PreviousCount()
strDate = "6 June 08"
strColumnRange = "$A:$C"
strResult = "='" & strDate & "'!" & strColumnRange
Dim i As Long
i = Range("A2"). CurrentRegion.Rows.Count
Range("D2:D" & i).FormulaR1C1 = "=IF(RC[-3]="""", ""Column A blank!"", IF(ISNA(VLOOKUP(RC[-3],strResult,3,0)), ""NEW INSTALL"", VLOOKUP(RC[-3],strResult,3,0)))"
End Sub
View 4 Replies
ADVERTISEMENT
May 14, 2008
I am creating a worksheet and I have a formlua where the value is text. I can make it work standing alone but not with other variables. =IF(E11=S1,0)+(IF(E12=S1,0)+(IF(E13=S1,0.0025))+IF(E14=S1,"Call for Prciing"))
View 2 Replies
View Related
May 5, 2014
Basically I need a formula that takes into effect many variables. It needs to check a size, thickness, and material, to determine a cost...
1.5 O.D. - 16GA - 304SS = $X
but
1.5 O.D. - 18 GA - 304SS = $Y
and such.
The problem I have is not only am I not understanding a lot of Excel jargon, the data set is not in perfect "example" conditions.
O.D. is in N:N yet there are blanks between N(x) and N(y), GA is in O:O yet blanks between O(a) and O(b), and so on.
Here is what I have at the moment, it uses a second sheet to derive the cost based on the parameters. Please ignore the first few terms, they are used with this cost lookup to give me my final. There also may be incorrect syntax in this, but since I get a nesting error first, I really don't know.
=V7+(Q7*M7*(IF(N7=2=AND(O7=20)=AND(L7=304),’Steel Tube Cost WIP’!$DD$19,IF(N7=2=AND(O7=20)=AND(L7=316),’SteelTubeCostWIP’!$DD$20,IF(N7=2=AND(O7=20)=AND(L7=2205),
’SteelTubeCostWIP’!$DD$21,IF(N7=2=AND(O7=18)=AND(L7=304),’Steel Tube Cost WIP’!$DD$16,IF(N7=2=AND(O7=18)=AND(L7=316),’SteelTubeCostWIP’!$DD$17,IF(N7=2=AND(O7=18)=AND(L7=2205),
’SteelTubeCostWIP’!$DD$18,IF(N7=2=AND(O7=16)=AND(L7=304),’Steel Tube Cost
[Code] ......
Mentioning LOOKUP, can I even use it? I've attempted many times however it isn't going too well What can I do?
View 2 Replies
View Related
May 22, 2008
I would like to use a varible Cell for the following forumula:
ActiveCell.Formula = "= ROUND(PIExpDat(""TimeEq('""&Cells(RowNdxG,4)&F$2,F$3,F$4,F$5,0,)/3600,1)"
I have underlined the variable. This is not working but is rather showing up as a string value Cells(RowNdxG, 4).
View 6 Replies
View Related
Dec 10, 2008
I can choose among three variables in cell A1. They are named P10, P50 and P90. I want to substitute them with 1,2 and 3 in cell B1. Is it possible to do this by using the substitute-function or do I have to use a nested IF.
View 2 Replies
View Related
Nov 5, 2007
I am receiving an error message for a nested if statement. Excel is telling me my statement contains too many variables.
I am using a VLOOKUP to look up data in a table based upon a result in the first table, however, the entry that is returned is different based upon a unique cell value.
Would anyone have any suggestions on how to shorten this down?
=IF(CR="AG",IF(ISBLANK(A7),"",IF(ISNA(VLOOKUP(A7,SI.xls!$A:$G,3,FALSE))=TRUE," ",VLOOKUP(A7,SI.xls!$A:$G,3,FALSE))),
IF(CR="G",IF(ISBLANK(A7),"",VLOOKUP(ISNA(VLOOKUP(A7,SI.xls!$A:$G,4,FALSE))=TRUE," ",VLOOKUP(A7,SI.xls!$A:$G,4,FALSE))),
IF(CR="MG",IF(ISBLANK(A7),"",VLOOKUP(ISNA(VLOOKUP(A7,SI.xls!$A:$G,5,FALSE))=TRUE," ",VLOOKUP(A7,SI.xls!$A:$G,5,FALSE))),
IF(CR="C",IF(ISBLANK(A7),"",VLOOKUP(ISNA(VLOOKUP(A7,SI.xls!$A:$G,6,FALSE))=TRUE," ",VLOOKUP(A7,SI.xls!$A:$G,6,FALSE)))),
IF(CR="UC",IF(ISBLANK(A7),"",VLOOKUP(ISNA(VLOOKUP(A7,SI.xls!$A:$G,7,FALSE))=TRUE," ",VLOOKUP(A7,SI.xls!$A:$G,7,FALSE)))))))
View 9 Replies
View Related
Apr 29, 2013
I am very new at in depth excel formulas, usually in my daily work I can get by with simple summations, if statements etc. This one has thrown me for a loop.
I'm making a spreadsheet on soil compressibility and I feel like I am just knocking my head against the wall. Here is what I am working with. I need to calculate values in a column, we'll call them "Rm" values.
There are a few basic things that dictate which "Rm" equation you use. I'm substituting V,W,X,Y,Z in for the equations to simplify the question.
I = 3 Rm= W*log(K)
.6
View 9 Replies
View Related
Jan 12, 2010
I am trying to do a nested if formula but I can't get it to work.
The condition is: If value in cell R5 >0 then get the value in cel X29 on power 4.
=IF(R5>0,POWER(X29,4)
View 2 Replies
View Related
Sep 25, 2007
One of my macros is a tied to a worksheet_change. The macro runs and gives me the result that i want, however excel flips to the sheet that the sub is running for.
example:
Sheet1, Change the cell
excel flips the visible to sheet2
macro runs
Its kinda of annoying. I would like excel to stay on sheet1 where i am trying to finish data entry. I suspect it is how i have writen the macro. Any help at all on how to clean it up.. or a better way to write the same thing would be very appreciated.
Sub TermPlacement()
' Term_Placement Macro
' Uses the Value of Term to insert a formula repeatedly in a colum.
Dim sNFormula As String
Dim rTerm As Range
Dim rStart_Cell As Range
Dim rNpayment As Range
'The reference cell located directly above the first cell in column
Set rStart_Cell = Worksheets("Floor Plan").Range("$B$13")
'Additional set values....................
View 9 Replies
View Related
Jun 26, 2008
Application. ScreenUpdating = False
For FormulaRemake = 2 To 2000
Worksheets("Data"). Range("D" & FormulaRemake).Formula = ""=If(ISERROR(Data!W"" & FormulaRemake - 1 & "")"" & ""=True,"",Data!W"" & FormulaRemake - 1 & "")""
Next
Application.ScreenUpdating = True
I'm having troubles with the syntax for the .Formula part. I read that when you do this you're supposed to double quote everything, except if you need a "" in it...?
Should look like this when done right: =IF(ISERROR(Data!W1)=TRUE,"",Data!W1) Also is there anything besides ScreenUpdating that will make this code run faster?
View 3 Replies
View Related
Dec 19, 2013
I have a spread sheet that has various references
on the same sheet
on a different sheet ( in same workbook )
on a different sheet ( in a different workbook )
If I click on a example cell it might say
Code:
=5*C4*$D$1-Sheet1!C9
Is there a way for excel to be able to iterate through these separate variables ? and know if the reference is on the same sheet ?
Say I wanted to evaluate the references on other sheets ( replacing the link with the value )
So if Sheet1!C9 contained the value 10 the formula would be replaced with
Code:
=5*C4*$D$1-10
View 2 Replies
View Related
Apr 20, 2012
I am trying to create an active cell formula based of variables from given cells.
With ActiveCell.FormulaR1C1 = "=CONCATENATE(""RU"",RC[-12],RC[-11],RC[-10],RC[-9])-CONCATENATE(""MU"",RC[-12],RC[-11],RC[-10],RC[-9])"
ActiveCell.Formula = ActiveCell.Value
But This does nothing.
View 5 Replies
View Related
May 21, 2008
I am trying to use VB to vlookup between to workbooks
1. Make active workbook WBK1
2. Make workbook being open WBK2
3. Copy and Paste between WBK1 and WBK2
4. Have a vlookup in WBK1 and bring in the values from WBK2
5. Close WKB2
6. Copy, Paste, and transpose values in wkb1 within wkb1
The script works fine until it reaches the vlookup step. I have used the vlookup by itself without the copy and paste code successfully but when I combine the two it provides me with the error 9. Subscript out of range.
View 9 Replies
View Related
Aug 24, 2014
I am trying to use a combination of Concatenate and IF formula to produce an email.
My input.
D4: First Name:
D5: Middle Name:
D6: Last Name:
D8: Organization:
Once all these are filled, I want the formula to produce a result like
FirstName.M.LastName@Organization.com
I have used the following formula.
[Code] .....
Problem is if there is a middle name the formula works fine, but in case where there is no middle name, it produces the following result.
FirstName..LastName@Organization.com
How do I remove the additional (.) in cases where there is no middle name.
Attached File : Email Generator.xlsx‎
View 3 Replies
View Related
Feb 20, 2009
I have a rather large formula that I put together and it's shown in column 'C' in the small sample file I have attached. I'd like to add one more criteria such that I avoid duplicate solutions in column 'C'. i.e. I only want the first occurrence of a group.
The formula works fine 'bucketing' the first entry. If I have an solution in C I want the formula to not give me another answer if the cell immediately above has an answer. In other words, rows 11 and 15 would be blank because rows 10 and 14 have an answer.
I've tried inserting another criteria in the AND statement, like ISBLANK for the cell above, but I can't get it to work. What am I doing wrong, OR is there a simpler solution?
View 2 Replies
View Related
Sep 17, 2009
=IF(C2="Windows",B2,IF((AND(A2=A3,C3="Windows")),B3,IF((AND(A3=A4,C4="Windows")),B4,IF((AND(A4=A5,C5="Windows")),B5,IF((AND(A5=A6,C6="Windows")),B6,IF((AND(A6=A7,C7="Windows")),B7,IF((AND(A7=A8,C8="Windows")),B8,"None")))))))Here is a link to the spreadsheet (D2 being the formula cell):
http://img199.imageshack.us/img199/8500/29072008.jpg
Basically, I am trying to put the columns data (if it has a porch, conservatory, windows) into one row for the contract number, as the dump which I have, is giving me seperate row's instead of one.
I am trying to get the formula to check if the contract number matches on the following row, and if it does, to check if the Product Description matches the header of that row (if it's a window, conservatory) if so, copy the quantity cell into D1, however, it's copying it even if the contract numbers do not match and I do not know where I am going wrong!!
View 9 Replies
View Related
Feb 20, 2009
Objective: To find out which customers order certain items and which customers dont order certain items. Many customers may order the same item eg customer A, B, C, D all order item "4567"
I have 2 worksheets.
Worksheet 1: Showing 30 item codes, item description and customers. Items in col A (A2:A31), description in col B (B2:B31) runnning down vertically. Customer name in cell 1 of all other columns running across horizontally, eg C1, D1, E1.... (C1:GF1). There are 186 customers. (A formula needs to start at C3 and dragged to GF3)
Worksheet 2: Raw data showing customers in column A and items in column B, There are 3,753 rows. Customer in column A are duplicated as the same customer may order a number of items so for eg
ABC PTY LTD 5671
ABC PTY LTD 5683
ABC PTY LTD 5692
ABC PTY LTD 5610
ABC PTY LTD 5611
Tried the below formula
=if(C1='S2'!A2),=if('S1'!A2='S2'!B2),"ordered","")
View 8 Replies
View Related
Apr 1, 2009
I have a spreadsheet which contains dates (amongst other info) of when a specific form was completed for a client. We have 'date windows' which these should fall into. I'm really struggling with how to work this one out and whether to use nested IF's or try some kind of code (although my vb knowledge kind of stops after recording a macro). Bascially I need to know for each 'date window' if the window has been met or missed. There may be up to 30 different dates per person (in my example sheet I have up to 9).
View 2 Replies
View Related
Aug 7, 2009
He works at state tax administration. He had this formula at work to calculate income tax.
View 3 Replies
View Related
Aug 20, 2008
I currently use 3 different formulas to return a value I want. The first one is =LEN to return a value. I then want to decuct 1 from this length value so I just use =A2-1 in the next cell. I then use =right so my returned result is always the X right most characters -1. Is there a way I can nest all these formulas so I'm getting the desired result in just one cell?
View 3 Replies
View Related
Nov 18, 2012
where the issue with the following formula is?
=IF(AND($E$2="N",$AA$550),"BT1",IF(AND(Z5="F",Y5>=0),"CLEAR",IF($AA$5
View 2 Replies
View Related
Jul 23, 2013
I have to write a formula which states the following:
if cells AA1,AB1 &AC1 = 0 then "Slow-Moving",
if of these cells AA1,AB1 &AC1 contains a number then "OK",
if cells, AA1,AB1,AC1,Z1,X1,Y1 all = 0 then "Non-Moving"
I believe an If and AND combination could work but its not working for me.
View 8 Replies
View Related
Nov 1, 2006
I have the following IF formula. It is working fine except the underlined part; it does not returned the value 15 ...
View 4 Replies
View Related
Feb 4, 2008
I have searched the threads high and low, but no one seems to have come across this issue: My formula works fine untill it comes across a blank in Comparison!E16: =IF(AND(Comparison!E14=Comparison!E15,Comparison!E15=Comparison!E16),"",IF(AND(Comparison!E14=Comparison!E15,Comparison!E16=""),"") IF(AND(Comparison!E14<>Comparison!E15),Comparison!E14))
I am comparing cells from a previous sheet onto Comparison so that I can see where the discrepancies lie.
I want to:
return nothing if all the cells match;
return nothing if the first two match, but the third is blank;
return the value if the first cell isn't matching
keeping in mind that this is only for the top cell: Comparison!14 - I will then have to bring it down to 15 and 16
View 6 Replies
View Related
Mar 3, 2014
If the value in the Discount Y/N column is equal to Y, the formula should check if the value in the Tenure (Yrs) column is less than 5.
View 3 Replies
View Related
Feb 11, 2013
But I've read that the new versions of excel allow for up to 64 nested if functions. I need to nest about 5 if functions, but after I write out the 3rd excel says there are too many arguments.
=IF(INDEX(vlookup!$D$2:$D$157,MATCH($F16,vlookup!$B$2:$B$157,0))="CPM", INDEX(vlookup!$E$2:$E$157,MATCH($F16,vlookup!$B$2:$B$157,0))*(J16/1000), IF(INDEX(vlookup!$D$2:$D$157,MATCH($F16,vlookup!$B$2:$B$157,0))="CPA", INDEX(vlookup!$E$2:$E$157,MATCH($F16,vlookup!$B$2:$B$157,0))*(L16), IF(INDEX(vlookup!$D$2:$D$157,MATCH($F16,vlookup!$B$2:$B$157,0))="dCPM", 0.04*(j3/1000))), IF(INDEX(vlookup!$D$2:$D$157,MATCH($F16,vlookup!$B$2:$B$157,0))="dCPC", 0.008*k3, "calc")
View 3 Replies
View Related
Oct 16, 2009
Trying to make it even more complicated....probably again misplaced my parenthesis
=IF((AND('Input Page'!B43="-",OR('Input Page'!B13="New Lease In-House",'Input Page'!B13="New Lease w/Co-Broker")),'Analysis Report'!D57*'Input Page'!B32/”2”,'Analysis Report'!D57*'Input Page'!B32/"4",IF(AND('Input Page'!B43="-",OR('Input Page'!B13="Renewal In-House",'Input Page'!B13="Renewal w/Co-Broker",’Input Page’!B13=”Expansion In-House”,Input Page’!B13=”Expansion w/Co-Broker),'Analysis Report'!D57*'Input Page'!B32,'Analysis Report'!D57*'Input Page'!B32/"2"))
View 6 Replies
View Related
Mar 4, 2013
following
i tried formula =IF(F$1
View 3 Replies
View Related
Jun 4, 2013
I amtrying to find a way to nest a formula in the Match function.
How can i make the following formula more dynamic so that I do not specify the colum "AY"?
=MATCH(11,Data!$AY:$AY,0)
The information is contained in column AY labeled "New-Rate" in the data sheet
View 4 Replies
View Related
Jul 5, 2007
I know there is a maximum of 7 nested IF statements available but I cannot get the following formula to work, even though it only has 7 IF's
=SUM(IF(Department=J$88,IF(Progress_StatusCNP,IF(Progress_StatusCSO,IF(Progress_StatusNS,IF(Current_Phase=$B91,IF(MID(Progress_Status,1,7)="On Hold",IF(MID(Group_Procurement_Involved?,1,3)="Yes",In_Year_Opportunity__2007,0),0))))/1000)))
CNP, CSO and NS are named cells, all named ranges are the same size and the formula is entered with Ctrl & Shift to make an array formula.
View 3 Replies
View Related
