Vlookup alternative or if function

[username_rand]

I have data in multiple excel sheets in the same workbook. All sheets have the same table but different data as each sheet represents a certain date. The name of shops in the table are chosen from a dropdown list and you can see the full list of shops to the right of the photo. What i want to do is to have a new sheet that has the names of shops and the sum of receipts next to each shop, so i'm looking for a formula that would copy and sum the receipt value from each sheet only if the name of the shop is found in the sheets. I know i could easily do it through vlookup but it would need some preparation and changes to the table format and arrangement so it would be my last resort. The below is only a sample of the data i have. Is there any formula that would help me get this done?

 

[mumps]

Create two new blank sheets. Name one "Shops" and the other "Summary". Remove the list of shop names from their current location in column N and place them in column A of sheet "Shops" starting in cell A1. Change the data validation formula for the drop down lists to refer to this new location of the shop names. Try this macro:

Sub CalculateTotals()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, shop As Range, fnd As Range, sAddr As String, total As Long, desWS As Worksheet
    Set desWS = Sheets("Summary")
    For Each shop In Sheets("Shops").Range("A1", Sheets("Shops").Range("A" & Sheets("Shops").Rows.Count).End(xlUp))
        For Each ws In Sheets
            If ws.Name <> "Shops" And ws.Name <> "Summary" Then
                With ws
                    Set fnd = .UsedRange.Offset(1, 0).Cells.Find(shop, LookIn:=xlValues, lookat:=xlWhole)
                    If Not fnd Is Nothing Then
                        sAddr = fnd.Address
                        Do
                            total = total + fnd.Offset(, 1).Value
                            Set fnd = .UsedRange.Offset(1, 0).Cells.FindNext(shop)
                        Loop While sAddr <> fnd.Address
                        sAddr = ""
                    End If
                End With
            End If
        Next ws
        With desWS
            .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) = shop
            .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0) = total
        End With
        total = 0
    Next shop
    Application.ScreenUpdating = True
End Sub

 

[mumps]

Sorry. Try this revised version:

Sub CalculateTotals()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, shop As Range, fnd As Range, sAddr As String, total As Long, desWS As Worksheet
    Set desWS = Sheets("Summary")
    For Each shop In Sheets("Shops").Range("A1", Sheets("Shops").Range("A" & Sheets("Shops").Rows.Count).End(xlUp))
        For Each ws In Sheets
            If ws.Name <> "Shops" And ws.Name <> "Summary" Then
                With ws
                    Set fnd = .UsedRange.Offset(1, 0).Cells.Find(shop, LookIn:=xlValues, lookat:=xlWhole)
                    If Not fnd Is Nothing Then
                        sAddr = fnd.Address
                        Do
                            total = total + fnd.Offset(, 1).Value
                            Set fnd = .UsedRange.Offset(1, 0).Find(What:=shop, after:=fnd, LookIn:=xlFormulas, lookat:=xlPart, _
                                SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                MatchCase:=False, SearchFormat:=True)
                        Loop While sAddr <> fnd.Address
                        sAddr = ""
                    End If
                End With
            End If
        Next ws
        With desWS
            .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) = shop
            .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0) = total
        End With
        total = 0
    Next shop
    Application.ScreenUpdating = True
End Sub

 

[username_rand]

I did exactly like you said and it does work! But is there a reason why the data is duplicated in the summary sheet? Am i doing something wrong ?

Thanks a lot!

 

[mumps]

You said:

What i want to do is to have a new sheet that has the names of shops and the sum of receipts next to each shop

The new sheet would be "Summary". I tried the macro on some dummy data and it worked properly. How is it not working for you? Do you get an error message and if so, what is the error message and which line of code is highlighted when you click "Debug"? It would be easier to help and test possible solutions if you could attach a copy of your file. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[username_rand]

I did not say it is not working, sorry if you thought so, it does indeed work and gives the total for each shop name, but the thing is that after the macro runs, the Summary sheet shows duplicated names of the shops; as in i can find Shop1 and its corresponding total more than once in the final list. Let me try it again and share what i'm getting with you if it keeps showing duplicated values.

 

[mumps]

Are you running the macro more than once?

 

[username_rand]

It seems so, i ran it three times ! Thank you for being so helpful!! You saved me a lot of time!

 

[mumps]

Try:

Sub CalculateTotals()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, shop As Range, fnd As Range, sAddr As String, total As Long, desWS As Worksheet
    Set desWS = Sheets("Summary")
    For Each shop In Sheets("Shops").Range("A1", Sheets("Shops").Range("A" & Sheets("Shops").Rows.Count).End(xlUp))
        For Each ws In Sheets
            If ws.Name <> "Shops" And ws.Name <> "Summary" Then
                With ws
                    Set fnd = .UsedRange.Offset(1, 0).Cells.Find(shop, LookIn:=xlValues, lookat:=xlWhole)
                    If Not fnd Is Nothing Then
                        sAddr = fnd.Address
                        Do
                            total = total + fnd.Offset(, 1).Value
                            Set fnd = .UsedRange.Offset(1, 0).Find(What:=shop, after:=fnd, LookIn:=xlFormulas, lookat:=xlPart, _
                                SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                MatchCase:=False, SearchFormat:=True)
                        Loop While sAddr <> fnd.Address
                        sAddr = ""
                    End If
                End With
            End If
        Next ws
        With desWS
            .UsedRange.Offset(1, 0).ClearContents
            .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) = shop
            .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0) = total
        End With
        total = 0
    Next shop
    Application.ScreenUpdating = True
End Sub

 

[username_rand]

One last question regarding the solution you provided, is there a way to calculate the sum of shops receipt for each date (sheet) instead of the whole workbook?