Variable Vlookup across workbooks

[cowproduct]

Hi, I have a bunch of workbooks which I am pulling data on to 1 page. I have attached a very simple example of what I'm trying to achieve.

Where I have Cats and Dogs in Melbourne and Sydney on the 'Total' workbook, the other workbooks have the data I want to take from. However, you'll notice they are on different rows. The data I have is kind of everywhere, but they both have a heading of "Pets". But then the data is on different rows, some where the data is a few rows below the heading.

How can I get this to look for "Pets" and then vlookup for the cells below it?

Thanks!

[Moo the Dog]

See the attached modified sheet and the VLOOKUP formulas where your ?'s used to be.

I created two named ranges:
1. Syd_Pets (Sydney!A5:B6)
2. Mel_Pets (Melbourne!A9:B11)

Hope this helps!

- Moo

[cowproduct]

Yeah, I thought of that too, but the problem is, when I pull the report again next month, the data may not stay in the same area. So I need it to find the heading in the sheet, and then look at the data in the cell table below.

I might just need to doctor it each month, but I wont be doing it usually, so I need it to be fool proof.

[FDibbins]

try this, copied down and across

=SUMIF(INDIRECT(B$1&"!A:B"),$A2,INDIRECT(B$1&"!B:B"))

[Moo the Dog]

See my attached REVISED worksheet... added two cells as helpers in B6 and C6 of the totals page. Works like a charm.

- Moo

[Moo the Dog]

try this, copied down and across
=SUMIF(INDIRECT(B$1&"!A:B"),$A2,INDIRECT(B$1&"!B:B"))

FD - I got the wrong totals when I tried your formula. I think it is adding the totals for Cats and Dogs from ABOVE the 'Pets' section on the Melbourne sheet plus the 'Pets' section, instead of only from the 'Pets' section.

- Moo

[FDibbins]

yes it's adding all the dogs and cats, i though thats what the OP wanted. guess we will have to wait til they are online again

[cowproduct]

Moo, that works pretty well, thanks. The only issue I could foresee is that the data range goes to cell 100. I was wondering if we could make it go from the first one it finds plus 10 or 15. Just because it might be referencing too much data then. If you saw my actual spread sheet, some of the names occur late with different data. And some of the names don't show up in the first instance.

The data I have from the supplier is a mess!

Other than that, it is working great. I'm just worried about the potential issue...

[Moo the Dog]

You could put this in B6:

Please Login or Register  to view this content.

and this in C6:

Please Login or Register  to view this content.

This setup will work if the data always falls within 15 rows of the word "Pets". Otherwise, you should probably consider VBA code. You can change the total # of rows being included in the range by reducing or increasing the very last number in each formula (15).

- Moo

[cowproduct]

Excellent! That is perfect for what I need. Thank you very much Moo! You deserve a promotion!

[Moo the Dog]

Excellent! That is perfect for what I need. Thank you very much Moo! You deserve a promotion!

You're welcome! And I hope that includes doubling my paycheck too... Let's see, using Excel to figure my new salary:

Please Login or Register  to view this content.

I'm rich!

- Moo