dynamic cell range

  1. D

    Dynamic Ranges

    I have the statement below that I hardcoded the values I165:L165. Each time I create this report I have to change the values (165 to the last row). Is there a way to dynamically change them using xlUp since the column IXXX is the last row in column I and the L165 is the same row just over 4...
  2. S

    CountA with a Dynamic Range

    I am trying to determine the number of active months for personnel on a rolling 12 month basis. This is the formula I'm currently using but am getting an incorrect result value. I'm new to offset so I know I must be missing something simple. =COUNTA(OFFSET(D2,0,COUNTA(D2:AA2),1,-12))
  3. Rymare

    VBA Populate column with formula

    I have a dynamic range, it changes all the time, and every time a value looks like so: #####-### (or in my formula I put ?????-???), I need this formula: =IF(COUNTIF(A2, "?????-???")>0,MID(A2,5,1),"") to be placed in the corresponding row in column C, because I need to extract the 5th number in...
  4. L

    SUM IF Dynamic range

    <tbody> Name Department Wages 401K Smith, John Admin 10,000 200 Lavine, Avril Sales 500 10 Turner, Ted Admin 100 10 Fonda, Jane Sales 750 75 </tbody> I want to use an index match/sum if formula to sum the 401K column. The problem is that my data's columns maybe in a different...
  5. J

    concatenating and formatting dynamic number of strings

    So here is what I'm trying to accomplish. There is a number out to three decimals in column A. Date in column B (mm/dd/yyyy). Say I have this data: <tbody> 2.300 8/1/2018 2.400 9/1/2018 2.850 10/1/2018 </tbody> I want to concatenate this strings to where I get a single string of "2.33...
  6. L

    Find and paste data into first blank row in table

    I am working on a macro that needs to be able to pastedata into a table. The macro does several different filters and after eachfilter the results should be copied and pasted to a table. The issue I amhaving is the table it is copying to starts on B17 (there are blank rows andother tables above...
  7. P

    Dynamic Print Range With Print Preview

    New To VBA I wrote a macro to print a specific range on a specific sheet: Sub PrintPreview() If ActiveSheet.Name <> "Sheet2" Then _ Sheets("database").Range("C1:AE12").PrintPreview End Sub However, the table I am attempting to print will have additional rows added (number of columns will...
  8. M

    Pivot Table Dynamic Source Range

    Hello! :smile: How would I edit this code to reference a dynamic range instead of a specific table? I tried modifying what Record Macro gave me, but did not have any success. Any assistance would be greatly appreciated! Sheets.Add ActiveSheet.Name = "In Program Pivot" Dim tri As...
  9. A

    Loop Through All Files, Manipulate and Copy Data to Summary Workbook with VBA

    Hi all, I am working on something to help me analyze a bunch of queries in an Access database. To motivate this I exported all the queries to .csv in a particular folder. I want the output to be in a format so that I can filter on the query and pull up the associated SQL statements for quick...
  10. J

    how to set dynamic range with this code?

    Sub test() Dim f As String Dim ab As String text= ThisWorkbook.Worksheets("Sheet1").Range("F1").Value filter = ThisWorkbook.Worksheets("Sheet1").Range("AB1").Value If text = filter Then ThisWorkbook.Worksheets("Sheet1").Range("S1").Value = "xxxx" End If End Sub I have code like that, but...
  11. N

    Help with Counting by Dynamic Range & Skip Blanks

    Hello, I have a reference sheet linked to a Sharepoint file similar to the format below. The blanks do contain the reference formula which is causing issues. I need to figure out how to get the percentage of data points above 1.1 based on the last 10 days with values. So for example, 11/15/17...
  12. K

    Alphabetically Sort a dynamic range based on multiple filter values

    Hi all I am struggling to get an Array formula to sort based on Multiple search values. I have 3 List. when the excel was first written I only had List B and C and i used the following formula to create a result list of just the references in list C...
  13. P

    VBA - Open certain sheets based on a range

    Hi there, I'm looking for advice on the best way to go about constructing a module to get my desired result. I have a commissions file that i want managers to be able to go in and review the commissions. Each manager will have a username and password. Each manager will require access to...
  14. V

    Satisfy if condition on Dynamic columns

    Here is my question. I am looking for a value "Y" in Columns Y: AW and if Y then return " N " else "Y ". I got it to work in cell AX2 using formula IF(COUNTIF($Y2:AW2,"Y"),"N","Y"). Having trouble to dynamically update columns when new columns are added after AW . I need column Y (the...
  15. F

    Linking Excel to Powerpoint

    I am trying to write VBA which will allow dynamic excel ranges to be exported to (possibly) an intermediary file (open-minded about file type) and for these ranges to then be linked to a PowerPoint presentation. Currently, the intermediary files are PNG pictures but the print quality of the...
  16. 7

    copy data values in a Name Range to a new worksheet

    I want to in same workbook. The name range is called ReportCopy and the range formula is a dynamic =OFFSET('C_Register'!$H$1,,,COUNTA('C Register'!$H:$H),6) The Macro I am trying to us is: ' ' Sheets("C_Register").Select Range("ReportCopy"). Selection.Copy Sheets("Report").Select...
  17. M

    Updating a formula with a dynamic range

    I'm running into a wall trying to accomplish updating a static formula in Cell N2. The first piece of my script works due to the start of my range will always be I19. I have a formula in N1 that is based off of range I19:the last cell before a blank (RowNum) and that works great. My issue is I...
  18. T

    Example of OFFSET when data is stored in rows not columns

    I have been trying to find an example of the OFFSET formula for a dynamic range for data stored in rows and not columns. For example Date is stored in row A (A3:Z3) and Score stored in row B (B3:Z3). All I can find is examples of data in columns. What I am trying to accomplish is to create a...
  19. S

    Dynamic "Copy And Paste"

    Hi all, I’m new to VBA and need some help with copying dynamic ranges from Sheet2 to Sheet1. In Sheet2, I have 5 dynamic ranges. Named in col."A" as: Dep-1, Dep-2, Dep-3, Dep-4, Dep-5 Each range have data in column A,B,C,D,E and I. Below range name (e.g Dep-1) are some rows with info. Then...
  20. J

    Index array with dynamic range using Match Function

    Hi all, As with the screenshots below I am struggling (in my head) to figure out a way to turn my 'Product!$AK$31:$IQ$31>0' & 'Product!$AK$29:$IQ$29' into a dynamic range (PIC1) i.e. I want to be able to insert a Match formula to reference cell AE57 - 'Panel_1' & "Product Description" which...

Some videos you may like

This Week's Hot Topics