dynamic ranges

  1. C

    Excel formula to dynamically reference a range using values from a horizontally and vertically spilled 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...
  2. M

    Autofill Dynamic Column with Dynamic Starting Point

    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...
  3. K

    COUNFIF within a different sheet with dynamic range

    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...
  4. D

    VBA - SUMPRODUCT with SumIf With Dynamic Ranges

    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 & ")"...
  5. T

    Create List of All Missing Values from 3 Dynamic Worksheets with Criteria

    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...
  6. S

    EXCEL VBA Charts, Dynamic Chart Range

    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...
  7. P

    Sort Dynamic Range - help please

    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...
  8. D

    Access table by column name in a cell, and row number specific ranges

    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...
  9. M

    Multiply cell values with adjacent cell value in ranges if criteria is met

    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...
  10. L

    Dynamic Print Range

    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...
  11. A

    Using Data Validation Drop Down, Dynamic Ranges, Lookups, and Data Sheet to Build Report

    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...
  12. K


    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...
  13. G

    Countif for multiple criteria given two dynamic ranges match

    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...
  14. M

    Sort muliple columns by named or dynamic range

    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)...
  15. H

    Comparing Columns inserting message based off of difference

    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...
  16. H

    New WorkBook Save As using a dynamic range list

    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...
  17. G

    Combine Dynamically Named Ranges into a Pivot Table

    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...
  18. B

    Work sheet names to a range

    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...
  19. W

    SUM between varying points

    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...
  20. G

    Allow users to edit ranges - dynamic range issue

    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...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back