Miscellaneous Code fragments of Worksheet functions

Location:   http://www.mvps.org/dmcritchie/excel/miscws.htm
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]

Rows & Columns

Most of the following example show obtaining the value located in Column D for the row specified in cell A1.  Another example shows obtaining the column letter for a specified cell.

=GetFormula() was used to show the formulas actually used.  The MarkCells macro was used to place the cell reference numbers into columns C and D as literals for testing.

  A B C D
1 8 8 C1 D1
3 purchase =INDIRECT(ADDRESS(A1,COLUMN(D1))) C3 D3
4 purchase =OFFSET(D1,A1-1,0) C4 D4
5 purchase =INDIRECT("D"&A1) C5 D5
6 purchase =INDIRECT(ADDRESS(A1,COLUMN(D1))) C6 D6
7 purchase =INDEX(D1:D201,A1) C7 D7
8 purchase =INDEX(D:D,A1) C8 purchase
9 8 =CELL("row",D8) C9 D9
10 4 =CELL("col",D8) C10 D10
11 D =MID(A13,2,LEN(FIND("$",MID(A13,2,LEN(A13)-1))-1)) C11 D11
12 D =MID(ADDRESS(8,4),2,LEN(FIND("$",
C12 D12
13 $D$8 =ADDRESS(8,4) C13 D13
14 $D$8 =ADDRESS(ROW(D8),COLUMN(D8)) C14 D14
15 $D$8 =ADDRESS(ROW(D8),COLUMN(D8),1) C15 D15
16 D$8 =ADDRESS(ROW(D8),COLUMN(D8),2) C16 D16
17 D8 =ADDRESS(ROW(D8),COLUMN(D8),4) C17 D17
18 ColA-Head ='[Another Book.xls]Sheet1'!A1 C18 D18
19 Heading-A ='Sheet One'!A1 C19 D19

   =INDIRECT("H"&MATCH("##",H3:H700)+3)     will find first value above ##
   =VLOOKUP("PT9999",H3:H11,1,TRUE)        will find closest value at or below PT9999
Referencing cells on multiple sheets (Tom Ogilvy 1999-12-10).  These are codepages not sheetnames.

if all the values are in the same cell on different sheets


You are one of many distinguished visitors who have visited my site here or in a previous location  since July 17, 1998.

Visit [my Excel home page]   [Index page]   [Excel Onsite Search]   [top of this page]

Please send your comments concerning this web page to: David McRitchie send email comments

Copyright © 1997 - 2004,  F. David McRitchie,  All Rights Reserved