dynamic ranges

I have 2 dynamic arrays; one for the first column of data (vertical) and a second for a header row (horizontal) which together make up a grid view. I need a formula that will spill dynamically when there are changes to either the horizontal or vertical ranges. I want to use the combination of...

I'm trying to Autofill Column X and Column Y with text Where Column Z determines the table length. Starting cell for column Z is "Z3" But the starting point of column "X and Y" are dynamic (due to previously imported information) (Column X & Y is never longer than Column Z). The last filled...

Hello, I have one excel file with several sheets of data and one final sheet XYZ dedicated for reporting. I have one sheet named ABC and there is a dynamic range which will change in the future - the number of columns can be different per each update of the file. I want to do a COUNTIF within...

I know that in order to use Sumproduct in VBA I need to have the results set as value. For example, here's a code that I have been using and it works great: With ActiveSheet.Range("P" & LR + 1) .Formula = "=SUMPRODUCT($N$16:N" & LR & ", $P$16:P" & LR & ")/SUM($N$16:N" & LR & ")"...

Hello, First, all of you have been a huge help over the years. This is my first time posting, so I hope this is not incorrect in any manner. I appreciate any assistance. I am creating a VBA automation step for peers that have to manually enter in contract information for each of our...

I have created two charts in my source worksheet Sheet4, but I want to adjust the range for 42 worksheets. I'm less than a week old to Bloomberg, so didn't know the best way. The data positioning is the same for all the worksheets. I've added secondary axis etc, so want the copy and paste to be...

I've looked everywhere and cannot work out the way to reference the sort key in my dynamic range. I believe it's relatively easy but my VBA still not up to it. I have a dynamic range that I want to sort using a column within the range. The range is A18:AR25, refers to...

Hi, I have an excel table named incomeTable like this: <tbody> name spend income mario 3 10 roger 4 5 tom 5 4 </tbody> I'd want to access a given column and a given range of rows (but getting the column name from a specific cell). I.e., Let us say I have in A2 the column...

Hi guys! I have a table ("B:G") where the column headers are weeks ("C:E"). And in column G I have different dynamic values. I am trying to multiply the values in range C:E if the criteria in column B is met. So, in short this is what I am trying to do: If value A1 = "dollar" and If column...

Please help if anyone has any ideas; I have set up an offset to make my print range dynamic (Offset($F$1,o,o,$J$1,4) so as the table expands the print range expands. CountA(I:I)+4 is in J1 to get row count. This works great, however, each month I copy the table and insert the copy in front of...

Hi all, I am working to automate as much of a report as possible. The data will be exported directly to excel, and the data sheet will be manipulated to create a main table that drives all summary views. Originally, I planned to use the indirect function to make the lookups simpler, but haven't...

Hi I am trying to switch from counting Rows to counting columns in VBA and something isn't quite right. This is what I have come up with (including comments for explanation) Sub Faffing_Around() Dim newCol As Integer newCol = Cells(1, Columns.Count).End(xlLeft).Column + 1 'I have row one...

I'm trying to count the instances value are less than zero given two dynamic column ranges match. That's a mouthful. These are named ranges 'universe' and 'planets'. I have the following formula to give me the total number of instances that I have a match however I'm not sure where to include...

Hi All, I have a macro that will sort rows 8 to 57 by columns D, E, and F Sub Sortcolums() Dim rCount As Long rCount = Evaluate("COUNT(" & ActiveSheet.Name & "!D8:D57)") With ActiveSheet.Sort With .SortFields .Clear .Add Key:=Range("D8").Resize(rCount)...

Hello again! This community has been so helpful with my previous problems I've returned with another. This could be something simple; however, I can't seem to figure it out. I stole this code (off this site?) and I'm trying to re-work it to compare two columns based off of the position of...

Hello, I am trying to piece together a macro for a WorkBook where the end result would be to copy a form on Sheet"InnoluxRMA" to a new workbook and save it as "InnoluxRMA_{DyanamicRangeValue from Sheet"List"}_DateStamp. Sheet"List"'s data will always be column A, with a header row. Right now I...

Hi All, I have multiple data sets on different sheets, all with the same column headers. I have dynamically named these sets so they change height at times. I can't seem to get a fully function Pivot Table (at least in my terms). I can get one that doesn't offer very much flexibility, but I...

I have a worksheet that uses the INDIRECT formula to select data from another worksheet dependent on the "value" typed as text into a cell: = INDIRECT(""&A1&"!A1") Is there a way to create a dynamic range in column A that adds names (text) as I add a sheet? EXCEL Preferred, VBA OK I am new to...

Heya, There is probably an easy solution to this that I'm missing, but I've got a long series of numbers that can change values (Anywhere from 0-9) for example purposes lets say A1:A30, and in the row below I've got values generated from other formulas. I need to try and find the sum...

Longtime fan, first time poster; apologies if I somehow messed up thread etiquette. Using Excel 2010. For various reasons which aren't worth going into (basically I need to protect/lock an entire table with the exception of one column, but still allow sorting) I need to make use of Excel's...