# Thread: Formula to extract data based on Asset number

1. ## 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/

2. 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. 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. I'm not sure what workbook you're now referencing. I think the VLOOKUP works as you indicated.

5. 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. 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

7. 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. 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. 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. 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. 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. 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]

zeddy

13. 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

14. 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

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

HowardC (2013-05-20)

16. 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
•