Dynamically reference into a structured reference column header in a table

[AaronDP]

Hi

Hopefully someone can help here as I have drawn a complete blank!

I have a table with dates as headers. I want to sumif rows where the sum_range is dynamically referenced.

So this formula works...

=SUMIF(Rev_WK12[[#All],[ITEM]],A8,Rev_WK12[[#All],[30/03/2013]])

table name = Rev_WK12
A8 = Reference to what I am looking for.
[ITEM] = column header title

But I want to dynamically reference the column with the date in it. So I tried...

=SUMIF(Rev_WK12[[#All],[ITEM]],A8,INDIRECT("Rev_WK12[[#All],["&N8&"]]"))

N8 = contains the Date Text. The same as the Table column name.

I have also tried removing the INDIRECT...

=SUMIF(Rev_WK12[[#All],[ITEM]],A8,"Rev_WK12[[#All],["&N8&"]]")

All ideas gratefully received

Working with Excel 2012 on Win 7

Thanks

Aaron

 

[circledchicken]

Hi,

You almost got it with your INDIRECT version - just needed to format your date correctly.

Perhaps try one of the following alternatives:

Without INDIRECT:

=SUMIF(
     Rev_WK12[ITEM],
     A8,
     INDEX(Rev_WK12, 0, MATCH(TEXT(N8, "dd/mm/yyyy"), Rev_WK12[#Headers], 0)))

Using INDIRECT:

=SUMIF(
     Rev_WK12[ITEM],
     A8,
     INDIRECT("Rev_WK12[" & TEXT(N8, "dd/mm/yyyy") & "]"))

Note:

• I think version with INDIRECT should be avoided as INDIRECT is volatile - see: Volatile Excel Functions -Decision Models
• Some good resources for learning about structured table references are:
  • Using structured references with Excel tables - Excel - Office.com
  • Working with Tables in Excel 2013, 2010 and 2007

 

[AaronDP]

That's great. Its taken me hour to work through it and apply it! I have taken your advice and used the INDEX & MATCH approch.

Thanks

 

[circledchicken]

You're welcome - thanks for the feedback!