Sum across multiple sheets with 2 way look up

[bdom2369]

I am looking for a way to populate a summary table. I export multiple sources of data, which all have the same information but arranged in a different way. The constant would be the date that I keep on the far left. So what I am looking for is a way to use the date then look up the column header, go across all the sheets and total. I am thinking index match match with a sum product formula. Maybe have to use an indirect. Or vlookup with date, match row 1 for header, then sum across all the sheets. I can't get it to work out yet.

Here is some sample data in a google drive:
https://drive.google.com/folderview?id=0B9YXUQxu5Av0ODg4eXoyUnZmNXM&usp=sharing

Thanks,
Brandon

 

[XOR LX]

@Ingolf

Ah! I didn't even consider that the OP might be on a different system.

I hope you're right, as that would explain a lot.

Many thanks.

Regards

 

[bdom2369]

No luck, it will just error out, still stuck

 

[XOR LX]

Are you sure that you've committed the formula as an array formula?

What's the precise current formula you are using? Have you updated your shared workbook with your latest attempt?

Regards

 

[Ingolf]

No luck, it will just error out, still stuck

For last time. it is array formula.
You don't folow:

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

just copy in B2 this formula:

=SUM(IF(T(OFFSET(INDIRECT("'"&Sheets&"'!A1");;{1\2\3\4}))=B$1;SUMIF(INDIRECT("'"&Sheets&"'!A:A");$A2;OFFSET(INDIRECT("'"&Sheets&"'!A:A");;{1\2\3\4}))))

 

[Ingolf]

@XOR LX

Your formula work in my workbook.

OP not use CTRL+SHIFT+ENTER.

 

[bdom2369]

Thanks guys, I know its an array formula, it still isn't working for me. My excel will say "formula contains error" on this version and I can't even get to evaluate formula to test it. The previous version would "kind of work". But not on the whole table. Also none of it would work with an actual data sample due to it not be dynamic enough. I have tried many variations and this and is not going the direction I am looking for.

I am now am working on a sum product (so I don't have to do array), with a vlookup for the date, then index for the data range of sheets, with a match for column header. I am almost there, but I still don't have it yet.

 

[XOR LX]

Why can't you be more specific? What does "kind of work" mean? You have previously posted a link to a shared workbook, so you could quite easily use that as a reference for any discussions.

And, for the third time, I have already mentioned that I would be more than happy to amend my set-up to account for any number of columns. Though of course it would be useful if you could manage to get the static version working first.

Regards