Vlookup and sum across tabs??

[erniepoe]

Hi All,

Have a problem and am wondering if there is an easy solution.

On Sheet 1 I have a column listing store numbers, there are about 250 numbers. In the same workbook are also 250 sheets corresponding to each store, but the tab is the store name, not the store number.

The store number is in cell C3 on every tab.

I am wondering if there is a way to match the store number from the Summary Sheet to have it vlookup across all tabs in cell c4, and when it finds the match, to sum up row 53 from columns F to O.

I'm hoping there is a formulaic solution and I wont have to do this manually, but such is life I suppose.

A million schrute bucks to anybody who can help?

Much obliged and happy new year y,all!

 

[Fluff]

One option would be to use a UDF

Function SumShts(StoreNum As String) As Double
   
   Dim Ws As Worksheet

   For Each Ws In Worksheets
      If Not Ws.Name = "[COLOR=#ff0000]Sheet1[/COLOR]" Then
         If Ws.Range("C3").Value = StoreNum Then
            SumShts = WorksheetFunction.sum(Ws.Range("F53:O53"))
            Exit For
         End If
      End If
   Next Ws

End Function

This needs to go in a standard module (change the sheet name in red to suit) & then in your sheet enter the formula as shown below

Excel 2013 32 bit
	A	B
1	Store Number	Totals
2	10	41609.07
3	11	216657
4	12	0
5	13	0
Sheet1

Cell Formulas
Range	Formula
B2	=SumShts(A2)

 

[erniepoe]

this is amazing, thank you so much Fluff! Much appreciated, and have a great week!

 

[Fluff]

Glad to help & thanks for the feedback