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.)
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
where a1="J2" and b1="J400". Copying this and pasting value gives
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.