offset formula

[ehsas]

=OFFSET(sheet1!$A$11,COUNTA(sheet1!$A:$A)-sheet1!$E$1,0,sheet1!$E$1,1)

The above formul refers to column.How I can changed it to refers to the row instead of column.Regards,

 

[Aladin Akyurek]

On 2002-10-22 10:23, ehsas wrote:
I tried the above but it is not working.Let me tell you that I wan to use the offset formula for dynamic chart in my chart.Please help.Regards,

This "but it is not working" doesn't tell me much.

See for an example of creating dynamic charts...

http://www.mrexcel.com/board/viewtopic.php?topic=7435&forum=2

 

[ehsas]

=OFFSET(sheet1!$A$6,0,3,1,COUNTA(sheet1!$6:$6))

Thank aladin.Only I want to add some more function in above formula which only take last 13 cell from.Please tell if it possible.Regards,

 

[Aladin Akyurek]

On 2002-10-22 10:41, ehsas wrote:
=OFFSET(sheet1!$A$6,0,3,1,COUNTA(sheet1!$6:$6))

Thank aladin.Only I want to add some more function in above formula which only take last 13 cell from.Please tell if it possible.Regards,

In fact, I already done that using the range D6:AK6... Below is the equivalent when you use A6 and row 6...

=OFFSET(Sheet1!$A$6,0,MATCH(BigNum,Sheet1!$6:$6)-13+CELL("Col",Sheet1!$A$6)-1,1,13)

 

[ehsas]

Thanks.It taking las 13 field on the row I want to check for last field where is filled with. and then take the last 13 fields.Regards.

 

[ehsas]

Aladin thanks.Let me tell you that I have changed the match to counta and it worked.Regards,

 

[Aladin Akyurek]

On 2002-10-22 17:11, ehsas wrote:
Aladin thanks.Let me tell you that I have changed the match to counta and it worked.Regards,

My "war" against COUNTA in defining named ranges is probably unknown to you. When you have an empty cell, COUNTA will not give you the correct range, MATCH always will.

 

[ehsas]

but match it not giving me the last 13 fields it looking through all the row 6 till end.

 

[Aladin Akyurek]

On 2002-10-22 17:46, ehsas wrote:
but match it not giving me the last 13 fields it looking through all the row 6 till end.

See...

Book6
	A	B	C	D	E	F	G	H
1	Last	4
2	7	#VALUE!	Selecttheformulaontheformulabar&F9				{0,50,80,90}
3	5	40	Selecttheformulaontheformulabar&F9				{40,0,0,50}
4
5
6	30	40			50	80	90
7
Sheet1

The above example shows the last 4 values in row 6 instead of 13.