array formula

Hello, I am looking for a macro to search column C and insert an array formula one row below every occurrence of "Resource Description." Then, in column A of each row that a formula will be inserted, there is a number - which calculates how many rows to drag this array formula down. After the...

I see this reference in a cell: {=TABLE(,B26)}. I know that the braces represent array formulas but TABLE is not a valid function. Does anyone know what this means? Your help would be appreciated. Joe

Hello all, this is driving me absolutely mad, MATCH is returning the wrong value, for only this set of data. <tbody> 0.59 30 0.37 35 1.31 55 25 0 30 0.59 35 0.37 40 0 45 0 50 0 55 1.31 60 0 65 0 70 0...

Hello, I have a custom UDF that requires a 2-element array in one of the parameters of the form: {string 1, string 2}. If I construct the formula such that the array is a 'hard' copy, i.e. =MyUDF(param1, param2, {"abc", "def"}) then it works perfectly. However, I want to be able to pull the...

Hi there all. Firstly, Happy Christmas all!! Hope you really enjoy it. Secondly, I waa planning to wrap up my year by incorporating buttons into my worksheets for faster and effortless use and activation of all the cool macro's i have found and developed over the years, here. ( As well as...

Hi I'm not sure how to word this so bear with me! I asked a while ago how to find the first instance of "" using MATCH, to which I got a great reply... ' =MATCH(TRUE,INDEX(A1:A20="",0),0) ' This works perfectly and is entered as a normal formula (rather than an array formula -...

Hello, I've learned a lot from people smarter than myself on this forum, so I'm here to learn again. Preface, I will try to be clear and concise, but there are several conditions and complications here: I have a table (~70 columns, 500 rows and counting) that is exported from a...

I have a report I make for every month end. We dump tons of raw data out of SQL and then I slice and dice it into a table that the customer wants. We're talking 200k rows of info across 25 columns. I have to conditionally sum certain columns based on many logical checks on other columns. For...

Hello to all, Please your help. I have items that appear several times in column A and respective values in column B. I want to show as headers from D1:G1 the values in column A and all the values for each element. May you help me with a Excel formula to get this goal please. <tbody> A B...

<tbody> Account Code Jan Feb Mar %Fixed %Variable 6X $100,000 $50,000 $25,000 100% 0% 5X $200,000 $250,000 $50,000 25% 75% 4X $150,000 $100,000 $0 50% 50% </tbody> I have sheets relating to various cost centers and each line is the spend for a specific account. Let's call the above...

I'm having trouble with inconsistent calculated formulas when adding new rows in a table in Excel. For context, I adapted this video on how to create a searchable drop down list in Excel for a project I am working on. https://www.youtube.com/watch?v=vkPoViUhkxU I'm designing a template which...

Hi everyone I've been trying to create a spreadsheet that tracks our golf group (boring I know!). And I have a table ranking the players that have won the most match points, made the most pars, birdies, etc, over the course of the week. I can use INDEX and MATCH functions to find the highest...

I am trying to write an array formula that will return the row number for the row containing the maximum number of values in a set of data. For example, I would like the formula to return 3 for the following as row 3 contains the maximum number of data entries. <tbody> 1 1 1 1 1 1 1 1...

I am trying to write an array formula that will individually count the number of values in a set of rows and return the maximum count. For example, I would like the formula to return a value of 4 for the following table as the maximum number of values in a single row is 4 (row 3): <tbody> 1 1...

Hello, I am working on a model and have VBA code: Copy/paste a range ("A21:L30") from a template worksheet to multiple workbooks that begin with "Labor BOE", multiple times based on a number in cell "L2" For example, if "Labor BOE 1" worksheet has a "3" in cell L2, then it will...

Hi, I posted a similar question a few days ago and I thought I had solved the problem but something else came up. Basically what happens is my last row keeps changing as I run my array formula macro and it gets bigger and bigger every time the formula runs and as such after a few times using...

Hi All, I have an array formula as below that works as intended, but is really slow to calculate values due to the size of the dataset. Therefore my though to speed this up is to convert it to VBA. I have searched, however am struggling to find any examples online. Therefore any assistance...

Hello, I'm trying to use the following code to evaluate an array formula across a range of cells: Sub EvaluateTest() ActiveSheet.Range("D2:G2").Value =...

Hello, I am trying to apply the following array formula to a range in Excel: =IFERROR(INDEX(AllFiles!A$2:A$1000000,SMALL(IF(AllFiles!$C$2:$C$1000000=$A$2,ROW(AllFiles!A$2:A$1000000)-ROW(AllFiles!A$2)+1),ROWS(AllFiles!A$2:AllFiles!A2))),"") using the following methods that I found online (I'm...

Hope I present this problem in an understandable way... 1.) DATA I have an "up counting", table like this, which restarts the count from 1 "randomly": <tbody> Count 2 3 1 2 3 4 1 2 3 1 2 1 2 3 4 </tbody> 2.) GOAL It is easy to find the maximum: 4 and...