Dynamic Named Ranges Using Match / Address And Indirect Functions
Dec 17, 2012
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?
View 4 Replies
ADVERTISEMENT
Feb 3, 2010
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: ....
View 9 Replies
View Related
Nov 22, 2006
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.
View 9 Replies
View Related
Sep 25, 2009
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.
View 12 Replies
View Related
Mar 25, 2014
I am getting a #REF error when using an INDIRECT function within a MATCH function to check against a dynamic named range. Basically, I am trying to get the row reference so that I can go back and extract other data from the row (in a table contained in another sheet) into the current worksheet.
I attach an example file for reference. The issue arises when a Dynamic Named Range is used. In the example file, if a value from a static range is chosen, the match with indirect function works, but it fails with the dynamic range.
Dynamic Ranges INDIRECT v2.xlsb
View 3 Replies
View Related
Dec 25, 2008
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 Replies
View Related
Dec 9, 2013
I have an INDIRECT function set up which should, in theory, concatenate data from a date range listed in another cell to correctly reference an external spreadsheet, which is named based on the date range it is generated for. The issue I'm running into is that the program which generates the external spreadsheet uses an MMDDYYYY format when naming its output file. This works fine if the month and day are both 10 or greater, but if they are less than 10, it doesn't work - for example, my INDIRECT function would return the date 5/3/13 as 532013, whereas the file name would be 05032013. I was contemplating using a series of IF statements to fix this, but there has to be a simpler way. Here are the relevant functions.
Function which generates date range (later used in INDIRECT function):
=MONTH(MIN(TransData!A2:A9882))&"/"&DAY(MIN(TransData!A2:A9882))&"/"&YEAR(MIN(TransData!A2:A9882))&" - "&MONTH(MAX(TransData!A2:A9882))&"/"&DAY(MAX(TransData!A2:A9882))&"/"&YEAR(MAX(TransData!A2:A9882))
TransData is a different sheet within the same workbook where the various dates are stored in column A. I found that I needed to format the formula like this in order to get it to return actual dates instead of date sequence numbers.
=AVERAGE(INDIRECT("'[DCQA_"&SUBSTITUTE(B4,"/","")&".xlsm]Total'!$C$3:$C$46"))
Cell B4 is where the date range formula listed above lives. "DCQA_" is the beginning of the filename of the external sheet, followed by the date range in MMDDYYYY - MMDDYYYY format. So, the sheet for 12/2/13 to 12/7/13 would be named DCQA_12022013 - 12072013. I'm unfortunately unable to change the way the external program names its output sheets, so I have to fix this from within my workbook.
View 2 Replies
View Related
Feb 7, 2012
I have the following formula which i'm using to indirectly reference a worksheet. The worksheet i'm referencing includes named ranges that i'd like to call on but i can't get the syntax right.
Code:
=SUMPRODUCT(--(INDIRECT("'"&HH_Elec_Title&"'!B15:B19")>=D5),--(INDIRECT("'"&HH_Elec_Title&"'!B15:B19")
View 6 Replies
View Related
Oct 4, 2005
I have tried using PULL from Harlan Grove's posts to workaround this but am coming up with #VALUE errors.
Here's what I have:
Column B contains the acct # being referenced e.g. 5230
Column E="_"&Br where r is the row #
Column F=MATCH($B$3,INDIRECT(Er&"Rows")) where _acct#Rows is a named
range referring to an external workbook e.g. _5230Rows
Columns G-R=INDEX(INDIRECT($Er),$Fr,COLUMN(G$6)) where_acct# is a named
range referencing an external workbook e.g. _5230
I thought that INDEX($Er, $Fr, COLUMN(G$6)) should work in Columns G-R however it returns #REF!
I need a solution to replace INDIRECT so I do not have to have both workbooks open together. The named ranges are static, but reference external workbooks.
View 13 Replies
View Related
Feb 13, 2007
I am trying to use a combination of dependent named ranges and variable length ranges, so one can select in eg Col A truck name from a drop down list, and then in Col B, the engine variant only for that make of truck. The indirect(substitute) function works well if I define the dependent range name (the engine variant) using actual cell refs. However if I use the variable length name definition as described in Ozgrid (ie with offset function), I get an error message and the dropdown menu freezes. Is there anyway to overcome this without resorting to VBA?
View 4 Replies
View Related
Feb 11, 2012
The formula is =SUMPRODUCT((Group="A")*(Project_Description="Long Term")*(Profile="B")*(INDIRECT(B9)="x"))
B9 through M9 have header text Jan_2012, Feb_2012...Dec_2012. Basically my aim is to get the above formula working before I drag it across so that the named ranges get picked up automatically from the headers.
Jan_2012 thru Dec_2012 are dynamic named ranges using INDEX (and not OFFSET as someone mentioned OFFSET is a volatile function).
The above SUMPRODUCT formula is giving me a #REF! error for the (INDIRECT(B9)="x") part. I know that because when I replace it with (Jan_2012="x") it works fine.
I realized while writing this that it may be because the named range in Jan_2012 is not the same size as that of other arrays. [but it is the same size - I've re-confirmed just now]
View 5 Replies
View Related
Aug 26, 2009
I am trying to do season stats for my roller derby league. I have a great spreadsheet going but the one thing I want to do, I can't seem to do. I am attaching a "test" file. very simple and small but shows what I need to do.
I have different sections and I am trying to pull the highest number out of that section. For this example, it is under the green Jams Skated: Jams at Jammer. The number on the right is the highest number of jams skated while the number on the left should be the name of the skater who has this number. Okay, no problems there. My problem occurs when I try to move these same formulas to another sheet. in this case, sheet 2. It pulls all kinds of strange things.
View 4 Replies
View Related
Jun 21, 2009
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 Related
Sep 28, 2011
determining the correct formula to lookup various details from multiple worksheet:
Data as follows:
Sheet1: input
AB1MonthSales2AUG55003MAY4000
Sheet2: Data
AB1MonthSales2JAN20003FEB25004MAR30005APR35006MAY40007JUN45008JUL50009AUG550010SEP600011OCT650012NOV700013DEC7500
b2,b3 Formula = Indirect(Address(match(a2,????,0),1,1,1,"Data"))
i dont know what is the correct range inside the match fuction to identiy the row number inside the Data sheet, i think the solution would be match(a2,Data!a2:a13,0) but things might get complicated if you have multiple worksheet such as sheet2: Data , sheet3: Data2, sheet3: Data 3
and each table has unsorted details compared to others sheets (Data vs Data 2)
View 9 Replies
View Related
Aug 20, 2009
Dynamic named ranges - lots of them! ...
View 9 Replies
View Related
Aug 26, 2012
I 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.
View 3 Replies
View Related
May 6, 2014
I have 153 rows of data to name, with a different offset formula per row.
View 8 Replies
View Related
Nov 24, 2008
I 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!
View 2 Replies
View Related
Mar 27, 2008
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)
View 9 Replies
View Related
Apr 28, 2008
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?
View 9 Replies
View Related
Mar 19, 2008
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.
View 9 Replies
View Related
Nov 27, 2007
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 Related
Feb 4, 2010
I 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 "" ?
View 1 Replies
View Related
Apr 3, 2008
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.
View 9 Replies
View Related
Jul 28, 2008
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 Related
Sep 9, 2006
I 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 Related
Oct 13, 2007
I 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.
View 9 Replies
View Related
Jul 27, 2012
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.........
View 1 Replies
View Related
Aug 7, 2014
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!
View 4 Replies
View Related
Oct 13, 2011
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
View 3 Replies
View Related
