dynamic range

  1. K

    Issue with copying dynamic range

    Hello All, I'm trying to copy dynamic range without copy-paste sequence, where the position of last column is dynamic. To test the correctness of my code, I'm using fixed ranges now and this code works well: Sheets("Data").Range("A1:J1").Value =...
  2. B

    Replacing cell value with a range of cell values

    I have data on Sheet1 in column A which is inputted dynamically via a userform. The data exists in column A like: Column A AB1234 BC1256 GF4567 I want to copy this data to sheet2 which has another set of data inputted dynamically via a second userform. I want to copy the data, to...
  3. E

    Max Difference Between Two Dynamic Ranges

    Let's assume our example data set is as follows: Column A1:A9 10 12 14 12 10 15 20 25 20 Column B1:B9 10 10 11 11 9 9 11 11 10 I'm looking for a one cell formula/vba code that will show the maximum difference between the maximum value of a dynamically increasing range in column A1:A9 and...
  4. J

    Copy and Paste dynamic range

    I've got a workbook with 48 used columns. Of those 48 used columns I need to select and copy a range covering the contiguous first 29 columns. I need to be able to select which row to start the range on by using an inputbox and a find/match loop until the given string is found and a cell address...
  5. A

    Using macro to sum dynamic range with result in cell 2 below last row of dynamic range

    I'm trying to take a recorded macro which results in absolute ranges and apply dynamic ranges to the code. In this case, I need to sum a dynamic range and then have the result appear two cells below the last row of the dynamic range. In this example, I would need to sum the totals of A1:A4 and...
  6. B

    dyamic array/range - rolling equation

    I have a few inputs in A1:B3: months 20 Price per month 30 cost 180 then in: A5 =sequence(1,b1,1,1) A6 =sequence(1,b1,B2,0) A7 =If(A5#=1,b3,0) This gives me a useful and dynamic table. However, I would add a rolling P&L from A8 for the length of the number of months? Below is the result that...
  7. P

    Multi-variable lookup across range of variable sheet count

    I have a three-sheet workbook intended to distribute tasks to employees. Sheet1 is a dashboard, Sheet2 is a table with primary tasks (some 1000), Sheet3 is a table with secondary tasks (some 1300). The employees are imported from another workbook via a PW-protected button as new sheets that they...
  8. Y

    COUNTIFS - count if value exists in a second range

    Hi, Hoping someone can help - I'm trying to add an additional condition to the below formula to check whether the value exists in another (dynamic) range, however the best I seem to be able to get is that it compares value 1 with value 1 and value 2 with value 2. For clarity, "main_data" is...
  9. btadams

    Calculate Percentile on dynamic named range

    Hello Everybody! I'm trying to calculate a percentile on a dynamic named range that references two non-contiguous ranges and not having success. First I tried using a static named range and it worked. Then I made the named range dynamic, NMFL_Load, using...
  10. C

    Setting Reference as dynamic range from inactive sheet

    Hello all I am hitting a brick wall. I am trying to create a reference based off of a dynamic range, however if the sheet is not active I keep getting an error for out of scope or application-defined or object-defined error. The dynamic range is made through special cells since the data often...
  11. H

    Dynamic variable cells in Solver VBA macro

    Hello, I have a macro that runs Solver in Excel, it currently uses two different ranges as the variable cells. See the code below. SolverOk SetCell:="$D$27", MaxMinVal:=2, ValueOf:=0, ByChange:= _ "$C$17:$C$23,$C$7:$I$11", Engine:=1, EngineDesc:="GRG Nonlinear" SolverOptions...
  12. L

    Chart: 2 columns of x-axis values and y-axis values but want one series

    Hi, I am trying to make an line/scatter line chart for trend analysis where there are x-axis and y-axis values in two different columns. In the attached image, I would like to use columns D and I as x-values and E and J as y-values. However, I want the points to display in order of the x axis...
  13. D

    Compute FormulaR1C1 on anther sheet (dynamic range)

    HI to all, here another question: I have attached the Cotizaciones (sheet 1) , Retornos (sheet2) and Descriptiva (sheet3) . From data in Retornos (sheet 2), for each column (are dynamics) starting from B3, I have to compute calcolation of average and the volatility (until last row for each...
  14. P

    INDEX range and INDIRECT not working inside LET functions

    Hello, I'm calculating Split Multiplier column from Split Ratio column by multiplying the rows from the current row towards the end of the column. It works nicely within normal excel table with PRODUCT($B3:INDEX(B:B; ROWS($B$3#)+ROW($B$3)-1) ) where the formula is copied down, but if I try to...
  15. S

    Dynamic range with locked cells?

    I'm trying to create a user-friendly timesheet for someone who has never used Excel. How can I create a dynamic range linked to a pivot table with the formula cells locked? I can do dynamic or locked cells but not both. What am I doing wrong? And I'm VBA ignorant, can this be done manually...
  16. A

    Countifs with dynamically altering criteria

    Hi Guys, I am trying to create a formula where I can draw data from a dataset which has 3 fixed ranges, 2 fixed criterion and 1 dynamic criteria. As I have shown in the uploaded images, there are the months, types of items and customers. All this data is drawn by a VBA code to the raw data...
  17. D

    Macro to delete rows based on cell values in a range - dynamic rows and columns

    Hello team, I would like to ask for help with the following macro as I just can't figure it out, I tried so many versions but nothing worked for me. I need to remove all rows in my sheet that do not contain value greater than 50 in any cell in a given row. That means that if the value is less...
  18. B

    naming a dynamic range and using it in power query

    I have a sheet in which there would be a dynamic range with static columns and dynamic rows, Range("A1:Y1892").Select **(need this range to be dynamic, which can be furthure used in power query) Range("J19").Activate Application.CutCopyMode = False...
  19. D

    Dynamic Scatter chart based on criteria

    Hello, I want to create a X/Y scatter chart to plot projects Final Amounts Invested amounts & % and use slicers to select one or more than one location. I have numerous locations and each location has a different number of projects to be included. This number of projects changes each month...
  20. K

    Calculating multiple averages of periods indicated by increasing values

    Hi all, I have a problem, I want to calculate the mean of daily production over a period of time, but this period is very different per case. Below you can see one case, in reality the row is much longer but for practical reasons in shortened the different periods. The top row indicates the day...
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

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