|
Host your Web site with PureHost! |
 
Sunday, December 10, 2006 – 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:' "Sometime before the year 3,000, Microsoft will hopefully increase the number of columns in Excel (Hey, I can dream can’t I). The challenge before you is to write a function that converts a column number to its letter equivalent assuming columns go to ZZZZ. That’s about 450,000 columns - maybe more than I need." Of course Office 2007 has taken it up to 16,284 columns. [Edited entry from 5/31/2004]
See all Topics <Doug Klippert@ 4:59 AM |
