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

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

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

Motorola Moto G6 review: Hands-on

The best smart speaker: Apple HomePod vs Google Home vs Amazon Echo

Les meilleurs jeux gratuits pour Mac (2018)