Results 1 to 10 of 10
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    I need to look up various types of Depreciation , where the values are in column 105 (Col DL) and the decriptions are in col L

    I have tried to use the following formula to add all the various depreciation types in Col 105, but cannot get it to add the values


    =IF(A69=(RIGHT(A69,50)),VLOOKUP("*Depreciation*",S heet1!$L$1512:$DL$1783,105,FALSE))

    The various types of depreciation are:

    DEPRECIATION-COMPUTER EQUIP
    DEPRECIATION Corp Signage
    DEPRECIATION-P & M
    DEPRECIATION-F & F
    DEPRECIATION-OE

    your assistance will be most appreciated

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,202
    Thanks
    49
    Thanked 987 Times in 917 Posts
    The VLOOKUP doesn't look right. The first parameter should be the value you are looking up and the 3rd should be a column index into the array of values in the lookup table.
    Also the lookup table should be at least 2 columns, with column 1 containing the lookup values and the 2nd column the value to return.

    I think we need a little more explanation about how you determine what dep. type is appropriate?

    cheers, Paul

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Paul

    Thanks for the reply. Attached please find saple data containing amongst others the various types of depreciation (L11:l15). I allways need to reference the values to Col DL. Each month I new col Is inserted and the data is copied into Col DL

    I need a formula that will addadd the depreciation values.

    Your assistance will be most appreciated
    Attached Files Attached Files

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,202
    Thanks
    49
    Thanked 987 Times in 917 Posts
    I don't have converters for xlsx format, can you post an xls version?

    cheers, Paul

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Vlookup does not work with wildcards. I think SUMIF will work:

    =SUMIF(L1512:L1783,"*Depreciation*",DL1512L1783)

    This will sum the values in Col DL where L contains "Depreciation"

    Steve

  6. #6
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    SUMIF is the way to go.

    VLOOKUP will work with a wild card as the match.
    However, it will still only return a value from the first match value that fits the pattern
    and as such here would be of no use.
    Andrew

  7. #7
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Quote Originally Posted by P T View Post
    I don't have converters for xlsx format, can you post an xls version?

    cheers, Paul
    Hi Paul - Try this link for Microsoft converters .....

    Microsoft Office Compatibility Pack

  8. #8
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,202
    Thanks
    49
    Thanked 987 Times in 917 Posts
    Thanks, Tim. I was being a bit lazy not installing the converters, but Office 2000 still does everything I need.

    cheers, Paul

  9. #9
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hi Howard - Using the sample file that was attached
    Try this (modification of Steve's formula) in Cell B2 on the tab named Depreciation
    =SUMIF(Sheet1!L1:L16,"*depreciation*",Sheet1!DL1L16)
    the answer should be 76,820.25


    Tim

  10. #10
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hi Paul - Here is an .xls version of Howard's file.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •