Home Page Computers Software Microsoft Windows Excel FrontPage PowerPoint Outlook Word Host your Web site with PureHost! |
Saturday, December 12, 2009 – Permalink – Display Row, Column HeadingsUser FunctionHere'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: =SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","") To break it down: =ADDRESS(row_num,column_num,abs_num) 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". SUBSTITUTE(text,old_text,new_text) 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. Dick mused:'
Of course Office 2007-10 has taken it up to 16,284 columns. [Edited entry from 12/10/2006] See all Topics Labels: Excel <Doug Klippert@ 3:08 AM |