Monday, August 15, 2016 – Permalink –
Display Row, Column Headings
Here's an odd little use of functions.
If you want to display the Row number on a spreadsheet, the formula
=Row()works just fine.
You could then hide the Row and Column headings and format the Row numbers any way you want. If a Row is deleted the numbers will automatically update.
Column headings are a little harder. The formula =Column() will show the number of the Column, not the letter, i.e. "2" instead of "B".
The following formula extracts the Column letter:
To break it down:
This finds the address at Row number "1" and current Column number. The abs_num of "4 " says make the result a relative address.
The formula will produce a result such as "AA1".
This function looks at the address, i.e. "AA1".
It replaces the Row number character ("1") with a null or empty value ("").
The formula will produce a result such as "AA".
Also see Daily Dose of Excel by Dick Kusleika.
Of course Office 2007+ has taken it up to 16,284 columns.
See all Topics excel
<Doug Klippert@ 3:27 AM
Comments: Post a Comment