[SOLVED] Defined Names - Offset Function

ssdsibes · #1 06-21-2009, 08:55 PM

Hi
I need some help to fix up my offset formula in my defined name range.

I have used the following formula in my Spreadsheet for the ROW i need to define.

=OFFSET(Table!$C$2,0,0,1,COUNTA(Table!$2:$2)-1)

trouble is, that this formula seems to also marquee the cell to the right of it, which is blank.

Any suggestions?

shg · #2 06-21-2009, 10:29 PM

It would help if we knew what you were trying to do and the data layout, no?

Chance2 · #3 06-21-2009, 10:36 PM

I think the issue is you have your height and width mixed...

=OFFSET(Table!$C$2,0,0,COUNTA(Table!$2:$2)-1,1)

Your original formula had a height of 1 and width of COUNTA.

=OFFSET(Table!$C$2,0,0,1,COUNTA(Table!$2:$2)-1)

ssdsibes · #4 06-21-2009, 10:41 PM

sorry about not being clear
I have a spreadsheet with Dates along Row2 and corresponding data below in Row3.
I want my graph to dynamically update when new data is keyed into the next blank Row3. I know how to setup my Named ranges, but i'm having trouble with the offset formula.

ssdsibes · #5 06-22-2009, 12:55 AM

Hi Chance2

thanks for that, but i do want the referencing to be wide not high. I need the formula to go across the rows, not down the columns.

if i use this formula it goes down the column of C

Code:

=OFFSET(Table!$C$2,0,0,COUNTA(Table!$2:$2)-1,1)

DonkeyOte · #6 06-22-2009, 03:34 AM

Using COUNTA etc is open to error if you have blanks interspersed in a range - also it is important to know what resides in A2:B2 ...

IMO often better to use INDEX with MATCH... if we assume values in row 3 are numeric, that the first cell (C2) contains a number then you can use the following as your RefersTo formula:

=Table!$C$2:INDEX(Table!$2:$2,MATCH(9.99999999999999E+307,$2:$2))

ssdsibes · #7 06-22-2009, 06:24 AM

that works perfectly, now i have to learn and understand INDEX and MATCH.
can you explain this a little for me, or point me to a good doco?

DonkeyOte · #8 06-22-2009, 06:46 AM

In this example we're using INDEX in "Reference Form" as it's documented in the help files, ie using INDEX to return a Range as opposed to a Value (Array Form).

Re: the use of MATCH etc - I would suggest having a read through of Bob Phillips' article: http://www.xldynamic.com/source/xld.LastValue.html

ssdsibes · #9 07-02-2009, 07:36 PM

Hi DonkeyOte
I have had a play with Index and match, but now i'm struggling with this scenerio.

If i wanted to use INDEX & MATCH in a column situation, that contained blanks, but was determined by the fact that there is data in the column directly to the left of it, how would this work

My column that has the defined range is D, starting at Row 4. I need the defined range to search on Column D, that currently has 20 entries in the spreadsheet, but only 1 entry in ColD, but there is a potential for ColD to grow and include entries. See attached example

=NewDifficult!$C$4:INDEX(NewDifficult!$C:$C,MATCH(9.99999999999999E+307,$C:$C))

DonkeyOte · #10 07-03-2009, 02:19 AM

I can't really tie out your attachment to your narrative - ie everything seems to start from row 3 rather than 4 etc...

In short you can use another cell to determine end position in another column by altering the INDEX / MATCH range references, eg:

=$D$3:INDEX($D:$D,MATCH(REPT("Z",255),$C:$C))

would create a range referring to D where end position is determined by last text entry in C.

ssdsibes · #11 07-05-2009, 05:19 AM

that worked, thank you!
sorry about the confusion.

Welcome to the Excel Forum

Forum Statistics