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.
71
questions
1
vote
1
answer
39
views
PivotTable Name Purpose
In the PivotTable Analyze tab, you are given the option to give a PivotTable a name, much like a regular table. However, unlike a regular table, this name may not be referenced in any formulas (such ...
- 11
0
votes
0
answers
50
views
Can you specify a named-range based on a rule in Excel?
For instance, I have a dataset that has many named ranges in it, but I find that the range gets out of sync when I add rows below it.
If a column a row (for instance, id) starts with a specific number ...
- 21.6k
0
votes
0
answers
35
views
I want to return the name of the named range from a worksheet that a cell value can be found in to another worksheet
The cleaners worksheet has the named references (Each Cleaner in the worksheet is its named range, Cleaner 1-19). Each cleaner has a drop down list that is used to allocate a room to them.
The colours ...
0
votes
1
answer
43
views
Can I display the "Refers to" portion of a named range as text?
I am an educator writing an exam on basic excel usage. I ask students to set print area. I would like to have a single cell that displays the cell range they have selected as their print area.
Print ...
1
vote
2
answers
1k
views
How do I address a column in a named cell range
I have a named multi-column, labeled cell range. How do I access values from one of the columns?
Here's an example of the range:
Now, Calc will help me get the entire cell-range:
But what do I do to ...
- 256
0
votes
1
answer
94
views
Reference values in Excel Tables with a semantic reference?
Problem
I have an Excel Table Object:
In which I store assumptions for a formula.
Are there best practices for referencing a value in that table with a semantic reference?
Deeper Explanation
Attempt #...
- 101
0
votes
2
answers
292
views
HYPERLINK - Reference a cell containing named range
I have a recipe book spreadsheet with a navigation bar to different categories (ex. PREP or HOT etc.). When Selecting PREP, a second list appears with all of the prep items. The user clicks on the ...
- 1
0
votes
1
answer
17
views
Named range supposed to be updated from the source column values or not
I understand one of the purposes of named range is to create a new range that will derive its elements from the source column. So when there is any revision in the source column, that should reflect ...
0
votes
0
answers
135
views
Can Excel only display the results from a FILTER, ISNUMBER, SEARCH formula and not the entire array of searched data?
I am creating a spreadsheet that can search an entire "Control" data table using two search boxes.
Search TextBox1 searches by UPI
Search TextBox2 searches by Zip
The two search boxes are ...
0
votes
1
answer
47
views
Naming a range and referring to it in Excel?
Is it possible to put a named range in a formula like this?
=ISNUMBER(MATCH(D42,someworksheet!A:A,0))
Specifically where it says A:A ?
- 21.6k
1
vote
1
answer
25
views
Unexpected behavior when using named cell in IF condition
I noticed something strange when using named cells in an IF condition in Excel 365 and I would like to know what's going on.
Let's say I have a named cell MyValue, whose value = 15.
Then, the ...
- 111
0
votes
1
answer
512
views
Excel Conditional Fromatting: I can't keep it to "$A:$A" range, how is this? [duplicate]
I set a rule range to =$A:$A but when I add or update rows the range gets a mess: the rule gets duplicated to single cells and those cells are "removed" from the main rule, so I get ...
- 3
0
votes
1
answer
811
views
How to sum each row in a named range in Excel?
I'm trying to reverse engineer a budget template so I can modify it. It has sections for different spendatures (Home, Entertainment, Transportation etc). Each section has multiple spendatures (...
0
votes
2
answers
940
views
Any quick way to remove external links from multiple named ranges?
Is there an efficient way to remove external links from named ranges (or a way to prevent them entirely)?
I have a tracking spreadsheet that I use and deploy to multiple clients. They use the ...
- 13
0
votes
1
answer
573
views
How to Copy Excel Named Range?
I have a Named Range = $A$1:$S$34. I can't copy it in same sheet in another cell outside the named range. Would you please help?
0
votes
1
answer
719
views
Dynamic filtered named range in Excel?
Is it possible to use a formula to have a dynamically filtered named range? Essentially, what I want to do is have a data validation dropdown containing filtered results. The data would be like as ...
- 1
1
vote
0
answers
25
views
Excel named lists, concatenating results
I have created 3 named lists (I have filled a couple of entries out as examples)
Instance
--------
Germany
England
France
Italy
VRF-Germany
-----------
Internal
External
Secret
VRF-NonGermany
-...
- 23
0
votes
1
answer
55
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 ...
- 103
1
vote
1
answer
298
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 ...
- 13
1
vote
1
answer
684
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 ...
- 139
0
votes
1
answer
52
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 ...
- 2,018
1
vote
2
answers
19k
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 ...
- 11
4
votes
1
answer
11k
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 ...
- 41
1
vote
1
answer
2k
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....
- 11
0
votes
2
answers
2k
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 ...
- 1
0
votes
0
answers
506
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 ...
- 175
0
votes
1
answer
992
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 ...
- 455
0
votes
1
answer
109
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 ...
- 415
1
vote
1
answer
1k
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 ...
- 1,528
0
votes
1
answer
83
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 ...
2
votes
1
answer
7k
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. ...
- 343
3
votes
1
answer
4k
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'!$...
- 31
2
votes
2
answers
1k
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 ...
- 121
1
vote
1
answer
5k
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?
- 11
1
vote
0
answers
96
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, ...
- 123
0
votes
1
answer
87
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
1
answer
2k
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 ...
- 11
1
vote
1
answer
1k
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 ...
- 321
0
votes
1
answer
3k
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 ...
- 1
2
votes
1
answer
161
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)),"...
- 133
1
vote
1
answer
1k
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.
- 11
2
votes
1
answer
6k
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 ...
- 21
0
votes
0
answers
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 ...
- 306
1
vote
2
answers
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 ...
- 306
4
votes
1
answer
3k
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 ...
- 143
3
votes
4
answers
2k
views
Apply Names in Excel is bugged?
I see people had this problem back in 2005 and it's still not fixed.
In my sheet I have these named ranges:
A1 - "name1"
B1 - "name2"
C1 - "name3"
In some other 3 cells ...
- 2,459
1
vote
0
answers
144
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 ...
- 35
0
votes
1
answer
5k
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"...
- 1
3
votes
2
answers
31k
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 ...
- 189
0
votes
2
answers
1k
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 ...
- 197