Results 1 to 15 of 15
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Formula to extract data based on Asset number

    I have a workbook called Fixed asset sample.2. I would like a formula or macro to extract the net book value from workbook FA sample data.2 based on the asset number on Col C. The formula or macro to extract the net book value are to be in Col K in the destination workbook.

    For eg if asset 7183 is in Col C , I want to extract the Net book value of this asset from the source workbook based on the asset number and the value to be extracted in this example is 129117.69

    The destination workbook has been converterd from PDF into Excel using a PDF converter so Net Book Value Col s not in the same on all the sheets in the destination workbook-see attached sample

    Your assisting in creating a formula or macro to look up/extract the value will be most appreciated

    http://www.excelfox.com/forum/f2/loo...le-shhets-978/
    Attached Files Attached Files
    Last edited by HowardC; 2013-05-18 at 13:30.

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Does this do what you want on Page 1 (then, similar on the others):

    =VLOOKUP(A7,'[Fixed Asset Sample.2-1.xlsm]Fassets'!$C$1:$K$9,9,FALSE)

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks for the reply. My Apologies, Only when you replied, I checked the workbook Fixed asset sample.2 and it was the same as FA sample data.2

    I uploaded the correct file now. It would be appreciated if you would assist. The Net book value for each sheet on Fixed asset sample.2 is not on the same column, thus making the Vlookup difficult

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I'm not sure what workbook you're now referencing. I think the VLOOKUP works as you indicated.

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    What I need to do is to look up the net Book value in the source workbook (FA sample data.2) based on the asset number in Col C of the destination workbook Fixed Asset Sample.2-2 and place the result in Col K Fixed Asset Sample.2-2

    Regards

    Howard

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Howard

    You can use an INDEX(MATCH) formula to find and retrieve the required values.
    If the asset number doesn't exist in the source data file, the retrieved value is #N/A.
    (This could be set to blank if required).

    See attached workbook.
    The source workbook [FA Sample Data.2.xlsx] needs to be open with my attached file which contains the INDEX(MATCH) formulas in column [K]

    zeddy
    Attached Files Attached Files

  7. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for the help. The difficulty is that this file was in PDF and whhen using a PDF converter, there are several sheets and the Net Book value is not in the same column. It would be appreciated if you would write VBA code to extraxt the NET book values based on the asset number

    Regards

    Howard

  8. #8
    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
    You should be able to use a similar formula for each sheet. Just change the column for each sheet to the appropriate columns. VB would not be required.
    Steve

  9. #9
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    Thanks for the reply. The easiest way to resolve this would be to write code to do the following on workbook FA Sample Data.2

    1) Check sheets2 onwards (sheets names as pages i.e Page 2 onwards) and where the columns extend to Col m, then to delete Cel d
    2) Copy the data from sheet2 onwards page 2 onwards) to Sheet1 after the last row of data (Page 1 after the last row of data)

    It would be appreciated if you could assis me in doung this as it would then enable me to set up one an Index & match formula to look up the data on sheet1 (Page 1) only

    Your assistance in this regard is most appreciated

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Howard

    I have a file which will process all your sheets and give you the results you want.
    I just need to do some final cleanup and then I'll post it.
    I am out for a while so I will post it later today.

    zeddy

  11. #11
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for the reply. You can post the results/code when you have the time. There is no urgent rush

    Regards

    Howard

  12. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Howard

    Looking at your sample file (copy attached), it seems the best way forward to me is to process each sheet in the source Book Value Report file so that the data is in consistent columns on each sheet.

    This includes separating the data elements that have the combined value of 'purchase date' and 'StraightLine depreciation%' into two components rather than the 'combined' value.

    We can then import all of these data records from each of the sheets in the Book Value Report into an import sheet in our Assets Workbook.
    This means that our INDEX(MATCH) formulas can now refer to a specific [import] sheet to simplify everything.

    1. copy my attached file into the same folder that you have your Book Value Report file.
    2. open my file.
    3. If you have enabled macros, the Tool title will animate on opening.
    4. Click the button to [Select Book Value data file]
    5. The file browser will open to allow you to select a file e.g. [FA Sample Data.2.xlsx]
    6. Click the button [Import Book Values]
    7. After import, select the tabsheet [Fassets] and review formulas in column [K]

    If you have any questions, please ask.

    zeddy
    Attached Files Attached Files

  13. #13
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for all your effort. This will help me a great deal

    I have attached a full sample of the data to be imported and all the data from this workbook is not being imported.

    It would be appreciated if tou could tweak this so that it imports all the data

    Regards

    Howard
    Attached Files Attached Files

  14. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Howard

    There are a couple of reasons why data was not 'imported'.
    1. some of the rows were hidden on the import sheet.
    2. Your new sample 3 has sheets which sometimes have **TOTALS as the last entries on some sheets (eg Page3), rather than an asset record being the last entry (e.g. Page1,Page2 etc).

    The attached new version v1b now takes this into account.
    The new versioin .xlsb file is 212Kb in size.
    I would recommend you open it in Excel and then save it in .xlsb format, which would reduce the file size to 99Kb.
    Smaller file sizes load and save quicker.
    ( .xlsb files cannot be posted directly here though - can someone fix that????)

    Please let me know if this does what you want.

    zeddy
    Attached Files Attached Files

  15. The Following User Says Thank You to zeddy For This Useful Post:

    HowardC (2013-05-20)

  16. #15
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for all the trouble. You are a star. Code now works 100%

    Regards

    Howard

Posting Permissions

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