654 questions
1
vote
1
answer
67
views
List Named Ranges in Active Sheet
I want a MsgBox that displays the names of the "Named Ranges" in the active worksheet, along with the "RefersTo" field.
I created a macro that I thought would work, but when I ...
- 11
1
vote
4
answers
129
views
Why doesn't getting a value from a named range work, only for this specific value?
I have a named range called A2Bounds25:
Grade
Percentage
A*
80
A
71
B
60
C
49
D
38
E
27
I pull the percentage from A2Bounds25:
=VLOOKUP(F74, (INDIRECT($K$3)),2)
where F74 contains the grade I want to ...
- 89
3
votes
1
answer
128
views
Error getting a named range from another sheet in the same workbook
I have a workbook with multiple sheets. In the name manager, I have a range that looks like this:
Name Value Refers To
PremierPeeled ...
- 33
0
votes
1
answer
73
views
Place the result of a formula in a cell, not the cell reference, to avoid circular references
In the last month of each project, I need to add a certain amount proportionally to the amount allocated to the project, relative to the amount I have for all projects. However, this amount (for ...
- 1
0
votes
0
answers
71
views
Excel VBA function to multiply two ranges based on cells containing specific text
I have a data table that is currently made up of some financial data and some "number of items" data. My aim is to copy and paste the table but replace the "number of items" with ...
- 1
0
votes
1
answer
85
views
Create Dynamic Named Range, based on adjacent value
Without using VBA (need to save as .xlsx), is there a way to create a dynamic named range?
Name Include
Dr. Nick Yes
Dr. Hibbert No
Dr. J No
Dr. Spaceman Yes
I am ...
- 23.5k
2
votes
1
answer
276
views
Reading Excel data and named ranges into Python script
I am new to Python but have used Excel and named ranges for some time. I have a football workbook, several worksheets, with several named ranges for team name, colors, plays, rules, formations, etc.
...
- 23
0
votes
0
answers
30
views
Trying to calculate "CountIfs" using multiple criteria and named ranges
I have a Table in Excel that has columns for : Country, Status, Cohort. I have given these columns named range values.
The country column would be a 3 letter acronym (BEL, USA, ESP), the Status ...
0
votes
0
answers
32
views
Using a Named Range of Worksheets in VBA [duplicate]
I cannot find any articles that deal with this subject. [Probably because it's so easy to figure out.] But here goes.
I have a Named Range which consists of the names of 12 worksheets (called "...
- 61
1
vote
1
answer
36
views
With Range("Named Range" & LastRow)
I'm using this code to insert the results of a formula into a cell:
`With Range("A5:A" & LastRow)
.Formula2 = "Some formula"
.value = .value
end With`
I've used this multiple ...
-3
votes
1
answer
129
views
How to deal with a link with range named in Google Sheets?
I'm currently working in Google Sheets. When I want to set a link from one "Worksheet" to another "Worksheet", I usually do the following:
https://url_to_your_shee_and_sheets_ID&...
- 491
1
vote
1
answer
80
views
Getting KeyError when Python iterates through Excel named ranges
I have a Python script which iterates through a set of Excel named ranges (DT_1, DT_2, ... DT_30). All ranges are in the same workbook, same sheet and are all named systematically. The script saves ...
- 49
0
votes
1
answer
124
views
Word VBA: How to reference a Named Range/Cell from Excel so it returns the row number
Solution: See Macropod's suggestion for solution & make sure you have CorruptLoad:=xlNormalLoad and not CorruptLoad:=xlExtractData
ExtractData = copies and pastes only the data into another ...
- 5
0
votes
2
answers
68
views
For...Each Loop Exiting After Finding First Match
I have an Excel (VBA) test sub that first searches for named ranges that have the word "Date" in them. Then it's supposed to search through the found named range for all cells in column (2) ...
- 61
-2
votes
1
answer
44
views
How do I set up range based on loop counter?
I am new here and I do not have much experience. I greatly appreciate any advice you may provide.
I have a 2D array representing staff scheduled to work on a particular date. The data looks like this ...
0
votes
0
answers
62
views
What method can I use for parsing Named Ranges from Excel into C++: Alternatives to xlnt?
I'm trying to create a C++ program that imports data from named ranges in Excel. Named ranges are defined in Excel using the Name Manager (Formulas > Name Manager), and I need to extract values ...
- 1
0
votes
2
answers
115
views
Using a named range in VBA
I'm trying to select worksheets to save as csv, was previously using an array that defined each sheet name but I was having to update the tab names each month.
I have a tab with the sheet names that I ...
- 1
1
vote
3
answers
121
views
Is it possible to have a named range attached to "activesheet," rather than a specific sheet?
Is it possible to name a range without it being slaved to a single sheet? Like if I select "rnge1" on sheet 1, it will select range e2:e7 on sheet 1, and then I can go to sheet 2 and select &...
- 73
0
votes
1
answer
61
views
Accessing a named range to insert a value
I am trying to overwrite the value in a named range.
I get
Object doesn't support this property or method
Sub CheckStuff()
If Evaluate("Table1RowCount") > Evaluate("...
0
votes
0
answers
81
views
Calling a named range without naming the workbook
My macro is currently working with the below line:
Range("Fee_Calculator.xlsm!Modelling_Labour_WBS").Copy
I want to remove the reference to the workbook name but I'm not sure how.
I think I ...
- 1
0
votes
2
answers
86
views
Excel VBA: named ranges aren't ranges at all? ByRef argument type mismatch
Consider this MWE:
Option Explicit
Sub test()
Dim r1, r2 As Range
Set r1 = Worksheets("MySheet").Range("MyNamedRange")
Set r2 = NewRange(r1)
End Sub
Function NewRange(rng ...
- 155
1
vote
2
answers
121
views
Use a Named Column for Reference instead of Cell Offset
I have a list of email addresses in a column. Using the Name Manager, I've named the entire column, $Q:$Q as "Email".
I have a macro that strings the emails together if their row is marked ...
- 107
0
votes
1
answer
253
views
Dynamic INDIRECT with named ranges google sheets
I want to union a few named ranges dynamically, while avoiding the use of Google Apps Scripts as much as possible. The number of named ranges can vary, so any references to them has to be dynamic. For ...
0
votes
3
answers
385
views
Previously working Excel Named Range gives a "Reference isn't valid" and returns error 1004 in a simple macro
I have a couple of files where previously good named ranges stopped working. Running Office 365 for business, 64 bit, Win 11.
The named ranges appear in name manager with the correct cell reference ...
- 1
0
votes
2
answers
77
views
Check whether a string value is formula or range or constant
I have a very specific use case where I need to read value input in the data validation field for each cell.
If the value is a list, then the value should be stored in the string as it is. However, if ...
0
votes
0
answers
90
views
I am trying to copy/paste a named Excel range to another range in the same worksheet using Python xlwings
I am trying to copy a named range in Excel (ex. "Question_1") and paste it into an empty area of the same worksheet, using Python (xlwings). I am getting an attribute error which mentions &...
- 49
0
votes
1
answer
95
views
A data extract by multiple conditions in Google Sheet is working for one raw, but I can't "arrayformula it"
I'm trying to create a table on a sheet, filled with data from multiple tables stacked in another sheet.
A simplified file is here : https://docs.google.com/spreadsheets/d/...
1
vote
1
answer
86
views
Dynamically Switching a Range in a SUMIF()
I have a formula in excel that looks like this:
=SUMIFS(SILOADRESERVED,
SIPDU,
B8&"*",
SIPHASE, "A",
SITYPE, "<>RMT"
)
The challenge is that ...
- 43
0
votes
2
answers
108
views
In EXCEL, get ALL named ranges, and set them as variables
In my Excel VBA, I have well over 100 lines of code similar to the following.
Is there a way to loop through all existing named ranges, and set a variable with a name equal to the name of the named ...
0
votes
1
answer
405
views
Is there an Office script to paste an image (png) of a specified range to an email body?
As a stopgap until systems get improved, I have a "form" some business partners use to submit a request for services from my group. It is not a "Form" in the technical sense, but ...
0
votes
1
answer
56
views
Using Names Range in Excel VBS not working in some circumstances
I'm trying to use a named range to control cell formats, but it does not appear to work as expected:
The Sub that does not use the named range, works fine:
Sub COLOR_PATCH()
'
' This changes the ...
- 702
1
vote
0
answers
76
views
Excel named range creation using xlwings workbook.names.add() generates extra quotes
Xlwings version: 0.30.14
When creating named range in excel 2016 using the following statement generates an extra quote.
Does anyone have the same issue?
Here is my code
workbook.names.add(name, f&...
- 11
-2
votes
1
answer
79
views
Choosing Named Range via formula
So I have this formula:
At a high level, the formula goes like this:
If [Dynamic Array #1] = False, 0,
Index(
If [Dynamic Array #2] = 1, Named Range A, Named Range B <-- Problem ...
- 1,332
0
votes
1
answer
282
views
How to get the named range definition in Cell
Suppose I have a named Range in Googlesheets as MyNamedRange with definition as C1:H25
Now In a cell I want to show this value in cell A1 as "C1:H25"
Is it possible through some formula like ...
- 3
0
votes
0
answers
115
views
Referencing cell by using table column and named range row
I have the following table.
Every TOTALs row is a named range as can be seen from the formula bar.
In addition, i have multiple sheets with the same table and the same named range (local named range)....
- 6,204
0
votes
1
answer
40
views
How do I add information into a specific range?
I'm very new to all this, but I've created a sheet where we can get some information from our clients, but I’d like to make it more dynamic, so I'm trying to program it to at a click of a button I can ...
1
vote
2
answers
59
views
How to streamline code with multiple non-contiguous ranges
I have a worksheet that project managers (PM) use to configure large systems. The worksheet has four sections that represent each of four panels. Within each panel, the PM must select one of the four ...
1
vote
1
answer
549
views
Power Query / M Code, extract a list of tables into one main table, some column headers same but some different and in different order (and in row 2)
I have some named ranges in an excel file which I need to merge. The column names vary but most have DATE and Code. I cannot put them as tables in excel, they need to remain as a named range as I ...
- 103
0
votes
0
answers
88
views
How to pass variable string as an argument to Names Object
I'm using a code to identify a the name of a range and store it in a variable. Then, pass this variable using Names object to a Range variable to convert the string to a cell range. However, I'm ...
- 1
2
votes
1
answer
264
views
ArrayFormula with SUMIFS not Working in Google Sheets
I'm encountering a challenge with an ArrayFormula in Google Sheets that utilizes the SUMIFS function. Here's the scenario:
I have a report with data in columns SpoPartnerShare, SpoMonth, and ...
- 23
0
votes
1
answer
281
views
Excel VBA Named Range Scope and/or Permanence of a Named Range
The Sub shown below is in the workbook's Module1 (in the Modules folder)
The workbook/file is named "240122 Range Name Scope v01.xlsm"
It has 2 worksheets S1 and S2.
Each worksheet has One ...
2
votes
1
answer
257
views
Export R dataframe to Excel named range
I am collating data from various sources into several dataframes. Each dataframe would have dimension R_k X C_k. I would then like to export each dataframe (identified by a unique tag say ...
- 63
0
votes
1
answer
339
views
Runtime error 1004, method range of object _worksheet failed. Defining ranges in VBA
I am defining some column ranges in VBA so that I can easily use the name of a range later instead of defining every time.
There are multiple worksheets in my workbook, which is why I'm defining the ...
1
vote
0
answers
83
views
Excel replace Indirect function with a non volatile formula but referencing a cell with named ranges in
In an excel file, I have 511 named ranges in a workbook that reference numerous tables of data plus I have about 72k indirect formulas used to pull the data into report tables or for charting (yes it'...
- 103
-1
votes
1
answer
64
views
Range of Object Failure
When referencing a named range, I'm getting a Global 'Range of Object Failure
I've tried to use a named range determined by user input as ranges to copy and paste...
Essentially copy rows( "SKE&...
- 19
2
votes
1
answer
59
views
Why FIND returns nothing with structured table?
Hoping someone could shed something on my line of vba script below and point out the error:
Sub findKeyword()
Dim tbl as ListObject
set tbl = activesheet.ListOBjects("myTable") 'containing ...
- 93
0
votes
1
answer
453
views
Google Sheets - Return Cell Name
I have named a cell and I often forget which cell is the main one so I am trying to put a label beside it.
I would like to return the cell name in an adjacent column.
i.e.
Cell A1 has a value of 2, ...
1
vote
2
answers
157
views
Run-time error '-2147417848 (80010108)': Method 'RefersToRange' of object 'Name' failed
I'm using the single cell named range RowMarker trick to keep track of when users are adding or deleting rows.
This happens in the Worksheet_Change event. When a user adds a new row a dropdown (...
- 11
1
vote
1
answer
235
views
Using the IF statement formula below, can I make any bank holiday dates regardless of time to theatre show as "out of hours" ? using a named range
=IF(AND(MOD(J2,1)>TIME(8,15,0),MOD(J2,1)<TIME(17,0,0)),"In hours","Out of Hours")
Formatted for readability:
=IF(
AND(
MOD( J2, 1 ) > TIME( 8, 15, 0 ),
...
- 119
0
votes
1
answer
736
views
Deleting named ranges from active cells in google sheets with a script
I am new to the google sheets environment and my limited knowledge of VBA does not help. I would like to be able to delete range names from a cell or multiple cells selected by the "curser" ...
- 1