# offset formula

#### ehsas

##### Board Regular
=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,

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### Mark W.

##### MrExcel MVP
See the Excel Help topic for "OFFSET worksheet function". The formula syntax is...

OFFSET(reference,rows,cols,height,width)

#### ehsas

##### Board Regular
could you change the above formula to refer to row please.Regards,

#### Mark W.

##### MrExcel MVP
I could if I knew what you were trying to accomplish. Care to provide some sample data with cell references, and your desired result?
This message was edited by Mark W. on 2002-10-21 17:04

#### ehsas

##### Board Regular
=OFFSET(sheet1!\$A\$6,0,3,1,COUNTA(sheet1!\$D\$6:\$Z\$6)

I manage to make the above formula which refers to the row 6 in my worksheet.The only problem which I don't understand.I want the formula to take last 13 fields of row 6 instead of d6 to z6.Please if any one could help.Regards.

##### MrExcel MVP
On 2002-10-21 17:44, ehsas wrote:
=OFFSET(sheet1!\$A\$6,0,3,1,COUNTA(sheet1!\$D\$6:\$Z\$6)

I manage to make the above formula which refers to the row 6 in my worksheet.The only problem which I don't understand.I want the formula to take last 13 fields of row 6 instead of d6 to z6.Please if any one could help.Regards.

What is the current exact range that must be named and what kind of data (numeric/text) the range houses?

#### ehsas

##### Board Regular
it starting from d6 till ak6. which i want to take last 13 months.It is month like jan'01,feb'01 and so on.Please if some one could help me.Regards,

#### ehsas

##### Board Regular
=OFFSET(sheet1!\$A\$6,0,3,1,COUNTA(sheet1!\$6:\$6))

The above formula is selecting the row 6 but it not selecting the last 13 cells of row 6 which has data.Please help.Regards,

##### MrExcel MVP
On 2002-10-22 07:52, ehsas wrote:
it starting from d6 till ak6. which i want to take last 13 months.It is month like jan'01,feb'01 and so on.Please if some one could help me.Regards,

Let the data of interest be in Sheet1.
I assume in what follows that D6:AK6 houses numeric data and you want to define a name covering last 13 values in D6:AK6.

( 1.) Activate Insert|Name|Define.
( 2.) Enter BigNum as name in the Names in Workbook box.
( 3.) Enter the following in the Refers to box:

9.99999999999999E+307

( 5.) Enter Drange (or anything more intelligible) in the Names in Workbook box.
( 6.) Enter the following formula in the Refers to box:

=OFFSET(Sheet1!\$D\$6,0,MATCH(BigNum,Sheet1!\$D\$6:\$AK\$6)-13,1,13)

( 7.) Click OK.

Now you can use in formulas Drange that refers to last 13 numeric entries in the range of interest.

#### ehsas

##### Board Regular
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,

Replies
7
Views
1K
Replies
3
Views
312
Replies
2
Views
251
Replies
5
Views
369
Replies
3
Views
163

1,181,064
Messages
5,927,910
Members
436,575
Latest member
Tiger750

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back