vlookup across tabs

[J G]

Please reference the attachment.

On the summary tab, B2:E7, I want to write some kind of vlookup formula to look for the value in column A on each of the proceeding tabs and add those values on those 3 tabs together.

Thanks for your help.

 

[Faseeh]

Hi J G

With such similar layouts you can use Consolidation feature of excel. Is't that appropriate for this sheet?

 

[J G]

Unfortunately no, actual data on 34 tabs ranges from 160 to 191 rows. All tabs use the same columns, through O.

I tried combining a vlookup with choose formula but its returning 0. See updated attachment.

=VLOOKUP(A4,CHOOSE(B4,Jan!A1:E7,Feb!A1:E7,Mar!A1:E7)*2,FALSE)

 

[Faseeh]

Can i have the actual file you are working for at-least a chunk of regional data. I think this can be consolidated.

 

[J G]

I appreciate your help but I can't do that. I updated the sample attached with unique values to simulate the actual document better.

 

[Haseeb A]

Hello JG,

Enter your 34 tab names in a range & give them a name 'TabList', then in Summary!B2

=SUMPRODUCT(SUMIF(INDIRECT("'"&TabList&"'!A:A"),$A2,INDIRECT("'"&TabList&"'!R1C"&COLUMN(B1),0)))

Then copy down & across

 

[J G]

This is great, thanks so much. I don't know how you did it. I'm going to have to pick this apart.

 

[J G]

Can you help again? Now my boss wants to be able to change the tab names. Is there a way to link a cell to a tab name?

 

[Smallman]

That is a very good formula Haseeb has come up with. Using it in your file with the following will give you your sheet names dynamically.

=IFERROR(INDEX(MID(Sheets,FIND("]",Sheets)+1,255),ROW(A2),1),"")

Where sheets is a named range

=TRANSPOSE(GET.WORKBOOK(1))&T(NOW())

I have attached a workbook to show workings. The pink areas is Haseeb's tablist table.

Take care

Smallman