Hide Range Names
Nov 21, 2007Suppose I have ten Names in Excel, and I dont like any one see them, How can I hide them (Names not cells)?
View 2 Replies
ADVERTISEMENT
Excel 2010 :: VBA - Hide Sheets Using Code Names Not Sheet Names
Oct 15, 2013Code:
Sheets(Array("Sheet 1", "Sheet 2")).Visible = False
How do I convert the above to using Sheet Codes Names, Sheet1 and Sheet2?
Want to ensure my code will work if the user changes the sheet name.
Hide Used Names In Drop Downbox
Apr 15, 2006I found something I would like for my workbook. I found it on MVP Debra
Dalgleish's page http://www.contextures.com/xlDataVal03.html. The original
code is from Peo Sojblom and it's about hiding previously used items in a
drop down box. I have my list of dealers on a sheet (GamesDealt):
Col A is a list of Dealers (casino type).
col B is their hire date.
From Col C thru I has headings of: BJ PG WH TCP BS MB CR (games we offer)
I have an "X" in the row under the colunm of what game(s) they deal. Most
dealers deal more than one game.........
Hide & Show Sheets Based On Names
Dec 19, 2006I made one file with 13 sheets.
sheet1 tab name is : MAIN
and other sheet tab name like following
2. xyz-Sales
3. xyz-Rev
4. xyz-SSN
5. xyz-ddn
6. abc-Sales
7. abc-Rev
8. abc-ddn
9. abc-ssn
10. ddd-sales
11. ddd-Rev
12. ddd-ssn
13. ddd-ddn
In Main sheet There are 3 buttons
1 . XYZ
2. abc
3. ddd
when user press on xyz button then only xyz sheets (like sheet 2 to 5) are shows to user and other sheets are very hide
if user press abc button then only abc sheets (like sheet 6 to 9) are shows to user and other sheets are very hide
i don't want to use
Sheet2.Visible = xlSheetVeryHidden
i want to use finde xyz sheet tab name and shows and other are hide.
Replace Range Names In Formulas With Range References
Jul 7, 2007I have inherited an Excel workbook in which the formlas all contain cell names (and there are thousands of names in this book). I need to find a way to change from using cell names in a formula back to a standard absolute cell reference but have no idea how to do this?
View 9 Replies View RelatedSelect Range Using 2 Range Names
May 29, 2009I know I'm not too bright, but I've really lost it here. Can someone give me the sintax to select all cells between two names: Start thru End
View 5 Replies View RelatedUsing Range Names
May 16, 2007I have in column B a link to raw data on another sheet within the same workbook that returns the same of a segment (ie "MSEC"). In column A I have SUMIF statements that total up the volume associated with each segment (ie "MSEC") that is shown in the report. Each SUMIF total is named (through Insert/Name) something that includes the segment name (ie "MSEC_TA"). In column V I want to offer a share number which shows the percentage of the total (ie "MSEC_TA) that each item is. So, I want a formula that allows me to say "E6/the text in B6 & "_TA". Since the text in B6 is "MSEC", that would give me the formula of E6/MSEC_TA, which is the named range which holds the total.
Matching First Name In Range Of Names
May 1, 2009I'm trying to find a way to check if the first and last name that are the content of cell A are anywhere in cell B and beyond. I've included an example: http://tinypic.com/view.php?pic=28cpkr9&s=5. In the example A1 must match B1, C1 and C2.
View 5 Replies View RelatedInserting Into Range Names
Jun 28, 2012Suppose that i have a table that displays different metrics for each month of the year. I'm dealing with waste management at a thrift store so I'm looking at the weight of donations salvaged, weight of donations sent to landfill and weight sold and so forth. It would look something like this..
weight sent to landfill
weight sold
january
500
450
february
350
390
march
400
789
In my dashboard I'd like to be able to sum the total weight salvaged for certain months like Jan-Feb or Jan-March.
I've named the ranges such as "weightsalvaged.Jan_Feb". So for the sum of the weight salvaged, I have the formula =sum(weightsalvaged.Jan_Feb)
I would like to have another cell where I write "Mar" and the name of the range in the formula automatically changes from
=sum(weightsalvaged.Jan_Feb) TO =sum(weightsalvaged.Jan_Mar)
Delete Range Names
Nov 8, 2008I inhereted a spreadsheet where the designer used range names for all formulas. i hate range names. is there any way i can delete all range names and have all formulas go back to showing cell references instead of range names? When i just go through and delete the range names, i get formula errors.
View 9 Replies View RelatedVLookup And Range Names In VBA
May 23, 2009I have the following
title = Application.WorksheetFunction.VLookup(Q, Range(QandA), 2, False)
QandA is a range that is defined in the active workbook. The code excutes without error but TITLE does not get a value because QandA is reported as empty (when I position my cursor over it).
I have also tried:
title = Application.WorksheetFunction.VLookup(Q, Range("QandA"), 2, False)
Range Names Between Workbooks
Jun 21, 2007In one workbook, i have a sheet with date like this:
TITLE
Data......
TITLE2
Data2....
In each case, the Data is a block of cells of varying number of columns and rows. Each of the data blocks is a named range. When I reference the ranges from a separate workbook, the results from calling the range don't work at all (return #VALUE!) UNLESS the layout of the second workbook is the same as the source data! so for example, if range Data is in cells A2...F5, then i can reference that data ONLY if i put my formula in cell A2...F5 on the second workbook. this appears downright ridiculous to me and i'm hoping someone can tell me what i'm doing wrong!
Using Sheet Names As A Named Range
Feb 6, 2009I have a namd sheet for everyday of the year in a workbook, so jan1 jan2 jan3 etc. I have a stats page which brings together certain pertinent data from those pages, so a formula on the stats page might look like this =sum('jan1'!$m$25:$m$900). What I would like to do is to name each day - jan1 jan2 etc as a named range perhaps date1 date2 etc so that the above formula would then look like =sum(date1!$m$25:$m$900).
View 2 Replies View RelatedPopulate Combobox With Range Names
Aug 15, 2007All I want to do is
put a combo box on a spreadsheet,
have it populated, and sorted - ideally, alphabetically with range names already created.
And
I'd like to be able to click on the Range Name and GoTo the location.
Using Relative References In Range Names
Jun 5, 2009I have a workbook with a single worksheet. The worksheet includes some named ranges, which are used in a dynamic chart (so that when I add data, the chart updates).
I want to duplicate worksheet#1 so that I can have worksheets 2, 3, 4 etc.
Thus I can paste new data in the new worksheets, and have all my calculations done.
Naturally, the only probelm is the graph. It is using the named ranges from worksheet #1.
Is there a solution, so that I don't have to re-create my graphs on each worksheet? (each worksheet has varying numbers of rows, AND, I will be updating each worksheet with new data from time to time, thus the need for a dynamic chart)
Sumproduct Count Range Of Names
Nov 17, 2011What is the formula for sumproduct to count a say column c for a range of names and as long it match column k for yes.
View 1 Replies View RelatedCopying Range Names From One Workbook To Another
Nov 1, 2012I have a workbook named Br2012. I would like a macro to copy the range names to BR12013.
View 2 Replies View RelatedAlphabetize Defined Range Names?
Oct 23, 2013Why can't I alphabetize all of my defined name ranges in the Name box?
View 8 Replies View RelatedRename Range Names Via Macro
Sep 7, 2007I am trying to automate the updating of range names in a spreadsheet.
On Sheet 1 Col 1 I have the names of all the existing range names.
In col 2 the new range names.
On sheet 2 the data referenced via range names.
I am getting stuck renaming the old range to the new name.
Sub Test()
Dim sName As String, nName As String
Sheets("Sheet1").Select
Range("a1").Select
Do While ActiveCell ""
sName = ActiveCell.Value
'MsgBox sName
Application.Goto Reference:=sName
Sheets("Sheet1").Select
ActiveCell.Offset(0, 1).Activate
nName = ActiveCell.Value
Sheets("Sheet2").Select
ActiveCell.Name = "nName"
Sheets("Sheet1").Select
ActiveCell.Offset(1, -1).Activate
Loop
End Sub
Automatically Updating Range Names
Dec 28, 2009I have a range of 9 cells located in A2 to A10. The name of this range should be whatever the text in cell A1 is.
The Problem is that the content of cell A1 can change since it is reflecting the content of another cell on a different sheet.
My probelm is that once i define the range to be named according A1 it will keep that name, even if the content of A1 changes.
How can i program in VBA that the name of the cells in range A2 to A10 always is whatever isthe text in A1 is? If Aq changes the name of the cells in range A2 to A10 should change as well; Plus the old name should be deleted.
I think it might work with some event trigger and then a automatic naming of a range. I tried a few things but nothing really worked. (not very exeprianced vit VBA)
Poor Performance With Range Names
Aug 24, 2006why it takes about 100 times longer to set a value using a named range. The code below demonstrates the huge difference between Range("A1") and Range("my_range")
Public Sub testloop()
Dim counter As Long
Dim start As Double
start = Timer()
Application.Calculation = xlCalculationManual
Application. ScreenUpdating = False
For counter = 1 To 34000
Range("my_range") = 9999 ' 101 seconds
'Range("A1") = 9999 ' 1.2 seconds
Next counter
Identify Range Names With Specific Value In Them
Aug 27, 2006I am developing a macro to select cells within a worksheet based on certain criteria and to make a list of their range names. My approach is to copy the range names to a column of cells using something like the following text:
Sheets("mySheet").Range("myRange").Name.Copy
but I get an error message saying "Object doesn't support this property or method". Why can't I copy a range name, and is there a way to work around this?
Combobox Populated By Range Names
Mar 5, 2007if a combobox in a userform can be populated by the NAMES of defined ranges in a worksheet?
ie. worksheet contains 3 NAMED arrays: W1 = $C$5:$E$6, W2 = $C$8:$E$9, W3 = $C$11:$E$12. Have the combobox in the userform actully list W1, W2, W3 so that when W2 is picked (for example), cell $C$8 will be selected.
Multiple Range Names For The Same Cell
Apr 6, 2007I understand how to return the address of a particular named range (that I already know the name of). For instance, the following works just fine for me:
MsgBox Range("BreedTestRangeName1").Address
In my current workbook, this returns a messagebox that reads: $C$13. However, cell C13 also has other range-names. Indeed, in my current program, it might have five or six more range names assigned to it. But I do not know how to return these names (if they exist), using only code. That is, I do not know how I can return those names, strictly within VBA. I have tried this
MsgBox Range("C13").Name.Name
But, of course, this only lists one range name; and it looks like it only lists the first one that was assigned to that cell. How can I return the others? If I can return them, I would prefer to avoid printing them anywhere onto a spreadsheet.
Concatenated Range Names In Graphing Vba
Jun 21, 2007I have a lot of seperate data that I'm trying to graph via a macro. I have one macro with a Do Until/Do While loops that runs before I import the data that creates and seperates the data into dynamic named ranges. My goal was to create a macro that graphs the different ranges using another Do Until/Do While loop. You'll see below that I have the ranges concatenated inside the loop so it does just that, graphs each set of data in each worksheet by running the same macro. Below is the code for the specific graphing macro and at the same spot, where the series values are set using the concatenated range name, the error message, "Unable to set the Values property of the series class." I have no idea what is wrong, but my guess is it's probably something simple.
Sub Graph()
Dim Counter As Integer
Dim CounterEQ As Integer
Dim Title As String
Counter = 200
Do Until Worksheets("IndustryEmployment").Cells(1, Head) <> ""
Counter = Counter - 1
Loop
CounterEQ = Counter
Do While Worksheets("IndustryEmployment").Cells(1, Explode) <> ""
GoingTo = Worksheets("IndustryEmployment").Cells(1, Explode)
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("IndustryEmployment").Range( _
"U15:U16"), PlotBy:=xlColumns.......................
List Range Names In Selection
Oct 11, 2007want a list of the defined names in a selection. So lets say the range is "A1 to G60". I would like the to see a list of each cells name and the associated address.
View 8 Replies View RelatedDelete Range/Cell Names
May 25, 2008As I have been working I have highlighted cells and typed different names in the Name Box thus creating different lists. How do I delete the lists after they have been created?
View 2 Replies View RelatedCOUNTIF Formula: Gives A Total Of How Many Names Are In That Range
Jan 5, 2009I have certain cells in column A2:A22 that have names of people. I want a formula in Cell A23 that gives me a total of how many names are in that range. I know this is simple, but how do I put my criteria that if a cell is not blank to count it?
View 4 Replies View RelatedCell Range References (Labels / Names)
Aug 6, 2013I inherited a Profit & Loss (P&L) template from a colleague. She developed it on Excel 2013 and I'm on Excel 2010, but it's not clear that is the issue here (tho, never say never).
The spreadsheet was set up for quarters and I've expanded it (by copy and paste) to 12 months. It has the typical tables: sales revenues, cost of goods, admin exp, etc. Each table has a number (varies from table to table) of rows with a subtotal in the bottom row of each table. There is also a label row above all the tables (this now has the month labels).
The problem: The subtotal (bottom) row of a table uses the formula "=SUBTOTAL (109, ref1)". In the original spreadsheet, ref1= "QTR1" (or QTR2, QTR3, etc.). I'd like to change that to be MONTH1, MONTH2.... However, when I enter MONTH1 or MONTH2, etc. for ref1, I get a formula error. Which I suspect is expected.
What I noticed is that if I highlight ref1 in the subtotal cell formula and then select the cells I want included in the subtotal, the first selected cell shows a "B8". With 2 cells, it shows "B8:B9". Good so far. However, when I get to the last cell before the subtotal row, ref1 changes to "[QTR1]", so the final subtotal formula shows "=SUBTOTAL (109, [QTR1])".
I've tried to change QTR1 to MONTH1, but get an error. I used Name Manager, but QTR1 doesn't show up on the list (However, the tables are named and seem to reference the columns correctly). If I do a "Define Name", the window pops up w/the name entry empty and the "Refers To" containing "=tblName[QTR1]" (tblName is the name given to the specific table (revenue, cost of goods, etc.) and does show up under Name Manager).
I tried an experiment and in the revenue table I deleted (move left) the cells (up to but not including the subtotal cell) under MONTH1, and then inserted (move right) a new set of cells. Now the "Refers To" shows "=tblSalesRevenue[Column7]".
So, apparently, Excel can assign some kind of label to a set of cells. My question is, how do I (if I can) change that label, or name, so that the column of cells I want to use for my subtotal formula will work (MONTH1, MONTH2, etc.). It's not clear that Define Name or Name Manager is what I should use. I looked at the original spreadsheet and there are no Name Manager listings for QTR1, QTR2, etc.
Additional Note: I forgot to mention that this isn't a show stopper, just a nuisance. If I use normal cell references (i.e. B8:B11, etc.) the spreadsheet works fine and as expected. This is just one of those "nits" that I'd like to understand so that I can make the spreadsheet programming more "readable".
Navigation Through Spreadsheet Using Shortcuts - Not Range Names
Sep 10, 2009I currently have a navigation menu with 9 options however these options are based on Range names that I have set up.
The concern is that if someone deletes the cell that contains the range name, obviously that option in the navigation won't work, nor would you want it to.
However if someone re-enters a cell that you would want linked up with the navigation there's a lot more action involved.
What i'm looking for is a macro that can be used through a button, Idealy i'd like the macro to search through column "B" and look for key labels (ex: "Ground Floor", "1st Floor" "2nd Floor" etc.) and have each button set up for it's respective search data (ex. Ground button searches only "Ground Floor")