Excel Dynamic Chart #12: INDEX function as Alternative to OFFSET function for Dynamic Ranges

i currently have multiple dynamic named ranges in my file all defined by the following formula (or similar to):

=OFFSET($B$4,0,0,COUNTA($B:$B),COUNTA($4:$4))

the problem as you all know is that this is a dynamic function and therefore it is impacting the speed of my workbook tremendously. through searching other posts i have found out that i can define the ranges of rows and columns by using formulas similar to this:

=MATCH(REPT("z",255),Sheet1!$D:$D) (for rows)
=MATCH(REPT("z",255),Sheet2!$2:$2) (for columns)

I am just having a hard time putting those together to define a range. Can anyone out there lend some insight? Thanks for the help!

Gex

I just saw an example of this formula in an online newsletter:
=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A$1:$A$19),COUNTA(Data!$1:$1))
I was trying it out and noticed that you have to place it with the upper
left cell in cell A1 of a worksheet for it to reproduce the data that it
refers to. Otherwise the #VALUE error appears. This is not how the OFFSET
function usually works for me and it seems to have something to do with the
COUNTA function appearing in the height and width arguments. I usually don't
use the height and width arguments at all. Could someone explain why this is
so and if there is a way to overcome this restriction?
Thanks for your help,
Sandy

Hello Guys,

I need your assistance in the defining Excel Micro.

I am thinking of using Offset and Counta function in defining ranges in a worksheet1. In this way ranges came dynamic. Then use these ranges to copy and paste data from multi worksheets to a single sheet.

Each month make a change in worksheet1 to move to new month in a column.

Any examples that you have, you can share to make life easy. This will be very much appreciated.

Or any other better solution that is on offer. I am thinking of Dynamic ranges/Offset function.Thanks
Regards
Raj

Hello,
I need to be able to select a dynamic range from a group of data that I have. The data are arranged in columns with 38 rows per data entry. I want to be able to use the last 400 columns to do calculations i.e. a 400 column x 38 row block of data, with the rightmost column being the latest entered data.

I have previously attempted to do this with the OFFSET function and inserting the formula into a named range to make referring to it easy. But I seem to be getting a strange problem when I tried using the formula on subsequent groups of data (I have 11 'groups' of data in this column arrangement). The data has a few rows of pre-loaded cells at the top which make it impossible for me to use the top cell as a reference point as the rightmost has no actual data. To get around this i've used OFFSET to look at the bottom datum point (38th row), continue to the right until it finds the end, then count back 400 rows. (at least that's my interpretation of offset). The problem that I get with this is that when I transfer my formula to another group and redefine parts of it as each is a little different the range that it gives me then has a few extra columns on the right where it seems to have overshot and then counted back. It shouldn't do this as there are only blank cells right of my data on the row it uses. The problem seems to compund itself and become worse with each group of data, giving me something like 132 extra columns on my last group.

To me OFFSET seems to be unstable, giving me a different result when there is nothing wrong in the entered formula.
Here's my formula in case anyone manages to see something that might cause this issue:

=OFFSET(Sheet1!$I$49,0,COUNTA(Sheet1!$49:$49)-1,-38,-400)

(If there's an error with 'Sheet1!' ignore it..I just replaced the name of my actual sheet with that)
- The first bottom datum point is in I49 for this particular group



Any suggestions on how to obtain a dynamic range without offset are welcome. I also have a problem where some of the groups have a smaller data range than others, so the '-400' causes me to get an error as it tries to get columns before A. to fix this I reduce the -400 to -200 etc, but i'll have to change that again later as the data range becomes larger to incorporate 400 entries. It has been suggested that an 'error handler' type thing be used to instruct excel what to do when it encounters this particular problem. If anyone could advise me on that it would be appreciated.

Hello all, i have a pretty large file that has many dynamically named ranges using the offset formula. I am trying to speed up the calculation times and one of the things i am thinking might help would be to eliminate the volatile offset dynamic named ranges. Currently the named ranges are named using the following formula:

OFFSET(!$A$1,0,0,COUNTA(!$A:$A),COUNTA(!$1:$1))

can someone please help me with an index alternative to this? Thanks for your help!!

I am using VLOOKUP to return some data into a cell; however, the range in which VLOOKUP scans through is dynamic, since the amount of data rows present change depending on the log file. Is there a way to "F8+CTL+DOWNARRROW" the range within the VLOOKUP function to ensure that the function scans all rows that contain data?

i have 2 worksheets. one with portfolio stock data, and a second that i would like to use for dynamic charting. the chart worksheet has a drop down menu that chages a refernce cell to a number corresponding to the collumn in the portfolio worksheet for the chosen ticker

i would like to make a dynamic chart that updates the data ranges based on something like this:

=OFFSET(Portfolio!AN24, 2, Charts!B2, 14, 1)

******** ******************** ************************************************************************> Microsoft Excel - Portfolio.xlsx ___Running: 12.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout AO24 AP24 AQ24 AR24 AN26 AO26 AP26 AQ26 AR26 AN27 AO27 AP27 AQ27 AR27 AN28 AO28 AP28 AQ28 AR28 AN29 AO29 AP29 AQ29 AR29 AN30 AO30 AP30 AQ30 AR30 AN31 AO31 AP31 AQ31 AR31 AN32 AO32 AP32 AQ32 AR32 =
AN AO AP AQ AR 24 * AAPL AKAM AL AMTD 25 * * * * * 26 5/17/2007 $109.44* $43.21* $81.34* $17.64* 27 5/16/2007 $107.34* $42.87* $80.92* $17.50* 28 5/15/2007 $107.52* $42.30* $81.80* $17.33* 29 5/14/2007 $109.36* $44.27* $79.54* $17.68* 30 5/11/2007 $108.74* $45.70* $80.05* $17.96* 31 5/10/2007 $107.34* $44.19* $78.43* $18.14* 32 5/9/2007 $106.88* $44.35* $78.31* $17.94* Portfolio *
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

where AN24 is a blank reference cell, my data range starting 2 rows below, and cell B2 in the Charts worksheet tells it how many collumns over to go to start my range, with a range 14 rows by 1 collumn

i get an error saying it is an invalid range though.. what am i doing wrong? would greatly appreciate the help

Hello,

I have a workbook that I am developing which will use named ranges to graph different data depending on what is selected from a drop down box.

I have most of it worked out, i've just hit a snag when entering my named ranges into the chart series.

my first named range is "Date". The named range links to a range which is on a different sheet to the one in which i'm putting the graph. when I type "='Book1.xlsx'!Date" into the series x values and then click out of it I get an error saying something about a formula reference.

I've checked to make sure that the named range works by selecting a bunch of cells and entering it as an array and all seems to be fine. I've searched for answers in a lot of places and everything just ends by saying to type what I have into the series box and it works...except mine doesn't.

There also appears to be another method involving the SERIES formula but im not sure how to use it because nothing i've found is clear on how to use it. They just say to enter it into the values box and show a screenshot of what looks like excel 98...im using excel 2007


If anyone can help me out here that would be great

Hi All -

I'm trying to use INDEX to return values from different arrays in a single cell; i.e., the value returned will change based on the user's selection from a drop down box.

For instance, if the user selects "New York" I want the INDEX function to return the appropriate value from the range named "NYC"; "Los Angeles" will return from "LA".

I've not been able to figure out how to make the first INDEX argument dynamic - I've tried naming the ranges and having the user input point to the name and to text that represents the range ($C$2:$F$40).

TIA for any assistance!

Regards,

-ears

This is a common problem mentioned in numerous places on the forum, but I haven't yet found a good solution to it. In summary:

1. I've created cascading drop-downs; drop down 2 shows only choices relevant to the choice made in drop down 1.
2. Drop down 1 is populated using a dynamic named range 'GROUPS'.
3. Drop down 2 is populated using the INDIRECT function referencing the cell where drop down 1 is located.
4. The names that appear in the drop down 1 are all defined dynamically using the the OFFSET function because I don't know how long each list will be.

Excel does like INDIRECT and OFFSET combined because INDIRECT is looking for a defined location and OFFSET is returning an array. The result is an error and no choices available in drop down 2.

Has any described a workaround?

Thanks in advance!