Changing Column Letters in a Formula

[rwmill9716]

=INDEX($F$7:$F$264,RANDBETWEEN(1,ROWS($F$7:$F$264)),1) I use the above formula to randomly sample a data set in Column F. I have data that I would like to sample in other columns. If I were to put one of their letters in Cell A1, could the above formula be modified where the letter in A1 replaces the 'Fs' in the formula?

 

[Cubist]

You can try the cell range address in cell A1 and use INDIRECT to call it.

Book2
	A	B	C	D
1	$D$1:$D$4	B		B
2		A		A
3		C		C
4		D		D
Sheet6

Cell Formulas
Range	Formula
B1:B4	B1	=INDIRECT(A1)
Dynamic array formulas.

 

[rwmill9716]

Thanks, Cubist, I didn't know about this approach.

 

[Peter_SSs]

If I were to put one of their letters in Cell A1, could the above formula be modified where the letter in A1 replaces the 'Fs' in the formula?

I'm not quite sure how that marked solution answers your question.

Also, using ROWS($F$7:$F$264) seems to introduce a pointless calculation as that is simply 258

This is my suggestion (Only part data shown)

24 03 29.xlsm
	A	B	C	D	E	F	G	H
1	H	H24
2
3
4
5
6
7						F7	G7	H7
8						F8	G8	H8
9						F9	G9	H9
10						F10	G10	H10
11						F11	G11	H11
12						F12	G12	H12
13						F13	G13	H13
14						F14	G14	H14
15						F15	G15	H15
16						F16	G16	H16
17						F17	G17	H17
18						F18	G18	H18
19						F19	G19	H19
20						F20	G20	H20
21						F21	G21	H21
22						F22	G22	H22
23						F23	G23	H23
24						F24	G24	H24
25						F25	G25	H25
26						F26	G26	H26
27						F27	G27	H27
28						F28	G28	H28
29						F29	G29	H29
30						F30	G30	H30
rwmill9716

Cell Formulas
Range	Formula
B1	B1	=INDEX(INDIRECT(SUBSTITUTE("$#$7:$#$264","#",A1)),RANDBETWEEN(1,258))