Can Excel find a row and column using cell values as a reference?

Question

I am using Excel 2010.

I have columns like

Start_Sum End_Sum ------ 1999 --2000 -- 2001 --- 2002 --- 2003 --- 2004 --- 2005

2000 ------ 2003 ------------- 200 --- 140 -- 160 ----- 368 ---- 892 ----- 213 ----- 637

Total SUM :- 1560

The above shown is example of my excel sheet. Now as the start and(2000) end date(2003) is edited by users I need to recalculate the Total value(1560). So to sum I need to know the Position like =SUM(E15:H15)

Now is there any way that with the header name like 2001 I can find which column and row its is Eg like 2001 is in F14 Is there any Excel function available to do this?

Sorry for spacing purpose I have use --- lines. I was not able to format this question properly. Thanks in advance :)

Dimitri Dimitri · Accepted Answer · 2014-05-29T10:51:06

If you mean to do it by formula you can use the following:

=SUM(INDIRECT("R2C"&MATCH(A2;1:1;0)&":R2C"&MATCH(B2;1:1;0);FALSE))

enter image description here

With VBA you can do it like this:

Option Explicit

Sub MySum()

Dim wb As Workbook
Dim sh As Worksheet
Dim rn As Range
Dim cl1 As Range
Dim cl2 As Range
Dim sFind1 As String
Dim sFind2 As String
Dim sRes As String

sFind1 = "2000"
sFind2 = "2003"

Set wb = ThisWorkbook
Set sh = wb.Sheets("Sheet1")
Set rn = sh.Range("1:1")
Set cl1 = rn.Find(sFind1)
Set cl2 = rn.Find(sFind2)

sRes = Application.WorksheetFunction.Sum(sh.Range(cl1.Offset(1, 0), cl2.Offset(1, 0)))

sh.Range("B5").Value = sRes

End Sub

Can Excel find a row and column using cell values as a reference?

2 Answers