Questions tagged [named-ranges]

A fixed or dynamically defined range of cells that has been assigned a name by a user. Named ranges are used in Microsoft Excel and similar spreadsheet applications. Named ranges may be referred to in formulas or in code and are useful for convenient reference and improved formula readability.

Filter by
Sorted by
Tagged with
0
votes
0answers
17 views

Varying Chart Range Based on Drop Down Lists

I'm hoping to get some help with automatically updating the chart range in Excel based on what's selected in a drop down list. I have 3 selections for my drop down list: All, Alberta and Saskatchewan....
0
votes
1answer
26 views

Three dimensional named ranges for dynamic graph

I have a table which contains values of 10 variables for every day in a calendar year. See attachment for reference. I'm trying to create a line chart with the following options: Select any of the 5 ...
1
vote
1answer
24 views

Split Range by other Range between it? VBA

Let's say i got the Range B1:BF1 And another, Variable Range (i.E E1:W1) I want to split the Upper with the lower one, so the Result would look like that B1:D1, X1:BF1 Is it possible to save a ...
1
vote
1answer
39 views

How to Create Named Range in VBA with Formula (Run-time error 438)

I am trying to create a named range using VBA. Both FirstBodyCell and ColumnCode are single cells. I want the named range to eventually be defined as something that looks like this (depending on what ...
0
votes
1answer
15 views

How to convert names created with Excel's Name Box from workbook-scoped to worksheet-scoped?

I created A LOT of names of cells in an Excel worksheet using the Name Box (to the left of the Formula Bar). Then, I decided to make a different version of the worksheet in the same workbook. And then ...
1
vote
1answer
624 views

Excel - named range in a conditional formatting formula

TLDR; seems like CF thinks my formula with named range is an array, and I want it to function like a flat formula. Long time lurker, first time poster! I have been creating nice elaborate ...
1
vote
0answers
992 views

Excel telling me my name already exists when renaming a table

I have copied a table from an external workbook into a different workbook. When I rename the table (on the design tab), Excel tells me that the name already exists. When I rename a range within ...
1
vote
1answer
110 views

VBA - How to refer a closed workbook named range

I'm trying to do a conditional count "COUNTIF" from closed workbook named range. Like the example below, count the amount of "fruit_count" if the "fruit_names" is "apples": =SUMIF('C:\folder\test....
0
votes
0answers
18 views

How to add a list of values to a named range manually?

How to add a list of values to a named range manually? I do not want to map it to cells. For e.g., = {"Apple"; "Orange"} I am looking for something similar to c() in R which saves set of values to ...
0
votes
0answers
117 views

How to create dynamic named range that includes blank cells?

I am attempting to define a dynamic named range, however some of the cells are blank, but I still need the range to encompass these cells. What functions, etc. should I be using in order to do this? I ...
0
votes
2answers
529 views

Find value in a non-continuous range in Excel

Is there a way in Excel to look for a numeric value in a non-continuous range throughout the table? Please refer to the image below: I need to search for the value 2 between the range minimum value ...
0
votes
0answers
81 views

Excel: How to replace references to named ranges with other named ranges that have the same name

I have two excel workbooks that are formatted similarly, but have different data in them (one with 2016 data and the other with 2017 data). This means that both workbooks have the same named ranges ...
0
votes
1answer
323 views

Cell “address” function for entire reference range

The function OFFSET($A$1:$Z$25,2,3,4,5) refers to the range $D$3:$H$6 of the current worksheet. If I pass "address" as the first parameter to the CELL function and the above OFFSET function as the ...
0
votes
1answer
68 views

In Excel, can I create a name that refers to cells that aren't tied to one worksheet?

This isn't a question about the scope of names. It's a question about specifying the cells in a name. Imagine I've created a name =A1+B2. That gets hard-coded by Excel into Sheet1!A1+Sheet1!B2. But ...
1
vote
1answer
513 views

Can VBA delete named ranges based on RefersTo value?

I'm cleaning up a bloated Excel workbook and am trying to bulk delete named ranges that are referring to workbooks on someones 'C' drive. There are a lot of them. And they are not being used in this ...
0
votes
1answer
36 views

trigger a macro by clicking on merged range

Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Selection.Count = 1 Then If Not Intersect(Target, Range(abc)) Is Nothing Then Call MyMacro End If End If End Sub this ...
1
vote
1answer
3k views

Using Table name in vlookup for conditional formatting

I have some cells which I would like to format to display the level achieved for each person: On the right I have a table called Table3 which contains the data of the level achieved by each person. ...
3
votes
1answer
2k views

Single named range over multiple sheets in same Excel file?

In Excel, is it possible to name a range that exists over multiple tabs? For example, I have a range B3:C20 in sheets Tab 1 and Tab 2 I tried to name a range NamedRange1 that referred to ='Tab1'!$...
2
votes
2answers
243 views

Converting/transforming sum(A1:D1) into A1+B1+C1+D1 in Excel

So, the situation is this, I want to know if there's a way to convert the sum of a range of cells into the sum resulted from adding each cell, separately, as shown in the title. For instance, say one ...
0
votes
1answer
1k views

Excel: Named Ranges based on cell Values

Is it possible to change the name of a Named Range based on the Value of a Cell? ie. A1 = NameOfRange If A1 changes to NewNameOfRange, how could this update the name?
1
vote
0answers
76 views

Excel 2010 vbs, creating a named range causes an Update Values dialog box to appear

I'm using this code to create named ranges: ActiveWorkbook.Names.Add Name:="B1065K16CN", RefersTo:="='1065-K-1'!$E$4" A file requester box appears "Update Values: 1065-K-1" when I cancel the box, ...
0
votes
1answer
69 views

Excel spreadsheet (2010 of 2013)

I'm hoping someone may be able to help? I've been asked to edit an excel file used for process requests. The person that created the file retired last year. There is a drop down list on sheet 3 of ...
0
votes
1answer
1k views

Excel VBA: Create two-dimensional array from multiple named ranges(no duplicates), sum columns

new here. looking for a VBA solution to merge multiple named ranges and remove duplicates with sum on some columns. I have 4 variable ranges: "ACTUAL","BUDGET","FORECAST","PYEAR" I would like to ...
1
vote
1answer
904 views

Referencing a dynamic, named range between workbooks

I inherited this data structure. I have a bunch of products and their supplier information laid out as so: Supplier Order Number Name Cost ACME 00123 A 1 ACME 00321 B ...
0
votes
1answer
2k views

using named ranges in a sumif formula

I have named ranges. They are columns on another tab within the workbook. I'm using a sumif formula to find value based on multiple criteria. The formula is =sumifs(H4,array1,b4,array2,c4). H4 is a ...
2
votes
1answer
82 views

compare text to named range in external sheet

I have the the following data: and I have this data in an another workbook: (larger image) the formula I am using is: =IF(ISERROR(MATCH(A10,'C:\path\to\file\John C 05-07-16.xlsx'!Individuals,0)),"...
0
votes
1answer
462 views

How to get a list of all named ranges in calc

I want to list out all the named ranges and the data ranges of a Calc workbook containing several sheets. I am jus tmoving from excel to calc and thus not too familiar with functions/macros in calc.
2
votes
1answer
4k views

Build a lookup query in Excel using named ranges stored in a cell

I have a spreadsheet which has a lot of named ranges, each of which is a table. I want to be able to run a lookup that will retrieve a value from any of the tables. I want to be able to store the name ...
0
votes
0answers
2k views

How to transpose a named range in Excel 2013, using cell formulas (not VBA)

Similar to this question, I want to display the values of a named range (horizontal) in a vertical range. If I have data a b d f which is named Letters (as a named range). I want to display this ...
1
vote
2answers
7k views

When using a named ranged in Excel (2013), how to display the entire range, but offset one cell?

I have a range $A$2:$G$2 named Distances; later in the sheet, I want to populate the range B11:H11 with the data in the original range. I can start in cell A11 and type =Distances and it will populate ...
3
votes
1answer
2k views

Excel 2010: Create named range without a sheet reference in the formula

In Name Manager I created a named range with this reference: =COUNTIF($A$4:A4, Tbl_InventoryMain[Barcode]) Excel automatically inserts the current sheet name prior to the $A$4:A4. I need this to ...
4
votes
3answers
1k views

Apply Names in Excel is bugged?

I see people had this problem 10 year ago and it's still not fixed. Or maybe I just don't know how to use this feature. In my sheet I have these named ranges: A1 - "name1" B1 - "name2" C1 - "name3" ...
1
vote
0answers
124 views

One dynamic named range not staying dynamic while another in the workbook is

I am attempting to use dynamic named ranges to make it so I can pre-set the column portion of the print ranges in a workbook without assuming a number of rows. So I opened the name manager and ...
0
votes
1answer
3k views

Copy named ranges to another workbook

I have this VBA code to copy my named cells from one workbook to another, but it gives me Error 9: Subscript out of range. Any ideas please? Sub namexfr() wbs = "C:\Users\MousaviM\Desktop\Book1.xlsm"...
2
votes
2answers
23k views

Referencing cell only by column name in Excel 2010 VBA

In Excel 2010, I need to go through a data table row by row and manipulate the data in VBA. My original function reads like this: Private Function GetValue(ByVal myRange As Excel.Range, ByVal strCol ...
0
votes
2answers
945 views

Auto-increment cells for named rows and named columns

I am looking for a way to reference cells inside named range and have it be understandable for person looking through the spreadsheet. I have this spreadsheet: A B C D E F G H 1 ...
0
votes
1answer
724 views

How to create multiple named ranges with a fixed size from one column?

I am wondering if there is a way to create named ranges with a fixed size (252 cells) from one column. I have 51,156 numbers in column B. I want the first 252 numbers (B1:B252) to become named range ...
-1
votes
1answer
234 views

Excel 2010: How do I reference a specific field using a defined column name and a row number?

I'm using Excel 2010 and am creating a workbook to define details about products in a store (including category, price, accessories). I also want to define default category details to use if the ...
1
vote
0answers
496 views

How can I check that a filter applied to a listobject column with vba is legal?

Filtering a listobject(excel table) with a value that doesn't exit results all rows instead of none can I get no rows? can I get list of optional legal parameters? example listobject with col A ...
1
vote
1answer
293 views

Creating Dynamic, Unique Arrays Dependent on Multiple Criteria or Subset of Data

I have a Table structured in this manner: City Region Date Value ABC 123 Jan1 10% DEF 999 Feb1 7% The table is fed by user-entry, but there is an important rule: No ...
0
votes
1answer
56 views

Calculating Named Ranges

The spreadsheet contains numerous named ranges. I need to refer to those ranges using VLOOKUP. Here's the kicker, I want to refer to the ranges via a text manipulation. Case. Column A contains list ...
0
votes
1answer
960 views

Defined Name to give text or date value instead of numeric value

I had been trying out Define Name in Excel. I defined a name for a cell in which I would put a text or date value or I may leave it blank. But when I use this defined name in another cell, the second ...
0
votes
1answer
788 views

How to call a namer range using a variable to define the name

According the documentation, any quoted text names can be defined by a variable If I define Set xlsDataSource = Application.Workbooks("New_TOR_V1.xlsm").Names("MyRange").RefersToRange it works as ...
1
vote
1answer
3k views

Insert columns, within a range, based on cell reference

So am trying to tell VBA to insert a certain number of columns between two other columsn based on a cell reference. I've managed to get excel to insert columns within my range: Sub INSERT() ...
1
vote
1answer
1k views

Get the row and column for a cell in a named range?

Given a cell that exists in a named range, what's the cleanest way to get the row and column numbers for that cell inside the named range? In other words, I want the row and column number relative to ...
2
votes
1answer
1k views

Can the name of a named range be dynamic?

I'd like the name of a named range in excel to be dynamic (as opposed to the range itself!). I'd like to know if/how I can define a range name using a formula or reference cell, so that the name ...
4
votes
2answers
13k views

Excel reference named range with text and formula

Is it possible to refer to a named range by combining text with a formula? Say I get a name for a range via INDEX which returns a text but I also want to add something after the text like so: ...
1
vote
3answers
1k views

Fill formula containing named range but change named range based on relative reference

I have a formula that I need to copy in many cells, but I don't know how to copy a named-range reference to another cell without changing it. NamedRange (a table imported from database connection) ...
1
vote
2answers
16k views

Count from a certain cell until a blank cell

I'm trying to create a dynamic formula which counts all the rows from cell D3 downwards until a blank cell is reached. I want to be able to add new data everyday and have the range automatically ...
2
votes
2answers
1k views

Mac Excel 2011: find Items in one column that are not in another column

Hi this is a repeat of the question: Find Items in one column that are not in another column I have two columns in excel, and I want to find (preferably highlight) the items that are in column B, ...