Get a certain value based on table header and row name

Question

Lets say my Excel worksheet looks like this:

Column A          | Column B
------            | ------
Table header1     |  
Some row name     |  1000
Some row name     |  2000
Total             |  3000
                  |
Table header2     |  
Some row name     |  2000
Some row name     |  2000
Total             |  4000

This worksheet comes from an external source and the position of the rows is not always exactly the same. I would like to have the value of column B where column A is Total and where it's table header is Table header2.

Normally I would just write some static code like; look for Table header2 and sum x rows until I reach the desired value. In this case the amount of rows between the table header and the Total row is dynamic so that doesn't help me out.

Do you guys have any ideas on how to fix this?

Answer 1

Just as a late answer, the following worked for me quite well. It will give you the ability to tailor your search based on the first occurrence of a title, followed by the name of the subtotal value you're looking for...

'An arbitrary number to limit the number of times we iterate through the 
'loop checking cells - we don't want to go too far...
Const gapSize As Integer = 10

'GetSectionSubtotal
'   Gets a value, as an implied subtotal, from a list of values after a 
'   particular title occurs in the list.
'Usage (direct function entry into a cell):
'   =GetSectionSubtotal(A1:B30, "Title 2", "Total")
'Parameters:
'   rng (Range) - A range definition containing the data you wish to test.
'   secTitle (String) - A string containing the title (or first occurrence)
'   of the section you wish to test for.
'   totTitle (String) - A string containing the name of the cell you wish to
'   return a value for after the first occurrence of secTitle.
'Notes:
'   This function returns the *first* value associated to totTitle after the
'   first occurrence of secTitle. If you have mulitple occurrences of the 
'   section title then this code will need to be revised.
'   The gapSize allows the function to quit gracefully if the process 
'   experiences a series of empty cells.
Public Function GetSectionSubtotal( _
  rng As Range, _
  secTitle As String, _
  totTitle As String) As Double
    Dim r As Integer
    Dim rv As Double
    Dim go As Boolean
    Dim fail As Integer
    Dim found As Boolean
    r = 1
    go = True
    fail = 0
    Do While go
        'Determine if we've found our title section...
        found = found Or rng.Cells(r, 1) = secTitle
        'We only want to continue when the title is either found or we've 
        'passed the end of the list...
        go = (((found And Not rng.Cells(r, 1) = totTitle) _
            Or (Not found)) And fail < gapSize)
        'If we're not going anymore then the answer has been found or we've 
        'exceeded the end of the block within the range...
        If found And Not go Then rv = rng.Cells(r, 2)
        'Increase fail based on the value of the first column. This 
        'assignment will zero fail again if a value is found...
        fail = ((fail + 1) And rng.Cells(r, 1) = "")
        'Increment our row counter...
        r = r + 1
    Loop
    GetSectionSubtotal = rv
End Function

Answer 2

UPDATE

single formula to return the value

=INDEX(A1:B1000,MATCH("Total",OFFSET(A1:A1000,MATCH("Table Header 2",A1:A1000,0),0),0)+MATCH("Table Header 2",A1:A1000,0),2)

The formula works by finding "Table Header 2" row and then using offset to push the range to find Total below that Table Header and therefore find the correct Total row.

Previous answer that requires a formula to be autofilled down column C

if your columns start on row 2 then you can use this formula and autofill down to return 4000 next to the Total under Table Header 2.

=IF(A2="Table Header 2",TRUE,IF(C1=TRUE,IF(A2="Total",B2,TRUE)))

You could replace the text with a reference e.g. $F$2

=IF(A2=$F$2,TRUE,IF(C1=TRUE,IF(A2="Total",B2,TRUE)))

And then put a sum in cell G2 to return the value of the header specified in F2

=sum(B2:B1000)

Answer 3

A simple sub like

Sub GetTotalValue2()
    Dim RefRow As Integer
    Dim CellContent As String
    Dim Total As Long
    RefRow = 0
    'Find header row:
    Do
        RefRow = RefRow + 1
        CellContent = ThisWorkbook.Sheets("Sheet1").Cells(RefRow, 1).text
    Loop Until CellContent = "Table header2"
    'Find total row:
    Do
        RefRow = RefRow + 1
        CellContent = ThisWorkbook.Sheets("Sheet1").Cells(RefRow, 1).text
    Loop Until CellContent = "Total"
    'Return value to Total variable:
    Total = ThisWorkbook.Sheets("Sheet1").Cells(RefRow, 2).Value  
End Sub

will solve your problem, I think.

Please note: a) you have to change "sheet1" by the whorksheet name you're using; b) if your table doesn't have a cell with a "Table header2" text content or a "Total" text content next to the header, this code will produce an error. If that's the case we could do an error handler to resolve the issue.