I have several SumIf formulas that references a Cell in a Main Reference Tab. This is useful to me as I have over 100 tabs of data points and having a reference cell with that text is easier to change one time vs 100 times.
My question is this: Will Defined Names be "faster" than using a reference cell? With over 100 tabs, there's a lot of formulas and I'm looking for a faster way to get the same output without Excel Memory overload.
Trying to use a Defined Name as the cell reference in VBA. The idea is to change the value of a couple of other cells base on the value of the cell "FreightRow". I am starting with a Message box just to make sure the code was reading the cell reference.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim FR As Range
Set FR = ThisWorkbook.Names("FreightRow").RefersTo
If FR = 3 Then
MsgBox ("Help")
End If
End Sub
Is it possible to use defined names from other workbooks? I would like to use this defined name as part of a VBA code.
Example: ActiveChart.SeriesCollection(1).Values = Range(Name1)
where Name1 is a defined name from a different workbook
I have a column J that is named Premium. If I use the function =INT(Premium) in K4 then it returns the integer for the value in J4. If I do the same thing with a UDF then the function argument receives all the values in column J. The author of the function says this is how UDFs work by design. Is there a way to use defined names with UDFs and have it work the way it does with built in functions?
View 5 Replies View RelatedI use Vlookup's a lot so i define my range with specific names a lot. But when i want to clear the range names via Insert>Name>Define>"Select the range name then click delete. but instead of clicking on each range name, i would like to to delete all.
I can create a macro to do this but some days i use different range names that other days so the below will not work exactly.
Ive created the following code which I want to use to create a name in excel but it doesnt work.
when i put =INDIRECT i get an error and with out the = it places a text string in with speech marks on it e.g. defined name reference in excel reads ="INDIRECT('Health and Safety'!$AP$3)"
How do I get rid of those speech marks? the cell reference will change each time the macro runs and so will the sheet reference.
myC = ActiveCell.Address
Dim strNAME As String
strNAME = "YAXIS" & "_" & "HS" & Chartnumber
strSHEET = ActiveSheet.Name
ActiveWorkbook.Worksheets(ActiveSheet.Name).Names.Add Name:=strNAME, _
RefersToR1C1:="INDIRECT('" & strSHEET & "'!" & myC & ")"
I am currently overhauling a massive workbook, that previously relied on an absurd amount of named ranges in order to run vlookups. I no longer need these named ranges and would like to delete them to unclog the workbook. Currently I only see a way to delete them one by one. Is there a way to mass delete all of them?
How to delete all defined names from a workbook
View 14 Replies View RelatedWhy can't I alphabetize all of my defined name ranges in the Name box?
View 8 Replies View RelatedThis thread is related to this thread. Offset, Match, Max Formula In the Insert - Name - Define window:
If one wants to copy a formula from the "Refers to" box and the formula extends past the right side of the box, how does one use "select all" or mark the whole formula from beginning to end so it can be copied without messing up the formula. At the moment, when I try this, it changes the formula to include the active cell of the worksheet that's open when I use the Ctrl + c etc. I have tried Ctrl + Ins and Ctrl + a and Ctrl + c. None of these are working for me.
I have a situation where I copy a worksheet to further on in the same workbook (essentially, using it as a template to create new sheets). I have certain cells that contain references to named ranges, e.g. on NewSheet1, cell $A$1 contains =StudyNo. Unfortunately, it shows as #NAME?. But, if I go to the formula bar and press enter, it resolves the name properly. I thougt maybe that automatic calculation was on manual but it is not. Why is this happening and what can I do to resolve it.
View 3 Replies View RelatedI want to be able populate a combobox with all defined ranges in the workbook?
View 4 Replies View RelatedI would really appreciate if someone could help me with the following problem:
I wrote this code, but for some reason it doesn't work. I am completely new to VBA and have no idea what went wrong:
as the title says, i need to rotate a range but to keep all defined names (single cell or range) consistent.
i.e.
lets say i have the following cells filled:
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
now i have a few defined ranges. lets say:
range name, refers to
mycell1row1, A1
mycell90roanything, B1
mysomething, C1
mysomthingrange, A1:F1
etc.
what i need in the end is this:
1 1 1 1 1 1 1
2 2 2 2 2 2 2
3 3 3 3 3 3 3
4 4 4 4 4 4 4
and for defined names:
mycell1row1, A1
mycell90roanything, A2
mysomething, A3
mysomthingrange, A1:A7
Reduced to it's simplest form I have a formula
=1*(FY2012_TY), where FY2012_TY
is a Defined Name which refers to a column on another tab in the workbook, and is defined as:
=OFFSET(Table!$AA$3,0,0,COUNT(Table!$AA:$AA),1)
What I would like to do is to come up with an approach to this formula that would allow me to drag it across columns and have the year automatically increment (e.g., FY2013_TY, FY2014_TY, all of which are similarly defined Defined Names referring to adjacent columns). I have tried various approaches using INDIRECT along with CONCATENATE, but when it comes down to evaluating the formula, I consistently get a #REF! error.
I'm trying to use some defined names to create charts. Though it works at the very begin, some extremely weird things happen when I try to develop further .....
Attached please find the test file, which simplifies my real case but shows the same problem.
Test.xlsx
In the test file, three names are defined as below:
Line1: "=Xvalue*1"
Line2: "=Xvalue*2"
Xvalue: "=ROW(SampleChart!$A$1:$A$25)"
the chart is very sample: "=SERIES(,Test.xlsx!Xvalue,Test.xlsx!Line1,1)"
So far, it works very well. However, I just simply can't add the second line into the chart, for example, "=SERIES(,Test.xlsx!Xvalue,Test.xlsx!Line2,2)"
I canNOT even change "Line1" to "Line2" in the "SERIES" formula!
I am looking to create a formula which sums the values associated with several defined names. For example, I have a workbook with the following defined names SalesPerson1Total, SalesPerson2Total, etc. and these amounts are all sourced from multiple tabs. The amount of defined names (i.e. 'SalesPersons') is variable, therefore, I want the formula to read Sum the values of all defined names which are named with the following convention 'SalesPerson(X)Total'.
View 9 Replies View RelatedI have created 500+ defined names that refer to worksheets that do not exist (yet). When I add the previously nonexistent worksheets, the defined names that refer to them are not "live" and do not work in functions where that name is used. If you go into the defined names and click on the ones that refer to the now existent worksheet, they start to work, but I was hoping there was a way around this step (e.g. a simple macro that says "update all defined names").
I created all the names so that the person who will eventually be using this will only have to add an appropriately named worksheet and put the data in...then everything will automatically work. I do not think adding names is difficult, but the person who will be using this does, so I want to avoid that person mucking around in my names.
I'd like to be able to refer to a cell or local scope defined name within my sheet that references another sheet, or accomplish the same functionality in some way.
For example, if I have a sheet MyInformation and then another sheet MyPrintedInformation, where the printed information sheet shows MyInformation in a more printer-friendly format, I might have this in MyPrintedInformation:
a cell or defined name that says (this doesn't work, but just as an example):
let's call the named variable TheSheetToPrint
='MyInformation'. Then from within MyPrintedInformation all of my references look like this
Some cell :
=TheSheetToPrint!A2
In the attached sheet. Cell C6 represents the concatenation of aspects of a product. I want to know if it is possible to use that cell as the named range (there is a corresponding named range) in a lookup. The lookup gives me the product code.
View 5 Replies View RelatedI have a list of numbers in Column A and in Column B I have a standard Sum formula which sums the adjacent number in Column A and the four numbers before it.
A B
1 =Sum(A1:A5)
3 =Sum(A2:A6)
5 ...
3 ...
5
6
I would like to have a formula which references another cell to define how many rows to sum from the starting cell. So in the above example, the total number of rows which are added together is 5. If I wanted to change all of the SUM formulas to add 6 rows, I would like to be able to change the number in the reference cell to 6 and all of the sum formulas would switch to adding 6 rows.
referencing constant cells in custom functions. This is a sample of my code:
Code:
Select Case Name
Case Is = "Ball"
Valve_Days = [V4] + Cushion
Case Is = "Check"
Valve_Days = [V5] + Cushion
Now, the problem is when I open the workbook up, or someone downloads the file from the server, it resets all fields to div/0 based on what sheet the last user 'Saved' the document on. I think this is occurring because it doesn't know which sheet to pull the value V5 from? The problem never started to happen until I added another sheet in the workbook. Is there a way to tell VB that it isn't just cell V5, but it is cell V5 on Sheet 'X'?
I'm trying to simplify a spreadsheet i've been given by defining names for certain values. I'm using Excel 2013. Is there any way to have all of the newly defined names I've created for cells automatically be inserted into all of the formulas that exist in the spreadsheet?
Example.
Old formula in one sheet of a workbook looks up a cell in another sheet with the value of ='sheet1!A1
I've given the value of cell A1 in Sheet1 a name of dgwd.
How do I get every formula in the workbook that references 'sheet1!A1 to change the value within that formula to dgwd?
I have 5 columns set up: A,B,C,D,E
D is the sum of A and B
E is the sum of A,B,and C
As I add in a new column to the right of C (call it C2), I need D (which has shifted over one) to sum A,B, and C.
I also need E (which has also shifted over one) to sum A,B,C, and C2.
Essentially I need a function in a cell that will be able to reference two cells to the left even as more cells are added.
VB:
Sub RenameTabs()
For i = 1 To Sheets.Count
If Worksheets(i).Range("C1").Value <> "" Then
Sheets(i).Name = Worksheets(i).Range("C1").Value
End If
Next
End Sub
I change my tab names with the above but than my hyperlinks breaks.
I have a workbook with multiple sheets. There is an overview sheet and then 14 sheets allowing for 14 days worth of schedule information. There are then 3 sheets following the 14 days to total some information. The workbook users frequently change the sheet names of the 14 sheets to reflect days of the week.
I'm creating a copy for distribution that takes the active workbook and copies the values to a new workbook. There are columns that I would like to delete from the 14 day sheets and that would be easy enough if the sheet names were never changed.
The code I'm using currently follows (my thanks to Turtle 44 for helping on that section)
Sub Copy_Visible_Sheets()
Dim arr() As String
Dim i As Integer
Dim WB As Workbook
Dim WS As Worksheet
Set WB = ActiveWorkbook
Application. ScreenUpdating = False
'Make sure template is saved as .xls
If Not ThisWorkbook.Saved Then
MsgBox "Please save this workbook before generating a Client Copy."
Else
I have been given a database with peoples names that require a unique number to hide/replace the name to hide their identity for a meeting and to act as a ghost system.
The problem i have is that some individuals names on the list appear more than once so i cannot simply call Joe Bloggs number 3 as he can appear further down the list at number 120.
I was wondering is there is a way to do this without manually replacing each name with a number and making and logging the persons name that corresponds to the number.
as there are 600 names and some can appear up to 4 - 5 times!
I want to create a series of VLOOKUP commands with different lookup tables whose names are concatenated from two different pieces. Here's what I'm trying to do:
VLOOKUP(x,LUTWK01,y,false)
VLOOKUP(x,LUTWK02,y,false)
VLOOKUP(x,LUTWK03,y,false)
LUTWK01, etc., are named ranges. I have the text "WK01", "WK02", "WK03" stored in other places, and would like to be able to concatenate "LUT" and the particular week (e.g., "WK01"). The formula that I've created, which isn't working is:
VLOOKUP(x,CONCATENATE("LUT",A1),y, false) where cell A1=WK01.
When I pull out just the CONCATENATE portion, it resolves to LUTWK01, but apparently this is not recognized as a range name. Any idea as to how I make the VLOOKUP formula recognize a concatenated range name? Or is there a better way to do this?
Within one workbook I have 15 sheets, 13 are for separate divisions within the company, 2 are used to present sums across the 13 sheets. All 13 sheets have identical columns and rows, with unique numbers in each cell. Right now I am manually selecting the cells to reference in my master sheet, =sheet name!cell+sheetname!cell+sheetname!cell - so and and so on. How can I reference the sheet name once, and then all of the cells to SUM? =sheetname!(c4+c14+c24+c34) etc?
This would let me be able to copy the formula from cell to cell, and only have to change the sheet name each time.
I am currently using cell references to create a depedendent list with data validation. How to make cell references show up with some invalid characters?
For example, one of my cell references is Youngs_Market_Company. However, when pulling up the cell, I want it to be displayed as Young's Market Company.