Book Home Page Bloglines 1906 CelebrateStadium 2006 OfficeZealot Scobleizer TechRepublic AskWoody SpyJournal Computers Software Microsoft Windows Excel FrontPage PowerPoint Outlook Word Host your Web site with PureHost! 
Monday, August 15, 2016 – 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+ has taken it up to 16,284 columns. See all Topics excel Labels: Functions, General, Reference, Shortcuts, Tips, Tutorials, VBA <Doug Klippert@ 3:27 AM
Comments:
Post a Comment
