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

  madwab 11:57 15 Aug 03
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 03

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 03

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 03

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 03

Welcome back, VOG - good hols?

  VoG II 00:21 30 Aug 03

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

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

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

The Pantone Colour of the Year 2017 is Green

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…