YTLE#105: Excel OFFSET for Dynamic Range in Function

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

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.

Hi All,

Multiple time reader, first time poster...

My challenge, I have a significant data set that I am creating a report on.

the challenge is I want to create an average using firstly the unique area number that only sits in the top left of each area and then average all the postcodes that are in that area.

Here's a sample of the data sheet... We will call this sheet "Data"

A B C D
Area postcode Number Number 2
20101 2401 3.58 4.54
(empty cell)2057 4.61 6.63
(empty cell)2001 4.34 8.30
(empty cell)2067 3.79 6.53
(empty cell)2053 3.63 5.14
(empty cell)2024 3.45 5.29
(empty cell)2080 3.17 3.51
(empty cell)2052 2.45 3.14
(empty cell)2604 2.38 4.38
(empty cell)2310 1.87 5.66

20102 2201 4.76 7.41
(empty cell)2352 2.44 3.88
(empty cell)2445 2.51 3.18
(empty cell)2146 2.92 3.72
(empty cell)2294 3.10 4.21
(empty cell)2116 1.86 1.95
(empty cell)2212 3.07 3.79
(empty cell)2183 2.47 3.47
(empty cell)2302 2.16 2.95
(empty cell)2289 2.23 2.92
(empty cell)2132 2.10 2.80
(empty cell)2309 4.57 4.09
(empty cell)2299 2.32 2.85

So if the user in the report selects 20101 I want to then give an average of all the numbers in all the postcodes following that area.

I am unable to populate the area next to each postcode as this will impact the source data (which is a feed)

I have over 50 areas and over 700 postcodes which are going to be reported on by a state selection.

This is the sheet "Area Report"
where I have put in what the answer should be if returned, I am at a loss as to the best formula to use.

Average Number Average Number 2
20101 =3.33 =5.31
20102 =2.81 =3.63


Any suggestions would be great?

Thanks,

DG

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

I'm using this formula to create a List which can vary in length:

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1)

Unfortunatly it also captures the cell after the last record so that a blank appears in the list.

For example if cells A2:A10 have data in, the list shows A2:A11, I need to overcome this so that only values are selected from the list.

Could someone assist?

Guys,
I have a spreadsheet with timestamps on col A, then Col B contains the
values correspond to timestamps in ColA (Col C thru Col M are similar to Col
B as well). I am trying to build a chart in function of time (meaning
Values for the X axis would be B2..M2, and Y axis would be B3..M3 for the 1
series, then B3..M3 for the values in x-axis, and B4..M4 for Y axis, so
on...).
Number of rows is subject to change (time stamps), and I am only
interested for the last 21 entries (if there is less than 21 entries, plot
them all, otherwise, only plot the last 21 entries).
Can someone please help me with the equation (offset?) in name definition?

Thanks

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

Hi

I am using the following formula as a named range in a pivot table.

=OFFSET(OrganisedData!$A$1,0,0,COUNTA(OrganisedData!$A:$A),16)

However the range seems to be picking up 1 blank row at the end of the data which is then pulling through as an #DIV/0! in my pivot table.

Any ideas how I can make the range stop at the bottom of the data?

Thank you

Hi!

I just can't understand the complete sintaxis of the OFFSET function!!

Im using it so I can create a dynamic chart where the user can select the start month and end month of the year and the graph automatically readjust to those month ranges.

I've made dynamic ranges before, but all of them are with just ONE CELL AS A REFERENCE like: =OFFSET($A$1,0,0,1,COUNT($A:$A)-1)

So now I found how to do a chart based on start and end dates (http://blog.contextures.com/archives...arts-in-excel/) , but can't apply it to my workbook, cause I don't get the meaning of the second and third parts of the function, what to they mean with row & cols reference?

=OFFSET($A$1,=?,=?,1,COUNT($A:$A)-1)

please can somebody help?? im a forum noob

I appreciate it in advance!

I have a data set that alters each week which means at the moment I have to update the ranges manually each time.

I would like to use a dynamic range and think I need to use the Offset and CountIf functions.

Both the start and end of the ranges are dynamic.

The range is based on grouping the numbers in Column E (LocNo)


What I would like to do or think the way to achieve this is by:

First looking at Column E

Count how many cells are equal to criteria. (CountIf)

offset 5 columns left from the first cell that meets the criteria (Offset -5)

offset X rows down based on how many cells meet the criteria (Offset X)


I've include a spreadsheet spread sheet which 'should' help illustrate the problem showing the ranges I would like and how the data changes each week.

any help would be much appreciated!