1. B

    Macro to copy a range of merged cells into only rows which are visible

    Hi All, I'm new here so apologies in advance if I overstep on the etiquette or for showing my ignorance. I am really hoping you can help - my specific problem is as follows: I have text data in merged cells on columns B to D for each row from 200 through to 213 (so 14 rows total). I would...
  2. S

    Take numbers in a list and convert to ranges for consecutive numbers

    I have a problem that I'm sure there is a formula that can perform this function. I have a list of numbers, some are consecutive and some are outliers. I need to take this data and create a "From" "To" column on those numbers that are consecutive. Below is an example of the data that I have...
  3. S

    Is it a table, a lookup, ? range always changing

    I download a .csv daily In the columns the following headers, Meter, serial num, value, uom, day, wk#, timestamp, and a few more rows of usless info. the meter name changes, the serial num, and the value changes. I added the columns for day, and week# in to make it easier I though. I use the...
  4. J

    Line graph with error bars for range

    Hi All, I am hoping someone could help me. I have some data that I have plotted the averages of as a line graph: My supervisors would like me to add whiskers/error bars based on the range from the data: B-SC IR-SC SC-IR SC-B Initial Inoculum Average: 7.95E+07 Range: 7.90E+07 -...
  5. 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] =...
  6. 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...
  7. 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...
  8. 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...
  9. 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?
  10. 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...
  11. 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
  12. 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
  13. 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
  14. 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...
  15. 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...
  16. 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...
  17. 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...
  18. 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...
  19. 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...
  20. 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...

Some videos you may like

This Week's Hot Topics