ranges

  1. K

    macro list of name ranges includes some i dont want

    I have a macro for listing all name ranges in active workbook when a certain worksheet is activated. the code is Sub Rprt() Dim nm As Name, n As Long, y As RANGE, z As Worksheet Application.ScreenUpdating = False Set z = ActiveSheet n = 8 With z .[a5:O155].ClearContents .[a7] =...
  2. C

    End of Month / Max if is showing the incorrect date

    Good morning, I'm using the below formula to display the oldest date in a range (to the end of the month) if there is a % value in a designated cell range. =EOMONTH(MAXIFS('FA Afford'!I22:I33,'FA Afford'!E22:E33,">0"),0) In an older version of the document I'm using (where the cell ranges...
  3. A

    Referencing values from different tables and ranges

    Hi.. I need help to develop an excel formula in referring values from different tables and ranges and add the value to basic size and place the results in respective cells. the demo file is at below link, and also one small explanation of my requirement. Kindly help... Thank you in advance...
  4. A

    Consolidate Multiple Named Ranges Into One Pivot Table In Excel

    Hi all, I am having trouble with the consolidation of my named ranges into the one pivot table to finish a dashboard. I have got all the named ranges into the data source. Each named range is the same shape: that is, each consists of 4 columns. Columns are Amount, Animal, Location, and Colour. I...
  5. S

    Graph with Bars (Ranges) & Lines

    Hello, How would I go about making a graph which plots a number of bars (representing ranges) and lines on the same graph as per below: I would like the lines (which represent the 5th and 95th percentiles of the ranges) to intersect the range as shown in the image. Any ideas?
  6. L

    Indirect Function Alternative

    I started using the INDIRECT function to create a super dynamic Sumif that references a named range based on criteria. However, I noticed that my calculation time has increased significantly since taking this approach. I read online that this is a very volatile function that performs slowly...
  7. B

    Range or similar formula?

    Hi I have a spread sheet with a couple of thousand cells of decimal numbers between two values of 0 & 10. I want to use some type of formula that will put them into five ranges eg "0> & <2"=1, ">2 & <4" =2 etc. What is the best formula to apply in this instance please. Thanks BT
  8. G

    Delete all defined named ranges from a selection

    Is there a way to delete all named ranges from a selection? Here is the selection that I am trying to delete the named ranged from: Range("CompOverviewTable[[#Headers],[Competitor_one]]").Select Range(Selection, Selection.End(xlToRight)).Offset(-1, 0).Select
  9. J

    Defining a name for more than one range

    Dear, No problem for defining a name for a single data range. However, I have situation to make a frequency table using data in two separate ranges, for example B2:C5 and D2:E6. Is there a way to define these two ranges as one name so I can use '=frequency(the name, bin range)?' Thank you, joylee
  10. F

    Adding with VBA

    I'm looking for a way to add whatever number I type in my active cell to the cell immediately adjacent to it to the right. However, the part that gets tricky.. Is that I only want this to work in a range of cells. . . not the entire sheet. I have a tally of numbers in the range N5:N24...
  11. R

    Need to find is value is between multiple ranges

    Hi excel wizards, I need to find if dates are in holiday date ranges For example is 03/01/2020 within the date ranges below. I could do this with a nested IF statement but I'm determined to evolve beyond that! Can you suggest a formula to do this, please? 01/01/2020 05/01/2020 15/02/2020...
  12. J

    Clearing multiple ranges from multiple worksheets efficiently

    I have a large worksheet used for creating daily reports on a monthly basis. I am trying to create a macro that will clear the data entries in order to create a blank worksheet that can be used as a template. The workbook contains a lot of pages but the ones that are currently giving me a...
  13. M

    Force refresh/load Named Ranges on Workbook Open in Excel

    I have a lot of Named Ranges that are coming from our File Management system metadata. Usually on workbook open it takes some time (up to 5 seconds) to load these values to cells. I have some other processes linked to these cells. For example showing/hiding shapes if let's say cell A1 > 1. The...
  14. T

    Selecting and identifying ranges

    Hi all, I have just opened, solved, and closed this thread: https://www.mrexcel.com/forum/excel-questions/1110902-creating-varied-pivot-tables-based-range-name.html#post5348104 But in it I have had to hard code a range, because I could not work out how to define it properly for my needs. My...
  15. T

    List of Dates & Dynamic Name Range

    Good Afternoon, I currently have a list of dates starting in cell A2 which lists every day of the current year (1/1 - 12/31). I also have some named ranges that reference columns A:U for each month (Ex: January is A2:U32, February is A33:U63 etc) . I'm running into an issue where since next...
  16. M

    EXCE Named Range Corruption

    Named Ranges become corrupted readily. I am writing a fairly large application using VBA and named ranges extensively. I have a major problem - it appears that in the event of any error in coding, wherever and not related to anything in particular, the defined named ranges are the first thing to...
  17. H

    Merge two ranges together and wrap them into each other

    Hi all, I want to merge two ranges together. The first range is equal to Sheets("1").Range("B1:AA100") the second range is equal to Sheets("2").Range("B1:AA100") I am familiar with the UNION-function, but I want to merge them differently. Basically, I want to merge them column for column...
  18. T

    Can I make my PDF look better?

    Hi Everyone, OK not come across this one before but heres what i need. I have a sheet with 3 different Ranges Range B2:F10 Range J7:T37 Range AA3:AT40 Now the problem im having is I want these ranges to be my Page sizes but when i print i get everything shrunk to match the width of Range...
  19. E

    VBA to loop through one range to find closest value in another range

    Hello. I know this has to be answered before but I couldn't find anything in the forums. What I am trying to do is this...I have two named ranges, LR and LR2, each on a different sheet. I'm trying to create a If and For loop that would go through range LR and match it with a value in LR2 IF...
  20. B

    countif question

    Good Morning, and thanks for the help. I am working with a string of 169 grades (numbers), >90, <70, 80-89, 70-79 I am good with countif on the >, < but need help with those two ranges. Is countif the best formula to use, and if so how to i use it for the ranges, and if not what would you...

Some videos you may like

This Week's Hot Topics

Top