Dynamic Named Ranges Where Data Is Manually Added And Deleted

Apr 22, 2013

A lot of the Workbooks that I design for use by myself and colleagues require data to be copied in from external data sources. To avoid named ranges from failing, I always use the following method:

Calculate the length of the data set:

Code:
=COUNTA(INDIRECT("'Data Sheet'!"&"$A:$A"))

(There will be no gaps in the data, hence a count is fine.) This named range is called DSROWCOUNT.

Example named range for the data in column A:

Code:
=OFFSET(INDIRECT("'Data Sheet'!"&"$A$1"),1,0,DSROWCOUNT-1,1)

I use INDIRECT to ensure my named ranges do not fail if the data is deleted (accidentally or intentionally), as #REF! errors will occur.

The problem with this method is that it automatically makes the Workbooks volatile because of the use of OFFSET and INDIRECT, hence the Workbooks always needs to be in manual calculation mode to be usable.

View 7 Replies


ADVERTISEMENT

Use A Named Range To Many Columns Are Added Or Deleted The Data Is Pulled Correctly

May 4, 2006

I have a spread sheet that I pull data from different columns on a particular row. The problem is the code I used works great as long as the column never moves from its current location. Is there a way to use a named range to make the following piece of code work, so no matter how many columns are added or deleted the data is pulled correctly?

View 2 Replies View Related

Macro Runs Fast When Rows Added / Deleted Slow When No Rows Added / Deleted

Jun 27, 2013

We have created a macro that basically looks for rows that contain an "H" and hides the row if it does.

Users can add new rows throughtout the year to this spreadsheet. and based on certain criteria, an H or U will be placed in a hidden column which the macro looks at and hides any row it finds an H.

The user has to click on the button that has the macro assigned to it once they have finished working on the spreadsheet.

The problem we're finding is that for users who insert/delete rows, once they click the button it takes up to 15 seconds to run through macro (which is ok). However, users who haven't added or deleted any rows and who click the button, they have to wait upto 5 minutes (which isn't ok) for the macro ro run.

We can't figure out why the macro takes longer to run when no changes have been made?

View 8 Replies View Related

Dynamic Named Ranges With Linked Data

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

Dependent Data Validation With Dynamic Named Ranges?

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

Dynamic Named Ranges And Indirect Data Validation INCOMPATIBLE?

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

Dynamic Starting Point For Dynamic Named Ranges

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

Add Data Monthly And Then Have To Manually Update The "source Data" To Reflect The Added Data On Chart

Apr 28, 2009

I have a column line chart to which I add data monthly and then have to manually update the "source data" to reflect the added data on chart. This is a rolling graph, which mean that I have to remove data for one month(from last year) and then include the new month's data. Is there any way on automating this process...like a macro or something, so once I add the data excel automatically removes one month of old data and make changes to include fresh data. Eg Currently chart is based on data from A2:F2 and I add new data to cell G2. I need something which automatically update the source data to cell B2:G2.

View 4 Replies View Related

Dynamic Named Ranges ...

Aug 20, 2009

Dynamic named ranges - lots of them! ...

View 9 Replies View Related

How To Add Numerous Dynamic Named Ranges

May 6, 2014

I have 153 rows of data to name, with a different offset formula per row.

View 8 Replies View Related

Dynamic Validation Without Using Named Ranges

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

Non-Volatile Dynamic Named Ranges

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

Advanced Dynamic Named Ranges

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

INDIRECT And Dynamic Named Ranges

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

Multiple Dynamic Named Ranges

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

Dynamic Named Ranges :: Formula Results In #VALUE

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

COUNTIF, INDIRECT And Dynamic Named Ranges

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

Vlookup And Dynamic Named Ranges = #ref Error

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

Query Based On Dynamic Named Ranges

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

Dynamic Named Ranges Based On Cell

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

Create Dynamic Named Ranges With Code

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

Create Chart Using Dynamic Named Ranges For Series

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

Rank Formula Using Arrays And Dynamic Named Ranges

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

Dynamic Named Ranges Not Appearing In F5 (GoTo) List

Mar 2, 2010

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>

View 9 Replies View Related

Macro Quit Working - Dynamic Named Ranges

Jun 1, 2006

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.

View 3 Replies View Related

Dependant Validation Lists With Dynamic Named Ranges

Jun 18, 2008

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))

View 7 Replies View Related

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 View Related

OFFSET-COUNT-MATCH Method To Create Dynamic Named Ranges

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

RowSource To Reflect Data Added To Named Range

Nov 8, 2006

I have several ComboBoxes & ListBoxes on a Userform that populate by referring to Dynamic Named Range in the RowSource property. Also on the Userform is an area to edit/add/delete items in these named ranges. If I Add or Delete an item, the Named Range does not automatically resize--or at least the Menus don't "resize" accordingly--unless I unload the UserForm and load it back up. Is there a way to refresh these Comboboxes without reloading the UserForm?

View 2 Replies View Related

Automatically Adjust Dynamic Chart As Data Added To Source

Jun 19, 2008

Is there a way to make a chart adjust automatically when you add to the source data? I'm trying to use a named range in order to power a chart (something like chart_data=offset(a1,,, counta(a:a),counta(1:1)); the range is working fine). When I put "chart_data" into the source field for the chart, it seems to work fine; it picks up the right range... But when I later add more data, the chart source data field seems to have converted my named range into a hard-coded range, absolute range. So the dynamical named range updates, but the chart doesn't and I don't see the new data. Am I doing something wrong that I need to tweak? Does what I'm trying to do just not work? Is there another way to make the chart pick up the new data without adjusting the range?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved