Passing Cell References From A Cell's Text To LINEST Function And A Graph
Mar 2, 2009
I'm using Excel 2003 and I'm trying to make my LINEST function and the source cells for a scatter graph use the cell range specified in another cell in the document. I figured out that it was simple to do the following:
But I can't figure out how to do this in a larger function. I've tried a few things but none of them work. Here is an example where I want it to do a LINEST with y values in cells Ja - Jb and x values in cells Ia - Ib (where a and b are integers specified in cells I250 and J250 respectively):
I want to select a cells value from a Data Validation Box ( Achieved ) then based off of that selection ( value ) pass it into a slicercache, as follows :
Select a value from a drop down list in cell A1 Based on selection update slicercache by passing the value of A1 to it
So far i have : The bold is the part where i need to replace with whatever is selected from cell A1. Must be possible ...
With ActiveWorkbook.SlicerCaches("Slicer_Contract_15") .SlicerItems("BRUTE").Selected = True End With
Would be best if this code ran on cell A1 click ...
Is it possible to replace the Pivit table name/reference in a GETPIVOT function with a cell/range reference? I can on other components of the function call, but not that. For example ... the "hardwired" function call might be:
=GETPIVOTDATA(" Sum of 1991",'[EE_financial data.xls]Sheet1'!$A$3,"Company","ERG","Item"," Depreciation & Amortisation")
I can achieve the same result by externalising the 1991 and ERG, so i replace this with other dates or names, thus:
ERG 1991=GETPIVOTDATA("Sum of "&C13,'[EE_financial data.xls]Sheet1'!$A$3,"Company",D12,"Item"," Depreciation & Amortisation")
.. and this works fine.
However, if I try and replace the '[EE_financial data.xls]Sheet1'!$A$3 with a reference to a cell containing that string, it returns #REF!
I have a spreadsheet which shows a matrix in pounds. But I would like to have a reference cell somewhere near the top which will automatically show the conversion to kilos of any selected cell. Is anyone aware of a function which will do this?
For example, one pound equals 0.45359237 kilos. Cell A1 contains the reference function to the active of selected cell. If I click on B1 which contains 12, cell A1 would automatically show 5.45. If I click on cell C1 which contains 20, cell A1 would automatically show 9.09.
So I’m thinking it would be something like =SelectedCell/0.45359237 or maybe =ActiveCell/0.45359237
I'm trying to create a dynamic COUNTIF function that references a previous cell.
For example, if I have 10 columns:
A B C D E F G H I J
a 1 b 2 c 3 a 1 b 3
I am trying to write a function that will let me reference A1:J1 and then if the cell contains an A, do a countif among the cell+1 for cells that have a value of "1". In this example, that function would return a count of 2. I think that I need some kind of combination of IF, INDIRECT, and COUNTIF, but I'm not sure how to string them all together--or maybe there's another way altogether.
Any way to use a find and replace function involving a cell reference. I have a spreadsheet with ton of junky text burying information that I want. Here is a simplified example
A B Book 345353hg dgdgsdgfd fff Book belongs to Jim Sneaker fdg4 Sneaker is worn by Jan Gum dfg s d e te4345Gum tastes great Cake jklsjflsjfjikCake smells delicious
The pattern is that the A1 text appears in the B2 text. What I want is
Find *A1 Replace with blank Find *A2 Replace with blank and etc.
So that in the end, I get this:
B2 belongs to Jim is worn by Jan tastes great smells delicious
I've looked at the functions of find, replace, substitute, left, and right and I can't seem to find the right one to do what I need.
I need to concatenate some standard text with some cell references but I can't use quotation marks as I would in the normal excel function as it produces an "expected end of statement" error message.
Is there a way around this?
What I want to say is:
Range(B23).FormulaR1C1 = "CONCATENATE(“With ”, Info!C18), “ entries, your expected rate is: ”, H21)"
I'm working in Excel 2010. I'm trying to create a dynamic range, using the OFFSET function. I've got it working beautifully but as soon as I save it and close the Name Manager the range stops working. When I go back into Name Manager I find all the cell references have changed (from C1:C600 to C1048572:C595 - or some other strange range).
P.S. What it's doing isn't so important as when the cell references are right it works. But just for full info: It's an OFFSET function, starting at A1. It will look at a list of companies and use a Match function to find out how many rows to offset (based on where the first instance of a particular company. And it uses the COUNTIF to make the range the same number of rows as there are entries for that company.
I can do least squares fitting utilizing LINEST and a worksheet. (See attachment.)
I would like to do the same calculations entirely within VBA.
I can specify the values as arrays within VBA :
Sub linest2VBA() ' ' linest2VBA Macro ' ' Keyboard Shortcut: Ctrl+Shift+A ' Dim dXvalSqd(4), dXval(4), dYval(4), dFirstRegStat As Double Dim i As Integer Second Question:
Is there a way to get the remaining regression statistics within VBA by referencing only the three arrays: dXvalSqd, dXval, and dYval?
I am creating a series of charts that all include a text box containing "(N=[value])", with different values in each instance, but with each N value listed as a total frequency in each table. I am creating hundreds of these, so if there is a way to automatically pull the correct N value from a cell into the text box, it would make this faster. I have written simple functions in excel tables but have no experience with VBA/macros.
I am trying to use the LinEst function in a program. There are some good examples on the web but they all refer to a range in the format “A1:A45”. Is there a way to refer to a column in an existing array instead?
I have set up regression in excel using the linest array function which automaticly updates whenever the data it is references changes. Currently the formula in K3 = =LINEST(K8:K50,M8:N50,TRUE,TRUE) .
Only the Y range in column K changes and then the formula automaticly recalculates the regression parameters. However sometimes the range of valid data changes with each new update so currently K8 to K50 contains valid values and so the formula works. But if the range of valid data now ends at say K45, and the remaining rows (K46- K450) contains #DIV/0!. In this case the formula does not work and I need to go in manualy into K3 and reselect the range to get it to work (in the baove case to =LINEST(K8:K45,M8:N45,TRUE,TRUE)
Is there anyway I can get the formula to update the range (the number of rows to use) in the calculation automaticly so I don't have to go into the formula in K3 and change the range manually everytime the data changes? Basicly I like to use all rows in column K (from K8 to K50) where there is a values and ignore cells with #DIV/0!
I'm guessing the easiest way to do this would be to set up a dynamic range of some sort that feeds into the linest array function so that the end row reference for columns K and M & N changes depending on where the first #DIV/0! occurs in column K.
It is suppose to be that if the employee is "FT" and has worked >=4 years the return is 15. But if the employee is FT and has worked 2 years but less than 4 years then it is suppose to return 10 (these are days off) Or if the employee is FT and has worked 1 year, but less than 2 then it should return 5 days off. And all the others in the column get no days off.
I have tried to do it with structured references and with cell references I get a column of zeros!
I'm trying to do a formula that references a cell and returns a different result dependant on the number in the cell being referenced.
For example I've said if A1 has a 3 in it then put the word TEST as the result, plus if it has a 4 put the word RESULT.
What I wrote as my formula is as follows-
=IF(A1=3,"TEST")+IF(A1=4,"RESULT")
It works fine when I only use one result but goes wrong when I add two. If I change the words I want to show to numbers it comes up fine but with words it just returns a Value error.
I have 2 inventory reports: what my store has and what my supplier has. I need to copy Tab1:K# to Tab2:T# provided that Tab1:A#'s contents match Tab2:A#'s contents. The A column represents the SKU of the item, but there is a difference in the amount of SKU's in each (my store sells ~6,000 items, supplier has ~10,000 items), so it's not as simple as sort by column A and copy pasting column K to column T.
For instance: On Tab 1, A2's value is [1], K2's value is [9.38]. On Tab 2, A70's value is [1], K70's value is blank, but I need it to be [9.38], to match Tab 1's respective SKU.
I almost thought I had it figured out with VLOOKUP, but I cant seem to get it right... It doesn't reference the correct number.
Screenshots for reference
First tab, from the wholesaler: [URL] Second tab, store's stock: [URL]
Below is my code which isn't formatting the cells it's suppossed to. It looks like it isn't doing anything. I think the issue might be with the highlighted section of my code, but when I go to "Manage" my rules for conditional formatting, excel references the appropriate cell under the "Applies to" section. I am using relative cell references for for the majority of the rest of the code and this section follows a section that selects the correct cell for this conditional format.
I'm trying use a worksheet that contains named ranges as the source in a macro that will dynamically create pivot tables. The named range could be anything so it can't be hardcoded in the vba and has to come from the worksheet.
In my example, the string strI = "policy" which I know because the msgbox displays the string.
Code: Dim strI As String strI = Chr(34) & rngIn.Cells(1, 4).Value & Chr(34) MsgBox strI
Application.Goto (strI)
But when I try to use strI in place of a hardcoded named range, it always gives me (Reference is not valid) error using the string variable in the following code but never when it's hardcoded in as "policy".
Code:
'This errors out Application.Goto (strI) 'This works Application.Goto ("policy")
where Ship_30 name references the following- ='sheet 1'!$C$20:$C$38
The value of 20 is static, the value of 38 is dynamic. Each new time period adds another value (i.e. 39,40,41)
Having to edit each name range reference in the name manager is not acceptable since there is potentially over 30 to edit each month.
I would like to edit a cell which would have contained the value 38 (or C38) with a new value of 39 (or C39) so the range reference would be updated with the new value.
The goal would be to have a couple of rows with the following that could be edited with new values
how to remember or pass the place where the user doubleclicks. I am trying to make a form show up when a certain range is chosen. For now this range is static, but it will be dynamic in the future. Once the user double clicksa cell in this range, the form pops up.
Code: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Not Application.Intersect(Target, Range("M4:AF23")) Is Nothing Then Cancel = True
Not sure if possible but im trying to pass a range to a formula in excel from another cell.
[URL] .......
There is a sum formula in range B5 that sums the range (B9:13). I would like to have a formula in cell D5 that passes the new range in cells(D9:D13) to the formula in cell B5 and give the result in cell D5. Is this possible using Excel functions or would i need to create some sort of user defined function for this?
I am having a problem with a run time 13 error on a variable assignment. The variable is being assigned a value from a cell that contains a formula, and I am suspicous that perhaps the mismatch is coming thru this. The de-bugger stops on:
Area = ActiveSheet.Cells(count + 41, 8). Here is the complete code (with all my poor coding skills!)
Sub Reservoir() Dim Inflow As Single Dim Withdrawal As Single Dim Evap As Single Dim Area As Single Dim Spill As Single Dim Stor_1 As Single Dim Stor_2 As Single Dim count As Integer Dim max As Integer max = ActiveSheet.Cells(9, 4) For count = 1 To max * 12 Stor_1 = ActiveSheet.Cells(count + 40, 11) Inflow = ActiveSheet.Cells(count + 41, 4) Withdrawal = ActiveSheet.Cells(count + 41, 6)..........................
way to use a function to remove text from a cell. I cannot use VB code, due to the spreadsheet being integrated in an operational software application - already tried it and get nothing but errors from the application. Was also trying to use a Find and replace function, but it's not working.
Here's the scenario - I have a cell that returns the weight of a product from the system, but it includes "LB" or "KG" in the cell along with the weight. I do not know how many characters will be returned to the cell, as it depends on the product. It could be a single digit up to 10 digits, including a decimal point. I need to clean up the cell to only a number, because in the next function, I am trying to round up the number that is left in that cell (won't round up with KG or LB in the way).
It seems to work. Are there any problems with it? Is there a better way?
Code: Function myRangePassExample(RangeA As Range) As Double Dim ArrayA As Variant, ArrayB As Variant Dim iLo As Integer, iHi As Integer, i As Integer Dim jLo As Integer, jHi As Integer, j As Integer Dim Sum As Single ArrayA = RangeA.Value iLo = LBound(ArrayA, 1) iHi = UBound(ArrayA, 1)
I have been searching for hours trying to find a solution to no avail. What I need to do is make sure that when I press a button on a form, the value in a combo-box should be populated as "text format" not as a number. The reason for this is that when my numbers start with a 0, the leading zero gets dropped and I can't run the rest of my code as the items don't match...
I have tried substituting the "Value" to "text" in : Me.cboItem.value and have tried for hours trying to name dim variables as a String and pass this into the cboItem...with no luck...
VB:
Private Sub CommandButton1_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Order") 'find first empty row in database iRow = ws.cells.Find(What:="*", SearchOrder:=xlRows, _
[Code]...
The below populated the combo box cboItem:
VB:
Private Sub OptionButton1_Click() With Me.cboItem .Clear .List = Application.Transpose(Worksheets("Item List").Range("List1")) End With End Sub
how to pass a cell content into a formula as a named range? For instance, I have 3 named range (Budget, Actual, Forecast) and I use the named range in vlookup formulas. But instead of using the named range in the formula directly, I want to refer to a cell where I can type in which named range I want to use. Can this be done? I am attaching a sample spreadsheet to clarify what I mean.
I want to know how to index a drop down list to be used for calculations.
Up until now I was using
Range("B1").Select 'the cell where the drop down list is ActiveCell.Formula = SiteID 'site id
But for whatever reason it trims a trailing zero from SiteID when setting it in the second line. SiteID is a VBA string.
Indexing it would work, as would preserving the zero so help on either one is appreciated. Auto Merged Post Until 24 Hrs Passes;Correction, I lied. I was using this line to set it.
Range("B1").Select 'the cell where the drop down list is ActiveCell.FormulaR1C1 = SiteID 'site id
I am trying to put text form 3 cells into 1 string. I got the 1st 2 but the 3rd I only want to extra the first part of the cell up to the 1st comma. This is what i have so far:
[Code] ......
For example cell E81 would contain "abc, def, ghi" . I want to add to the string above only "abc" and not the entire cell content.
The cell name "5260", is also entered as text in cell A1, in the current workbook (not calculos.xlsx). How do I refer excel to get the text from cell A1, instead of having to enter it manually.
