CODEWITHSUNDEEP
excelexcel-formula
athos
athos

Reputation: 6413

How to calculate the position of a range defined by "offset", without VBA?

Suppose I have a calculation in cell A3 based on Offset:

 = STDEV(OFFSET(H30,1-M2,0,M2,1))

since using of offset will not make the range visible when I click on the cell, I'd like to add a couple of checkings to ensure the calculation is alway correct.

Now, how could I show the top row, left column, row count and column count of the range defined by this OFFSET(H30,1-M2,0,M2,1)?

can this be done using Excel formula only, without adopting VBA?

Upvotes: 2

Views: 694

Answers (2)

barry houdini
barry houdini

Reputation: 46361

Assuming M2 contains a positive number won't the range always end at H30? (or if M2 is negative range will start at H32). You could get the full address with this formula

=CELL("address",OFFSET(H30,1-MAX(M2,SIGN(M2)),0))&":"&CELL("address",OFFSET(H30,1-MIN(M2,SIGN(M2)),0))

Upvotes: 3

Tim Williams
Tim Williams

Reputation: 166401

Full address:

=ADDRESS(ROW(OFFSET(H30,1-M2,0,M2,1)),COLUMN(OFFSET(H30,1-M2,0,M2,1))) & ":" &
 ADDRESS(ROW(OFFSET(H30,1-M2,0,M2,1))+ROWS(OFFSET(H30,1-M2,0,M2,1))-1,
 COLUMN(OFFSET(H30,1-M2,0,M2,1))+COLUMNS(OFFSET(H30,1-M2,0,M2,1))-1)

Credit to Richard Schollar: http://www.mrexcel.com/forum/excel-questions/506397-formula-show-full-address-named-range-without-visual-basic-applications.html

Upvotes: 3

Related Questions