Using Range Names
May 16, 2007
I 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.
View 9 Replies
ADVERTISEMENT
Jul 7, 2007
I 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 Related
May 29, 2009
I 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 Related
May 1, 2009
I'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 Related
Jun 28, 2012
Suppose 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)
View 3 Replies
View Related
Nov 8, 2008
I 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 Related
May 23, 2009
I 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)
View 9 Replies
View Related
Jun 21, 2007
In 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!
View 5 Replies
View Related
Nov 21, 2007
Suppose 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
View Related
Feb 6, 2009
I 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 Related
Aug 15, 2007
All 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.
View 9 Replies
View Related
Jun 5, 2009
I 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)
View 4 Replies
View Related
Nov 17, 2011
What 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 Related
Nov 1, 2012
I have a workbook named Br2012. I would like a macro to copy the range names to BR12013.
View 2 Replies
View Related
Oct 23, 2013
Why can't I alphabetize all of my defined name ranges in the Name box?
View 8 Replies
View Related
Sep 7, 2007
I 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
View 9 Replies
View Related
Dec 28, 2009
I 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)
View 9 Replies
View Related
Aug 24, 2006
why 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
View 7 Replies
View Related
Aug 27, 2006
I 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?
View 9 Replies
View Related
Mar 5, 2007
if 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.
View 3 Replies
View Related
Apr 6, 2007
I 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.
View 4 Replies
View Related
Jun 21, 2007
I 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.......................
View 3 Replies
View Related
Oct 11, 2007
want 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 Related
May 25, 2008
As 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 Related
Jan 5, 2009
I 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 Related
Aug 6, 2013
I 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".
View 4 Replies
View Related
Sep 10, 2009
I 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")
View 10 Replies
View Related
May 25, 2012
I have many names of people in column A .. what formula do i use to count all the names in the range in coumn A?
View 2 Replies
View Related
Feb 12, 2013
I'm trying to retrieve the worksheet names to a specif cell and populate from that point only with the names of the visible sheets, but It keeps returning only the last visible sheet name and not the sheets that are visible. Where the code is failing - I'm self learner and start with VBA macros a couple of months so I'm still learning how to identify when the error show up
Code:
Sub Summary()
Dim wks As Worksheet
ultl = Sheets("Control").Cells(1048576, 4).End(xlUp).Row
For i = 31 To ultl
[Code] ..........
View 6 Replies
View Related
Apr 1, 2008
I have 100s of name ranges in my spreadsheet. I am using a series of drop down boxes for the user to select the info that they want to see. Based on the dropdown box, the macro "dynamically generates" a range name from which to pull the info.
For instance, if they Select Manager = Terry, Analyst 1, and Month = Jan, then the range name would be JanTA1
The if statement would dynamically create this name by concatenating the info
If manager = Terry
mnger = "TA"
if analyst = 1
anlyst = 1
if month = Jan
mnth = "Jan"
rangename = mnth & mnger & anlyst
So, I am dynamically creating the range name based on user selections...then to display it, I want to set the display cell = to the value
Range("display cell").Value = Range(rangename).Value
The problem is, I am getting an object error every time I try to refer to a range this way.
If I type:
Range("display cell").Value = Range("JanTA1").Value, it works
View 9 Replies
View Related
