Forum Discussion
Solved
Formula
I have been tasked with creating a formula that looks into a big ole list of 317 items on a sheet with multiple columns and 317 rows. These 317 items have to be checked on over the course of 12 mont...
Sheesh.
Once more.
Let's say the units are in Electric!E3:E321.
In Electric!CP3, enter the formula =RAND()
Fill down to Electric!CP321 for example by double-clicking the fill handle of cell Electric!CP3.
On another sheet, for example Sheet1:
Enter Jan in Sheet1!D1, and fill to the right to Sheet1!O1; you should automatically get Feb, Mar, ..., Dec.
In Sheet1!D2, enter the formula
=INDEX(Electric!$E$3:$E$321,RANK(INDEX(Electric!$CP$3:$CP$321,26*(COLUMN(D2)-COLUMN($D$2))+ROW(D2)-ROW($D$2)+1),Electric!$CP$3:$CP$321))
Fill down to row 27, then to the right to column O (or vice versa).
The random sample will be updated each time Excel recalculates. If you want to "fix" a sample, select the range, copy it and paste it elsewhere as values.
Sheesh.
Once more.
Let's say the units are in Electric!E3:E321.
In Electric!CP3, enter the formula =RAND()
Fill down to Electric!CP321 for example by double-clicking the fill handle of cell Electric!CP3.
On another sheet, for example Sheet1:
Enter Jan in Sheet1!D1, and fill to the right to Sheet1!O1; you should automatically get Feb, Mar, ..., Dec.
In Sheet1!D2, enter the formula
=INDEX(Electric!$E$3:$E$321,RANK(INDEX(Electric!$CP$3:$CP$321,26*(COLUMN(D2)-COLUMN($D$2))+ROW(D2)-ROW($D$2)+1),Electric!$CP$3:$CP$321))
Fill down to row 27, then to the right to column O (or vice versa).
The random sample will be updated each time Excel recalculates. If you want to "fix" a sample, select the range, copy it and paste it elsewhere as values.
Ok Great. It pulled them over to sheet1. thanks