Reading Function Argument From Separate Cell?
Dec 3, 2012
I have the following problem: I have this formula, that works correctly:
=SOMMA.SE('[myfile_12112012.xlsx]RIEPILOGO'!$C$2:$C$12;E1;'[myfile_12112012.xlsx]RIEPILOGO'!$B$2:$B$12)
I wish I could read the name of the file (myfile_12112012.xlsx) or, alternatively, all of the function's first argument (('[myfile_12112012.xlsx]RIEPILOGO'!$C$2:$C$12) from a separate cell.
I tried this:
SOMMA.SE = (C2, E1, '[myfile_12112012.xlsx] ERROR SUMMARY'! $ B $ 2: $ B $ 12)
putting in the cell C2 the string:
'[myfile_12112012.xlsx]RIEPILOGO'!$C$2:$C$12
In this way, however, the formula does not work.
How can I read and evaluate correctly the first argument from the cell c2?
View 2 Replies
ADVERTISEMENT
Sep 16, 2009
I have an attached file and I am trying to build a VBA function to calculate total values. First row is "RollingTime" and for example if I am trying to calculate the "Total" value. For "RollingTime" = 2 it should be
RollingTime(2)*Percentage(2)+RollingTime (1)*(1-Percentage(1))*Percentage(2)+RollingTime(0)*(1-Percentage(0)*(1-Percentage(1))*Percentage(2)
Which is 109732508*0,3 + 1017508995*(1-0,2)*0,3+1587172158*(1-0,1)*(1-0,2)*0,3
And here is the code I have tried to produce:
View 10 Replies
View Related
Oct 13, 2008
spreadsheet has daily hours worked per employee and a weekly total. What is the formula to take the weekly total per employee and separate the regular hours (first 40) and have the overtime show up in the next column (without getting negative numbers for the totals under 40).
View 4 Replies
View Related
Sep 25, 2008
how to create a function argument containing my own defined formula?
View 9 Replies
View Related
May 23, 2008
formula to calculate the commission based on 9 cases each case is subjected to 4 differant arguments.
here is the formula for the first case (the answer if true 1.4) & the last case( if all the casses are fails it should show 0.6) u will find it in cell K5
=IF(AND(E5>=20,H5=1,I5>=80,J5>=80),1.4,0.6)
this is only 1 case HOW can i calculate it basd on 9 casses??
View 10 Replies
View Related
Jan 23, 2008
When I try a MATCH function, I substituted the range through the ADDRESS function. But, it returns #VALUE error. When I manually typed the same range address, it produces the results. I've been behind this more than 8 hours still I can resist the heat on my cool head! But, I thought you coolest head guys need some heat to warm up for to-day's challenge.
View 9 Replies
View Related
Oct 18, 2006
A custom function as shown below is not reading in the value of the parameter Ttorefinance which is an Excel cell value (actually a link in the spreadsheet). Eg when Ttorefinance cell value is 13, the function reads in 0.
Function MarginbyTranche(TfromIssue As Double, TtoRefinance As Double, AssetType As String, Tranche As String) As Double
Dim RmbsAaaMargin As Double
Dim RmbsAa3Margin As Double
Dim RmbsBaa2Margin As Double
Dim CmbsAaaMargin As Double
Dim CmbsAa3Margin As Double
Dim CmbsBaa2Margin As Double
Dim PsAaaMargin As Double
Dim PsAa3Margin As Double
Dim PsBaa2Margin As Double
View 3 Replies
View Related
Sep 12, 2006
In the code bellow I would like to automate a if function until the 1st emptycell in the row 7 of my spreadsheet.
In my IF function, I would like to refer to a fix cell ("B1") while the other argument is in the same column as the function but two rows bellow.
My problem is that I don't figure out how to refer to a fix cell with the syntax I would like to use t run the macro.
I think it's more clear when you'll see the code
Sub Face2face()
Range("B5").Activate
Do
If ActiveCell.FormulaR1C1="=If(R[2]C>=Range("B2"),Range("B2"),R[2]C)"
ActiveCell.Offset(0, 1).Select
Loop Until IsEmpty(ActiveCell.Offset(-1, 0))
End Sub
View 8 Replies
View Related
Jun 25, 2013
When I step-through my code below, it always opens the first file in the directory "C:Pyramid Files", but when it comes back to the Pyramid Files sub after fully processing the first file via various other subs, the VB Editor apparently doesn't like something about this line: StrFile = Dir(), since it quits after "snapping-back" to the previous sub Initialize(). I have also tried StrFile = Dir, but that doesn't work either. I did Dim Strfile in the General Declarations. When I set Watches for Dir and Dir(), I get the value "Invalid procedure call or argument" for both, as if the directory function lost the value. I can't determine why this is happening.
VB:
Dim WSM As Worksheet, WSB As Worksheet, WS1 As Worksheet, [U]StrFile As String[/U], StrDirectory As String, ClientCode As String
Dim Filename As String, LastRowb As Long, LastColB As Integer, LastRow1 As Integer, NextRowC As Integer, x As Integer, y As Integer
[Code] .......
View 4 Replies
View Related
Feb 26, 2014
Need to use CountIFs formula to evaluate multiple conditions in the same column. Here is the formula I tried. It works for Resolved but returns 0 when i try to add Duplicate.
=COUNTIFS('P12 Source'!H:H,A18,'P12 Source'!F:F,"Resolved,duplicate")
Also used this formula with success by referencing a cell that had "Resolved" in it but when I tried to add another cell with "Duplicate" it again returned 0.
=COUNTIFS('P12 Source'!$H:$H,A19,'P12 Source'!$F:$F,'P12 Source'!$F$75)
View 8 Replies
View Related
May 6, 2009
I have made my own excel function that my users needs every day. it works just fine, however if you do not know the required argument for this function, you couldn't use it. How can i prompt my users for the right order of the argument when they type the function in excel?. example is when you type the Function "=left(" you will be prompted by excel of the correct arguments.
View 3 Replies
View Related
Jun 27, 2006
I have a function in a cell triggered on the value returned by a DDE link in another cell.
e.g. cell A1 contains =function1(A2) and cell A2 contains
=ADVFN|NYSE_CAT!CUR
function1 is triggered each time the DDE link updates regardless of whether the result returned has changed or not. How do I prevent function1 being triggered if the returned value has not changed?
View 9 Replies
View Related
Aug 30, 2012
This error message in line vpp:
Invalid procedure call or argument
Code:
Function fn1(ByVal a, ByVal i, ByVal e, ByVal N, ByVal w, ByVal ta)
Pi = Application.WorksheetFunction.Pi
mhu = 398600
vpp = (mhu / Math.Sqr(mhu * a * (1 - e ^ 2))) * (-Math.Sin(ta * Pi / 180))
fn1 = 2 * vpp
End Function
View 9 Replies
View Related
Jul 2, 2013
When I attempt to send a spreadsheet using a German version of Excel, the following formula does NOT work properly.
=ZELLE("adresse",C14)
The function gets translated into =CELL("adresse", C14), and what I need is, obviously, = CELL("address",C14). The argument does not get translated.
View 1 Replies
View Related
Dec 28, 2013
Can we use an array like {1;3;4;5} as Row or Column argument in INDEX function? Like shown below
=INDEX($A$1:$E$10,0,{1;3;2;4;5}) OR =INDEX($A$1:$E$5,{1;2;4;5;3},0)
View 9 Replies
View Related
May 29, 2007
In VBA, True takes the integer value of -1. However, in Excel, it is 1.
I am writing a custom function that does arithmetic on a value. However, due to above, if the value is True, my custom function produces a different result than if I wrote an Excel formula to do the same thing.
Is there some way to identify the data type of a value passed into a function? Currently my function looks like this:
' Return the valueToIncrement with its magnitude increased by PctToAdd
Public Function INCREMENTPCT(valueToIncrement As Variant, PctToAdd As Variant) As Variant
INCREMENTPCT = valueToIncrement * (1 + PctToAdd)
End Function
Ideally I would like to identify whether the type is Boolean and adjust the value so as to give the same result as an Excel formula would give.
View 8 Replies
View Related
Dec 3, 2013
I'm trying to use a formula in conditional formatting to highlight a cell red if the cell contains a 0 but the date shown in another cell has passed. I want to copy the formatting throughout a column but I don't want the cell to highlight if there is no date in the other cell concerned.
View 11 Replies
View Related
Dec 1, 2008
the attached workbook, I am looking for M19, N19 & O19 to read from different locations depending on how many cells are called on when info is entered in P8.
View 10 Replies
View Related
Sep 8, 2005
I am trying to create a function in VBA (possibly an add-in in the future) to
calculate Relative Standard Deviation (RSD). I would like to be able to use
an equation such as: =RSD(values), where the values are cell references (a
variable quantity) chosen by the user.
To do this I've copied scripts for "Mean" and "StdDev", then wrote a small
function for RSD that calls those two. When I try to implement RSD in the
worksheet (or even just Mean), the cell returns an error: #VALUE! I think
the problem has something to do with the Arr() variable used as the argument,
as I've tried some simpler functions with array arguments and can't get them
to work, but using standard non-array arguments works fine. I can write a
subroutine that calls the functions and displays the correct result, but I
would like to be able to use RSD as a typical function in my worksheet.
If I could get Mean to work properly, I should be able to apply that
knowledge to StdDev and RSD. The text for Mean is listed below. I've tried
quite a few modifications to try to make it work, so if someone could get it
to work and reply with the correct function text (or with another function
that performs similarly), I'd appreciate it. Thanks.
Function Mean(Arr() As Single)
Dim Sum As Single
Dim i As Integer
Sum = 0
For i = 1 To UBound(Arr)
Sum = Sum + Arr(i)
Next i
Mean = Sum / UBound(Arr)
End Function
View 14 Replies
View Related
Apr 1, 2009
SUM(IF(FREQUENCY(E10:E29,E10:E29)>0,1)). this is the formula I currently use to read employee numbers and it works when we just use the number i.e. 011004. When we use the full employee number with alpha characters it does not work i.e. ASMO011004. I have used helper cells to do similar, but am not wanting to do this way for simplicity reasons.
View 2 Replies
View Related
Mar 10, 2007
1. Copy a certain amount of rows based on the number input at the top of the page.
i.e. you put in 12 and the macro will copy 12 rows to paste somewhere else.
2. Similarly, the same idea but instead of selecting rows i need it to drag a formula down to the relavent row number so all the copied data has a formula to match
View 4 Replies
View Related
Jan 17, 2008
I have a pretty basic spreadsheet which requires a quantity and a unit price (which may change) and then the total. I find that if there is no number in the quantity cell, it reads it as if there is a "1" in there. Can anyone offer assistance on how to get around this without placing zero in that spot?
View 9 Replies
View Related
Jul 31, 2014
I have some code I wrote that copies sheets from another sheet in another workbook to my current one, in order. However, depending on activity within these sheets, there might not be a sheet for the month.
I have the on error code to go to the next argument, however I would like it to tell me on my errors sheet that it did not exist.
The error sheet will simply have 2 columns of copied sheets and non-existent sheet.
VB:
On Error Resume Next
Workbooks("GLTemp").Sheets("981715").Copy After:=Workbooks("MHSGMR").Sheets("981715 Budget")
Workbooks("MHSGMR").Sheets("981715").Activate
sheetName = ActiveSheet.Name
Workbooks("MHSGMR").Sheets("981715").Select
ActiveSheet.Name = sheetName & " GL"
[Code] ......
View 3 Replies
View Related
Oct 20, 2009
I have the following formula which works fine in this form:
View 4 Replies
View Related
Dec 19, 2013
I am coding a spreadsheet that makes extensive use of the excel dropdown list boxes. So I have codes such all over and it is not a neat way to code.
Code:
With Target.Validation
.Delete
.Add xlValidateList, 1, 1, Formula1:="1, 2, 3"
.InCellDropdown = True
.ShowInput = True
End With
As many of the dropdown list boxes are similar in nature, with the only exception that the list content is different, I wanted to code a sub routine to include the code above.
My subroutine looks like this now:
Code:
Sub listbox(cellref As Range)
Set Target = Cells(cellref)
With Target.Validation
.Delete
.Add xlValidateList, 1, 1, Formula1:="1, 2, 3"
.InCellDropdown = True
.ShowInput = True
End With
End Sub
The problem is that when I call the subroutine with a
Call listbox (10,10)
It kept giving me a compile error.
I would like to create a listbox at cell row 10, column 10 of the worksheet.
View 9 Replies
View Related
Feb 1, 2010
I'm from Macedonia, we have to values for VAT, 18% and 5%. I try to separate values that have 18% VAT and 5% VAT. I'm using IF function but not is that I need
I'd like to SUM all cell from H2 to H24 that have 18% VAT in H27 and in G27 I'd like to SUM all cells that have 5% from H2 to H24.
View 3 Replies
View Related
Jul 18, 2009
For Each wksht In ActiveWorkbook.Worksheets
i = i + 1
ReDim Preserve wkshtnames(1 To i)
wkshtnames(i) = wksht.Name
x = wksht.Range("A1:B2").Value
Next wksht
View 9 Replies
View Related
Nov 16, 2011
I'm trying to automate a search and match function between two separate workbooks using VBA and am having some problems. I'm using the following line:
search_results = Application.Match(temp, Range("E1:E900"), 0)
which works fine. now the problem is that when i'm searching for lets say "ABCD" in one of the workbooks, if there is an entry "ABCD " (with a space at the end) it says that the entry doesn't exist. is there any way of correcting it such that it would always neglect the space at the end if it exists? i tried using -1 instead of 0 and it messes up, so i'm not sure what to do.
View 2 Replies
View Related
Mar 28, 2014
UREC Bar Event Sheet.xlsx
I am trying to correspond prices with products from the validate function in multiple columns. I want to select the product and have the price for the product populate in the 'cost' column. I have the list of products with corresponding prices on sheet 2 named 'products'. I have attempted to write a formula in cell G11 on the Event Sheet, but was unsuccessful. Please direct my path.
View 3 Replies
View Related
Feb 19, 2014
I need a VBA function to extract number sequences from a string and separate them with hyphens In the example below cell A1 has the value 'xx2 yyy34 zz515' The code must produce the value '2-34-515' from the above example I have the following function that extracts the numbers but need a way to separate the groups with a hyphen
Code:
Function parseNum(strSearch As String) As String
Dim i As Integer, tempVal As String
For i = 1 To Len(strSearch)
[Code]....
View 9 Replies
View Related
