offset formula

ehsas

Board Regular
Joined
Sep 17, 2002
Messages
200
=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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
See the Excel Help topic for "OFFSET worksheet function". The formula syntax is...

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

ehsas

Board Regular
Joined
Sep 17, 2002
Messages
200
could you change the above formula to refer to row please.Regards,
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
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
Joined
Sep 17, 2002
Messages
200

ADVERTISEMENT

=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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
Joined
Sep 17, 2002
Messages
200

ADVERTISEMENT

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
Joined
Sep 17, 2002
Messages
200
=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,
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

( 4.) Click Add.
( 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
Joined
Sep 17, 2002
Messages
200
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,
 

Forum statistics

Threads
1,144,214
Messages
5,723,057
Members
422,476
Latest member
beck85

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top