# Use the results of an Excel formula in another formula as a cell indexer

I have a basic formula:

``````=(RANDBETWEEN(1,20))
``````

which gives me a value, what I want to do is take that value (for the sake of argument '12') and use it to get another cell via another formula, i.e.

``````=(RANDBETWEEN(1, B<result>))
``````

where `B` is the column, `<result>` is the result of the first formula.

Can this be done? (I'm using Excel 2013)

-

Try using INDEX, so if you have the `=RANDBETWEEN(1,20)` formula in cell D1 you can use this formula in another cell

`=RANDBETWEEN(1,INDEX(B:B,D1))`

If the first formula returns 12 then the INDEX part will return the value from B12

If you don't want the first formula in a cell you can use it directly in the second formula like this:

`=RANDBETWEEN(1,INDEX(B:B,RANDBETWEEN(1,20)))`

-
Perfect, I wanted to avoid macros if possible and this does just what I wanted... – Chris Skardon Jan 6 '13 at 18:36

You can make an Excel macro to solve this issue.

• Click on the "View" tab in the Ribbon.
• Click on "Macros."
• Type `RandomCell`, then click "Create."

When the VBA window opens, paste this code directly under `Sub RandomCell`:

``````Dim Low As Double
Dim High As Double
Dim R As Double
Low = 1
High = 20
R = Int((High - Low + 1) * Rnd() + Low)
ActiveCell.Offset(R, 1).Select
``````

If you are in cell A1, this will select cell B[random number]. Save the workbook and close the VBA window. Run the macro by doing the following:

• Click on the "View" tab in the Ribbon.
• Click on "Macros."
• Select "RandomCell" and click "Run."
-