I use the standard OFFSET-COUNT-MATCH method to create dynamic named ranges in my Excel projects. Needless to say, this method won't work on a spreadsheet with formulas extending beyond the current range. The count function counts the cells containing formulas, even though they may contain no data. Does anyone know how to construct a formula that will IGNORE the "formula only" cells??
View 3 RepliesI keep losing my named ranges when attempting to reopen a particular file I am working on. I get the "excel found unreadable content....." error message and then when it corrects, the range names are all gone. is it efficient to create the named ranges at open? Otherwise how should it be done so that my workbook will definitely have these names each time its used?
View 3 Replies View RelatedI am employing code to label dynamic ranges that takes the form of,
ActiveWorkbook.Names.Add Name:="dms", RefersToR1C1:="=OFFSET(DMS!R10C5,0,0, COUNTA(DMS!C5),COUNTA(DMS!R10))"
'dms_j
ActiveWorkbook.Names.Add Name:="dms_j", RefersToR1C1:="=OFFSET(DMS!R11C10,0,0,MATCH("" * "",DMS!C10,-1),1)"
'dms_p
ActiveWorkbook.Names.Add Name:="dms_p", RefersToR1C1:="=OFFSET(DMS!R11C16,0,0,MATCH("" * "",DMS!C16,-1),1)"
'dms_r
ActiveWorkbook.Names.Add Name:="dms_r", RefersToR1C1:="=OFFSET(DMS!R11C18,0,0,MATCH("" * "",DMS!C18,-1),1)"
'dms_t
ActiveWorkbook.Names.Add Name:="dms_t", RefersToR1C1:="=OFFSET(DMS!R11C20,0,0,MATCH("" * "",DMS!C20,-1),1)"
The dynamic ranges are getting entered but the problem is that the range "dms" overshoots by six cells into blank cells at the bottom of the table, and the rest of the ranges overshoot by ten cells into blank cell area. I have deleted, cleared and destroyed everything around the table and re-sized the active area to no avail.
I've created my dynamic named ranges using the OFFSET function, ex.
="OFFSET(SAMPLE!$D$4,1,0,COUNTA(SAMPLE!$D:$D)-1,1)"
I'm now trying to get my chart to use that range. I read at [URL] .... that I need to make sure my series reference is a fully qualified reference. So I've entered that series reference using the name of the workbook followed by the range name (=2012-PIRS.xlsx!SAM_CLAR2DEL). I have two copies of my workbook (one as xlsm with VBA project, and one with xlsx without VBA) and I can't get Excel to accept the series value in either workbook.
ERROR MSG:
The formula you typed contains an error. Try one of the following:
- Make sure you've included all parentheses and required arguments.
- To use a function, click Insert Function on the Formulas tab (in the Function Library group).
- If you include a reference to another sheet or workbook, verify that the reference is correct.
- If you are not trying to enter a formula, avoid.........
I am having an issue when I try a chart a named range. The named range "Refers To" is
Code:
=OFFSET($A$1,(MATCH("Kevin",$A:$A,0)-1),,,COUNTA(INDIRECT(ADDRESS((MATCH("Kevin",$A:$A,0)-1),1,1,1)
&":"&ADDRESS((MATCH("Kevin",$A:$A,0)-1),100,1,1))))
As far as I can tell, the formula works just fine. If you look in the Name Manager and check on that Name, the highlighted box shows up exactly what it should and if you do a simple MAX test, it displays the correct MAX value from that range.
The problem comes in when I try and add it to a chart. Instead of a displaying all the values in the range, it displays nothing. Doesn't flag up any errors, just nothing.
The reason I chose to do it this way is that the table contents could change on a daily basis and so could the position of the row and I don't want to have to keep changing the references in the graph so make sure the right data is being displayed. Is it just that charts don't play well with the INDIRECT function?
I would like to replace the blue bit of this Dynamic Named Range (DNR) with an INDIRECT formula in order to modify the starting point for the DNR: =OFFSET(DATA!$C$60,0,0,20,1). Unfortunately, I can’t seem to get my INDIRECT formula to work in order to use it to replace the blue bit above: INDIRECT("'"DATA"'!&ADDRESS(MATCH('SHEET1'!AC8,DATA_Date,0)+22,3)"). The orange bit of the formula above returns a value of 60, therefore the ADDRESS formula should return $C$60, that can then feed into the INDIRECT and act as the equivalent of DATA!$C$60. But it doesn’t.
View 2 Replies View RelatedI know how to create a dynamic named range so that no blanks appear in my data validation list but have only ever done this for a list in a column, i.e...
-----A-----
1--Apple
2--Orange
3--Pear
4--Bannana
5--
6--
A1:A6 would be named Fruit, then name manager, edit: =OFFSET($A$1,0,0,COUNT($A1:$A6),1)
The Data Validation list then shows Apple, Orange, Pear, Bannana. And if I add Peach into A5 later, that then appears in the list.
What I need though is to edit this as if the named range 'Fruit' covers cells A1:F1.
------A---------B-------C--------D-----------E-----------F
1--Apple---Orange---Pear---Bannana---
2--
When I make a dynamic named range, I typically do something like:
=offset($A$1,,,counta(A:A),counta(1:1))
I remember awhile ago I saw (within some of the comments on Jon Peltier's site, I believe) a way to make a dynamic named range without using offset. Does anyone know how to do it?
I am a novice excel and VBA user, but have been working the past few days on a project to create a data entry form (using Excel 2003 on WinXP). I have the userform created and the textboxes working fine for inputting data into the spreadsheet. I also have comboboxes set up, and if I directly input data into them, they also input that data. So far, so good.
(problem)
Here is my issue: I want the comboboxes to use dynamic / variable ranges, so I have been trying to set up the ranges by defining them as names. I've come across a bunch of examples, but none of them seem to work (all produce an error that the formula is not correct or instead seem to reset the name to just the first cell when I input it).
My sheet is called Dropdown Lists, and there is a column here for each combobox. Each row in a column has the options I'd like used for that combobox. So I am basically trying to name a range as the entries in just one column.
This is what I have been working with most recently in the Name - > Define dialog box that according to everything I read -SHOULD- be working:
=OFFSET('Dropdown Lists'!$A$3,0,0,COUNTA('Dropdown Lists'!$A:$A),1)
This produces an error, that when you hit OK highlights "$A$3" - and if I remove the $ symbols, it skips then to the "$A:$A" until I remove THOSE $ symbols, and then excel will accept it, but when I go to the name, it only highlights one cell. I thought maybe that was OK, but when I tried putting the name into my rowsource, nothing happened - so apparently not.
Questions:
(main question)
- Why will this not input correctly when everything I read suggests this format? Am I not handling the space in the worksheet name correctly (with ' ')? Sometimes excel will automatically add in " " between = and OFFSET and the closing at the end, but this also has not helped. One source I read even recommended taking off the = at the start (also did not help). Am I missing something here?
(follow-up questions)
- Why are you supposed to use "$" before the cell references anyway? I've looked for an answer but haven't found one yet... is that for when you make cell references to cells on an external worksheet?
- What is the "Add" button for on the Name -> Define dialog box? I'm confused to if I am supposed to use this to add the name to the list of defined names, or just hit OK?
- At the bottom of the Name -> Define dialog box is the "Refers To:" field with the formula. On the right of that you can hit a button to go to what seems like another (smaller) formula input box. Why would I ever use that when I could just type it in on the previous box? There is no label for it so I am guessing here - but does it actually do something else (like you need to go to that smaller dialog to input new formulas and the previous box is only for -viewing- formulas, or?)?
I have a problem with an offset match function. What I want to do is look at the value in cell C3 and then match it to a location in a named range and then return the value 8 rows above. The problem is the named range contains non contiguous cells i.e. D10:AE10, D22:AE22, D34:AE34. So if the value appeared in the area D10:AE10 it would return the value in the matching column row 2. How to do this I have tried lots of combinations but they just come out #N/A.
View 3 Replies View RelatedDynamic named ranges - lots of them! ...
View 9 Replies View RelatedI have 153 rows of data to name, with a different offset formula per row.
View 8 Replies View RelatedI have a question regarding dynamic validation. I have attached a sample spreadsheet to clarify if my explanation doesn't. I have a list of codes that map to a list of other codes that are not one to one mapping. I would like for the other users of this code to populate what the correct code on our side is but without having to go thru the entire list in a validation field. (if that makes since)
Using validation before in a similar instance i used indirect to grab a named range to help shorten the list for them. However in this case that would be a ton of ranges i would have to set up! I've tried Match, countif and index in an offset formula in the validation list but can never get it to work!
I am looking for a non volatile alternative to using OFFSET in my named ranges as my models are generally quite bulky and it is beginning to make me quite angry having to build these models in calc manual.
Can anybody provide a non-volatile alternative to this?
=OFFSET(Query!$A$14,,,Height-13,19)
With Height being:
=MATCH(BigText,Query!$B:$B)
I have a spreadsheet with a large number of graphs on and lots of data.
Most the graphs look across 20 or so columns and one or 2 rows down.
The problem is that l need to insert new colums in now, and obviously excel graphs dont recognise the inserted column data.
Therefore l believe using named ranges may solve this problem...
I have the formula =OFFSET(Sheet1!$A$1,0,0,2,MATCH("DC",Sheet1!$1:$1))
However i need to expand on this to achieve what i need to do.
1. Firstly l only want to look from column C onwards (so ignoring all data in column A and B)
2. I need the match to match to columns starting with the letters "DC", however there is a number after this, so it is not a direct match. The match needs to ignore columns with any other heading start.
3. is there anyway to automatically refresh the graphs to the named range once it changes, or any simble vb macro i can run?
I have a dynamic named rage "AREA" which can contains values like "Area1, Area2, Area3, ..."
For each item in list AREA I have another dynamic named range, named exactly as the item, so for example I have a dynamic named range "AREA1" with values "SubArea1.1, SubArea1.2, ..." and also a dynamic named range "AREA2" with values "SubArea2.1, SubArea2.2, ..." and so on, I think it's pretty clear and straight forward (I am modelling the fact to have several areas and each of them has several sub areas).
Now, I have cell A1 being validated against the list AREA, that is, the source for A1 is "=AREA", so that I can choose one of the values in the named range AREA; I also have cell A2 being validated against the list that I choose in cell A1 using the INDIRECT function, that is, the source for A2 is "=INDIRECT(A1)".
I have already realised that this approach for cell A2 does not work, as it seems that INDIRECT does not work for dynamica named cells.
An option would be using the following for the source of A2: ....
I am trying to create a dynamic named range with a sales rotation tracker. Basically, I have a list of leads that come in each week and I add them to the tracker distribute the leads to sales people based on a rotation. I would like to create a dynamic named range for each sales person that will select each sales person's name including the lead information in the row.
I have read all of the dynamic named range information at the link below as well as the advanced dynamic named range page and I can't seem to put my finger on this short of doing it manually.
In column C i have dates. I have formula that count all the dates that are older than 20 days. The formula works perfectly. Now i made a Dynamic Named Range of the column A and now the formula results in #VALUE.
View 11 Replies View RelatedI am using OFFSET to make dynamic named ranges- the only problem is that I'm having trouble using it with linked data-
The Situation:
-Cells A1:H500 are linked to another sheet, with each cell containing a formula like =IF(ISBLANK('C:...[workbook.xlsx]worksheet'!A1),"",'C:...[workbook.xlsx]worksheet'!A1)
-Currently only cells A1:H100 have data, but this is dynamic and will change
-I want a named range that will only select the cells with data in them...the problem is that COUNTA counts the link formula even if the cell is "blank" (because it isn't blank).
-I hate hard-coding ranges...the solution for the named range =OFFSET($A$1,0,0,500-countblank($A$1:$A$500),8) is ugly!
Any solution to making dynamic named ranges with linked data that may or may not be "" ?
The following formula produces the desired result:
=COUNTIF(INDIRECT(TEXT(A8,"mmm")&"!B2:Z100"),"SK")
but replacing the range of cells with a dynamic named range returns #REF!:
=COUNTIF(INDIRECT(TEXT(A8,"mmm")&"!Data"),"SK")
where A8 is the date 01/01/07. I'm trying to count items within the range Jan!Data.
I'm not sure if I'm trying to do the impossible, or if I'm missing something.
I am trying to merge data from two worksheets onto a 3rd for a mail merge. The COLLECTIONS sheet contains the acct #, name and amount owed. The ADDRESSES sheet contains the acct #, name and all of the address information. The 3rd sheet is the MERGE sheet that I'm using as the reference point for my word document and the mail merge. Upon reflection the MERGE sheet is probably not necessary, but made sense to have it when I started out.
Never the less...
On the ADDRESSES sheet I am trying to name a dynamic range "AddressList" (I'm trying to go dynamic because the list of addresses will change from month to month). I am using the following formula in the refers to field when I name the range:
=Offset(Addresses!$A$5,0,0,CountA(Addresses!$A:$A),CountA(Addresses!$1:$1))
I think this is what is giving me my problem, because the named range does not show up in the list when I try to go to the named range.
Just in case that is not the problem, the #Ref is showing up when on the MERGE sheet I type a vlookup formula referencing the acct # on the ADDRESSES sheet. That formula looks like this:
=VLOOKUP(A4,AddressList,3,0)
which seems simple enough. Before trying to make the named range dynamic the formula worked fine, which is why I'm thinking the named range is what's giving me the problems.
I m trying to make a query based on a dynamic range and paste the results in a sheettab of my wrorkbook.
View 9 Replies View RelatedI have just started diving into the vast world of dynamic ranges and it's a bit overwelming. I have a dB of sorts that a listbox in a userform fills off of. The dB will be driven by column A and B. Column B are names that are displayed in the listbox. Column A is the class that these names belong to (3 classes). I need to develop dynamically named ranges of Column B based off of Column A. In other words, I need a dynamically named range for all of class1, class2, and class3 in column A to display column B in the listbox on the userform. I have attached an example to clarify the example.
View 6 Replies View RelatedI have three columns of data in columns A,B,C - there are formulas in these columns that either produce a numeric value or return blank.
I have created Dynamic Named Ranges for each of them and have entered the following formula:
=OFFSET(Working!$A$1,1,0,MATCH(1E+305,Working!$A:$A,1))
My issue is that this formula is looking one row past the last numeric entry in the column (a blank) which is leaving a blank space when I graph this data.
I am unable to use dependent data validation lists using the INDIRECT function when the initial named range is a dynamic one.
See attached.
The named ranges 'Men','Women','Children' are all dynamic based on number of entries in each column order to accommodate a growing list, whilst also not having blanks in the dropdown (hence I haven't used entire column ranges). The named range 'test' is a static one.
Column H has a dependent data validation based on entry in Col G. This works for the static list, but not the dynamic one!
I need to rank the top 3 names for each location based on gender (see sample data table below).
My spreadhseet has been setup using dynamic named ranges ("Size", "Name", "Gender", "Location" and "Score") using the formula "Size"=MATCH("zzzzz",'Sheet1'!$A:$A) and then "Name" =OFFSET('Sheet1'!$A$2,0,0,Size), and so on for "Gender", "Location" and "Score".
Is there a way to design an array rank formula using the dynamic named ranges to return the name associated with the highest score for "M"s in location "N"? I want to be able to edit the formula so I can chose which rank to display (e.g. 3rd, 7th, etc).
I've been playing with the following but can't get it to work
{=RANK(IF(ISNUMBER(MATCH(Gener,{"M"},0)),IF(Location{"N"},0),Score)}
NameGenderLocationScoreTimMN89
RebeccaFN45DanaFN85TerryMN15
RachelFN78BruceMN31BryanMS15
DonnaFS80JakeMS67MelissaFS66AdamMS48KateFS90
I want to start a new thread about this issue - it was previously listed on an old thread but slightly different topic: refer[url].
The problem is I have lots of Dynamic Named Ranges using formulae such as the following:
=OFFSET(RptPg!$F$9,0,0,MATCH("*",RptPg!$B$9:$B$200,-1),1)
. These named ranges are created in the Name Manager box and the code inserted in the RefersTo: box.
The ranges seem to work well and are dynamic as required. When I click on the range selector button against the RefersTo formula fo rthe range the range is correctly highlighted.
However, the range is not selectable from the F5(GoTo) command.
This then means for certain VBA operations, the range cannot be found and returns an error (Object not defined etc)
Is there anyway I can correct this or work around this - dynamic ranges are at the heart of my large workbook and fundamental to its performance>
I did the huge macro that names a bunch of dynamic ranges, all similar to the following: Range("=OFFSET(BD!$C$32,0,1,1,COUNT(BD!$32:$32))").Name = "ChtBDLabel"
I tested it as I went along, so I know it was working. Then I worked on several other tasks (copying the range to another location on the sheet, making graphs from the ranges). Now when I go back to run this again, I get: Run-time error '1004':
method 'Range' of object '_Global' failed. I went and looked what I thought were my named ranges, and they were wrong (not dynamic, just a cell reference). I deleted them and tried again with same error.
in a sheet I have two cells using data validation and dropdowns - the source for the first cell is a static named range - no problems. The source of the second cell is dependent on the value in the first cell and the sources are dynamic named ranges.
The dynamic ranges in cell#2 is named according to the value in cell#1 and I therefore have referenced the ranges using the INDIRECT function - but this only works with static ranges.
In the data validation source field for cell#2 I have the following formula:
=IF($A$1="",the_full_range,INDIRECT($A$1))
I can use a dynamic named range and a direct reference to the name and the DV works.
I can use a standard named range (not dynamic) and an INDIRECT() reference to a cell with the name of the range in it and the DV works.
But if I try to use a dynamic named range and an indirect reference to a cell with the name of the range in it, the DV fails.
Any workarounds out there? This project includes a LOT of DV lists, getting them to self-maintain is important if I can do it.