How to calculate multiple item dropdown list

%3CLINGO-SUB%20id%3D%22lingo-sub-2241606%22%20slang%3D%22en-US%22%3EHow%20to%20calculate%20multiple%20item%20dropdown%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2241606%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20monthly%20invoice%20list%20of%20purchases.%3C%2FP%3E%3CUL%3E%3CLI%3EI%20have%20a%20multiple%20item%20dropdown%20list%20in%20an%20abbreviated%20form.%20(Menu%20%26amp%3B%20Price%20List)%3C%2FLI%3E%3CLI%3EI%20need%20to%20have%20the%20short%20item%20name%20equal%20to%20an%20amount.%3C%2FLI%3E%3CLI%3EThen%20in%20the%20ledger%20sheet%20add%20all%20the%20items%20in%20the%20abbreviated%20dropdown%20list%20into%20a%20total.%3C%2FLI%3E%3C%2FUL%3E%3CP%3EI%20cannot%20convert%20the%20abbreviated%20item%20name%20to%20a%20number%2C%20and%20cannot%20get%20the%20multiple%20items%20in%20the%20dropdown%20list%20add%20to%20a%20total.%3C%2FP%3E%3CP%3EPlease%20help%2C%20I%20have%20been%20stuck%20on%20this%20issue%20for%20ages.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2241606%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2242093%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20calculate%20multiple%20item%20dropdown%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2242093%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1011098%22%20target%3D%22_blank%22%3E%40Handiman%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnlock%20file%20I%20did%20not%20understand%20exactly%20what%20was%20looking%20for%2C%20but%20here%20is%20a%20small%20suggestion%20for%20a%20solution.%20of%20what%20I%20think%20I%20understand%20%3A))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help%20with%20this%20information.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I have a monthly invoice list of purchases.

  • I have a multiple item dropdown list in an abbreviated form. (Menu & Price List)
  • I need to have the short item name equal to an amount.
  • Then in the ledger sheet add all the items in the abbreviated dropdown list into a total.

I cannot convert the abbreviated item name to a number, and cannot get the multiple items in the dropdown list add to a total.

Please help, I have been stuck on this issue for ages.

2 Replies

@Handiman 

Unlock file I did not understand exactly what was looking for, but here is a small suggestion for a solution. of what I think I understand :))

 

 

I would be happy to know if I could help with this information.

 

Nikolino

I know I don't know anything (Socrates)

@Handiman 

Here is a custom function you can use:

Function Total(Items As String, PriceList As Range) As Double
    Dim a() As String
    Dim i As Long
    Dim t As Double
    a = Split(Items, ", ")
    For i = LBound(a) To UBound(a)
        t = t + Application.VLookup(a(i), PriceList, 2, False)
    Next i
    Total = t
End Function

Use like this in C4:

=Total(B4,'Menu & Price List'!$B$2:$C$18)

Fill down.

See the attached version.