1. P

    Need workaround for Arrayformula breaking Indirect functions (google Sheets)

    I am using Google Sheets: I am trying to nest a formula that outputs a reference cell address based on the cells location within an array, and I wish I could just use the indirect function, but I know that the indirect function breaks when nested within the arrayformula. Here's my formula...
  2. T

    Using ArrayFormula with Spreadsheet Attached to Google Form Does Not Always Yield Correct Result

    I have a spreadsheet that needs to do X, Y, or Z (which are all various sums of columns of numbers) depending upon two variables: If A=5 and B<>0, then do X. If A=6 and B=0, then do Y. Else, do Z. I have a formula that works correctly without using ArrayFormula: =int(if(and(E2="5 courses each...
  3. M

    Single Cell Array Question!

    Assume the following: Cell A1 = 20 Cell A2 = 35 Cell A3 = 45 =SUM(A1:A3) = 100 But I want to have those 3 values within one single cell as an array like A4 ={"20";"35";"45"} and do = SUM(A4). However, when I do this, the result = 20. When I read the cell value of A4 with VBA, Range(A4).Value...
  4. S

    Performing functions on array variables

    Hi guys, First time posting, and hopefully an easy solution. I have a table called "Workbank" which is 33 columns, but will have a variable number of rows. One of the columns is comprised of alphanumeric strings such as: aa-###, aaa-##, aaaa-#. I would like to store these column values to a...
  5. M

    VBA Match+Index formulas call for multiple criteria

    Hi! I've using cycle macros for searching in each column certain value and then comparing values in adjacent columns in order to get values to show or similar matters At this moment I found out that there is a way to call formulas from vba code via Application.WorksheetFormulas and at the same...
  6. I

    'Unable to set the FormulaArray property of Range class' even though the length is 215

    I am trying to use the following Array Formula in my VBA. Range(Cells(2, "AF"), Cells(4, Col)).FormulaArray = "=IF(COLUMNS($AF2:AF2)<=$AE2,INDEX('Demand File '!$A$2:$A$3001,SMALL(IF('Demand File '!$CC$2:$CC$3001=CONCAT($J2,$K2,$L2,""Yes""),ROW('Demand File '!$A$2:$A$3001)-ROW('Demand File...
  7. O

    VBA: Copying Formulas and Array Formulas at the same time

    Hi, I am struggling with the following code, that in essence is trying to copy a range of formulas from one sheet to another: Sub CopySheetToSheet() Dim r, c As Integer Dim rngA As Variant 'HC Reference to Sheet5 Range Alpha Set rngA = Sheet5.Range("Alpha") r = rngA.Rows.Count c =...
  8. B

    Sum Formula Nesting with Multiple Functions

    Hello, I am trying to figure out how sum up two different functions into one cell. I have figured out the initial formula to sum the functions, I am using an array formula with the sum nested, however, I cannot figure out how to get just the one functions answer to populate if the second...
  9. P

    Array formula that captures data from multiple columns

    Hi all, Is it possible to create an array formula in a column that captures data from multiple columns? It is important that an arrayformula is used because I am using an online form that captures data and spits it into a Google spreadsheet. Every time the form captures a new entry, the data is...
  10. J

    Value of Array Formula into VBA

    Good afternoon, I currently have an array formula in my workbook which basically comes down to this (which does exactly what I expect / want): {SUM(IFERROR(((MONTH(C3:C100)=1)*(YEAR(C3:C100)=2016))*R3:R100,0))} It calculates the sum of a range (column R) when the date (column C) contains...
  11. D

    Run-Time 1004 Array formula help

    Hi, i keep getting the above error when trying to run this last bit of my code: Dim laddercount As Double Dim x, y As Double laddercount = Evaluate("COUNTA(R:R)-1") For y = 19 To laddercount + 19 For x = 7 To x + laddercount Cells(x, y).Select...

Some videos you may like

This Week's Hot Topics