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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
See the Excel Help topic for "OFFSET worksheet function". The formula syntax is...

OFFSET(reference,rows,cols,height,width)
 
Upvote 0
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
 
Upvote 0
=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.
 
Upvote 0
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?
 
Upvote 0
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,
 
Upvote 0
=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,
 
Upvote 0
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.
 
Upvote 0
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,
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

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
Back
Top