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
2	purchase	=INDIRECT(ADDRESS(ROW(D8),COLUMN(D8)))	C2	D2
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("$", MID(ADDRESS(8,4),2,LEN(ADDRESS(8,4))-1))-1))	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.

=sum(sheet1!A1,Sheet2!b5,sheet3!C10)

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

=sum(sheet1:sheet3!C10)

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