Excel: how do I use a 'h/c' cell ref in a formula?

  madwab 11:57 15 Aug 2003
Locked

I have 2 cells that contain 'J2' and 'J400' (produced by the 'address' function), which are references to cells. I can access the contents of those cells using the 'indirect' funtion. But how do I access the RANGE, J2:J400, in a formula? I.e., I want to get the result of median(xa:yb) where xa=J2 and yb=J400.

(Why not just type in the formula? Because I have a large table of such references.)

Thanks

  Mango Grummit 14:33 15 Aug 2003

I know next to nothing about Excel and our top Excel man (one Mr VoG) is about to go on holiday so I'll ^bump^ this for you and let's hope he looks in before he leaves.

Sub GetMedian()
Dim Myrange As Range

Set Myrange = Range("J2:J4000")
x = WorksheetFunction.Median(Myrange)
End Sub

HTH

Sub GetMedian()

Dim Myrange As Range


Set Myrange = Range("J2:J4000")

x = WorksheetFunction.Median(Myrange)

End Sub

  madwab 14:25 20 Aug 2003

I'm not particular familiar with visual basic, but your solution has j2 and j400 coded, which isn't what I need. Suppose call A1 contains text value "J2". The solution I need has to refer to cell J2 indirectly, via the contents of cell A1.

In fact I got around the problem a few minutes after posting it, using the solution below, which might better explain what I needed. I coded

="=median("&a1&":"&b1&")"

where a1="J2" and b1="J400". Copying this and pasting value gives

=median(j2:j400)

then F2 and Enter in each cell turns the formula into a value. Not ideal but acceptable for the amount of data I've got. I left the topic open because I'm sure there's a better way - preferably not using visual basic.

M

  VoG II 00:12 30 Aug 2003

I see that this is ticked - presumably you discovered that you could use e.g.

=MEDIAN(INDIRECT(C1):INDIRECT(C2))

assuming that C1 contains J2 and C2 contains J400

  jazzypop 00:16 30 Aug 2003

Welcome back, VOG - good hols?

  VoG II 00:21 30 Aug 2003

Excellent thanks; just planning the next one to recover ;o)

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Best Black Friday Deals 2017

How modern book design was influenced by illustrated manuscripts

Best Black Friday Apple Deals 2017

Les meilleurs logiciels de montage vidéo gratuits (en 2017)