PICKEDCOLUMN: Reuse the selected column

PICKEDCOLUMN() refers to the column chosen by MAX, MIN or another supported selection function earlier in the same formula. Use it wherever you need that column reference, for example to read its date or colour its cell.

The selection is calculated separately for each student. It is not the column highlighted on screen.

1. Find each student’s highest test grade

Place three test columns directly inside a Tests folder. Use the same score scale for all three and put the new formula column outside the folder.

Start with:

MAX(FOLDER(Tests))

MAX returns 9 for Alex, 8 for Sam and 7 for Taylor. It also remembers the column that supplied that result: Test 2, Test 1 and Test 2 respectively. On a tie, the first matching column in the supplied order is selected.

Tests is a folder reference inserted with the column selector, not a name to type as plain text.

2. Colour the selected test cell

Keep the maximum as the numeric result and apply a colour to the column that supplied it:

MAX(FOLDER(Tests))
  + COLORCELL(Green, PICKEDCOLUMN())

Insert Green with the colour picker opened by COLORCELL; it is a readable placeholder for the selected colour item. Do not type Green as plain text or replace it with a guessed colour code.

Read the formula as: Find this student’s highest test grade, then colour the cell in the column that supplied it green.

• Alex’s Test 2 cell becomes green; the formula displays 9.
• Sam’s Test 1 cell becomes green; the formula displays 8.
• Taylor’s Test 2 cell becomes green; the formula displays 7.

The target is one cell for the current student, not the whole column. Without a target, COLORCELL(Green) colours the formula’s own result cell instead.

If a later edit makes a different test the highest, the formula colours the newly selected cell. It does not automatically clear a colour previously applied to the old cell.

3. Read the selected column’s date

Suppose Test 1 is dated 5 October 2026, Test 2 is dated 12 October and Test 3 is dated 19 October. Use COLDATE with PICKEDCOLUMN as its column argument:

MAX(FOLDER(Tests))
  + COLDATE(PICKEDCOLUMN(), 2)

Alex’s result is 9 12 Oct 2026. PICKEDCOLUMN supplies Test 2’s column reference; COLDATE reads its date and adds it as text. Sam’s result is 8 5 Oct 2026.

For COLDATE, the shorter MAX(FOLDER(Tests)) + COLDATE(2) has the same effect. The explicit form shows how PICKEDCOLUMN can take the place of a directly inserted column.

4. Combine the number, date and colour

MAX(FOLDER(Tests))
  + COLDATE(PICKEDCOLUMN(), 2)
  + COLORCELL(Green, PICKEDCOLUMN())

For Alex, this displays 9 12 Oct 2026 in the formula column and colours Alex’s Test 2 cell green. The date is appended to existing printed text; it does not change the numeric grade. COLDATE and COLORCELL do not change the remembered selection.

To work with the lowest test grade, replace MAX with MIN. For Alex, the selected column then becomes Test 1, with grade 6.

Things to remember

• PICKEDCOLUMN takes no arguments. Use PICKEDCOLUMN().
• Select first, reuse afterwards. Keep MAX or MIN before the part that uses PICKEDCOLUMN. If several selection functions run, it refers to the most recently completed selection.
• It is a column reference. It does not print a column’s name or ID. In a numeric expression, it reads the student’s value from that column, just as a directly inserted column would.
• It needs a selection in the same calculation. Used on its own, PICKEDCOLUMN() has no previous selection and supplies no value. Nothing is carried over from another student or another formula cell.
• No selection means no target. COLORCELL with an unresolved PICKEDCOLUMN does not fall back to colouring the formula cell.
• Use a supported selector. MAX, MIN, COLUMN_RECENT, COLUMNRESULT_MAX, COLUMNRESULT_RECENT, TIMEMAX and TIMEMIN remember a selected column. SUM and averages do not make a new single-column selection.
• There must be one source column. A winning literal number or a combined expression such as Test 1 + Test 2 has no single column to return.

Related guides: COLDATE: Show a column’s date, COLORCELL: Colour a formula result and FOLDER: Use a folder in a formula.