**0**

votes

**1**answer

112 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

**0**answers

16 views

### Use COUNTIF to define a calculated (dynamic) address

I'm trying to find a way to use a COUNTIF formula with a calculated address as the first and last pointers to the range of data. Normally, one might specify something like this
...

**0**

votes

**0**answers

241 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

**2**answers

218 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 ...

**2**

votes

**1**answer

164 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

**3**answers

193 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

**0**answers

52 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

**1**answer

602 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 = ...

**1**

vote

**2**answers

6k 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

**2**answers

276 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

**1**answer

90 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

**1**answer

102 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 ...

**0**

votes

**0**answers

119 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

**1**answer

215 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:
...

**0**

votes

**1**answer

39 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

**1**answer

400 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

**1**answer

142 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 ...

**0**

votes

**1**answer

665 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

**1**answer

571 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

**1**answer

177 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 ...

**3**

votes

**2**answers

5k 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

**2**answers

577 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) ...

**0**

votes

**2**answers

6k 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

**2**answers

855 views

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

Hi this is a repeat of the question: Excel: 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 ...

**2**

votes

**3**answers

14k views

### Creating a chart using a dynamic named range in Excel 2010

I am trying to create a scatter plot in Excel 2010 using dynamic named ranges and am having trouble getting it to work. Here's simple example that is failing:
Open Excel, starting a new workbook
...

**3**

votes

**2**answers

12k views

### Excel Name.RefersToRange: how to check if Name object refers to a range?

The Microsoft documentation says about RefersToRange:
If the Name object doesn't refer to a range (for example, if it refers
to a constant or a formula), this property fails.
I'm trying to ...

**1**

vote

**1**answer

452 views

### How exactly do I create “Dynamic Named Ranges”?

I'm trying to create a "Dynamic Named Range" in Excel so I can populate an X/Y values table inside of it in Java.
I've seen tutorials, but I still don't get it. I know I have to use the =OFFSET() ...

**3**

votes

**2**answers

6k views

### How do I prevent VLOOKUP from breaking after I add a column to a named range?

My Excel worksheet contains a lot of VLOOKUPs on a named range that I have defined. Now, when I added a column in the middle of my named range, the VLOOKUPs that reference columns after the inserted ...

**2**

votes

**1**answer

294 views

### How do you make the Named Ranges box bigger in Excel?

The box with the Named ranges in it is too small to see the whole name sometimes, so I have to open the Insert Name dialog. How do I increase the width of the Named Range box (I don't mind shrinking ...

**4**

votes

**1**answer

14k views

### Shortest Method To Reference A Single Row/Column of a Named Range in Excel?

I have a named range (NamedRange1) and I need to reference the first row and first column of that range within a formula. I came up with the following:
First row:
INDIRECT("R" & ...

**6**

votes

**2**answers

10k views

### What's the difference between a Table and a Named Range in Excel 2007?

Can someone explain the difference between Excel Tables and Named Ranges in Excel 2007? It seems that in addition to having the features of Named Ranges, they're somehow marked as Tables which gives ...