Computer Asset Management

**Neil.Taylor** · 04-30-2013, 06:05 AM

*Forum Newbie*

Hi,

I'm wondering if I could ask for assistance due to my lack of excel knowledge that I have?

Working with Excel 2007, I have what is an asset spreadsheet, which lists all the computers the company, I'm working for just now, has as an Asset register. Each computer listed in the spreadsheet, starts from column A, listing the various details about each pc or laptop through to column AV. I have about 250 entries in total.

I'm trying to tidy up the physical cd's and licence documents that each pc or laptop came with. My idea is to print directly onto an A4 envelope, some of the relevant information pulled from the Asset sheet. The idea is if someone needs a licence key or media for a certain pc or laptop, then they pull out the correct envelope and all info they need is either printed on the front or held physically inside the envelope.

I have made an envelope template, which I have also created in Excel 2007, which can be printed directly onto the A4 envelope. One particular column lists the Asset Number, which is sequential in the asset register. Based on this one specific entry, is it possible to build a formula, which If I type in the asset number for a particular pc or laptop to my Envelope Template, it would then pull specific information from the asset register, populate certain cells in the envelope template and then allow me to print an envelope faster, rather than manually entering all the information my self?

Sorry if this is very basic, but its been years since I did my ECDL

Regards

Neil

**AndyLitch** · 04-30-2013, 06:15 AM

I understand what you need but it's difficult to answer the question without knowing the exact layout of your workbook so :-

a) You can upload your workbook or
b) Populate the printed envelope worksheet with formulas using Vlookup, the typed in reference number and the database

**TMS** · 04-30-2013, 06:19 AM

In terms of pulling the specific information, you would, typically, use VLOOKUP.

For example, =IFERROR(VLOOKUP(A2, 'Sheet2'!A2:F250,2,FALSE),"")

That uses cell A2 on the current sheet to search Sheet2, cells A2 to F250, and return matching data from column 2 (B). The FALSE says it needs an exact match.

Wouldn't it be easier to design a template in Word and use Mail Merge to print all the labels?

Regards, TMS

**Neil.Taylor** · 04-30-2013, 07:50 AM

I'm sure I could have done a mail merge, but I find that I understand Excel better, so I tend to default to it.

I'm going to try the VLOOKUP you have posted and see how that works. I'm quickly mocking up an Asset sheet for my testing so I don't touch the live Asset sheet until I'm ready. I will post both the mock up and the envelope cover sheets shortly.

Regards

Neil

**AndyLitch** · 04-30-2013, 08:15 AM

Asset Sheet Edit.xlsx

there you go.. i've put 5 vlookups into the cells i coloured brown.
just select a record in cell a1

bear in mind that the envelope sheet is purely for display and printing... not data entry

**Neil.Taylor** · 04-30-2013, 08:17 AM

Starting in the Envelope Cover Sheet, from a manual entry in B7, of a Host name (NT0100 for example), this would then trigger the various formulas to then check the Asset Sheet, confirm the Hostname is valid and then from relevant selected columns, pull the data to Envelope cover sheet for it to be printed.

**Neil.Taylor** · 05-01-2013, 08:51 AM

Asset Sheet Edit (2).xlsxThanks for all the help

In the end I used both of your ideas to pull this together and tested it fully before I added my changes to the live Asset spreadsheet.

in the end the VLOOKUP is this example: =VLOOKUP($B$6,Computers!$B$2:$AU$5000,43,FALSE)

I have added the Envelope Cover Sheet to the Live Asset spreadsheet, referenced the one cell (B7) in it, which I type the Asset Number of a pc or laptop, then the spreadsheet template I have a qty of 12, VLOOKUP statements which are identical, except for the Column, which is different for each one.

Thinking ahead, I have expanded the range to 5000, so the VLOOKUP statements won't fall out of scope too quickly.

Regards

Neil

**TMS** · 05-01-2013, 09:00 AM

You're welcome. Thanks for the rep.

You could use the Formula | Name Manager to define a Dynamic Named Range for the Asset List. For example:

AssetTable: =Sheet1!$A$2:INDEX(Sheet1!$AU:$AU,COUNTA(Sheet1!$A:$A))

You can then refer to that in your VLOOKUP. So, rather than:

=VLOOKUP($B$6,Computers!$B$2:$AU$5000,43,FALSE)

You could have:

=VLOOKUP($B$6,AssetTable,43,FALSE)

Which is shorter, neater, more efficient and will never require maintenance ... even if you ever did have more than 5000 assets.

Regards, TMS

Computer Asset Management

LinkBack

Thread Tools

Rate This Thread

Display

Computer Asset Management

Re: Computer Asset Management

Re: Computer Asset Management

Re: Computer Asset Management

Re: Computer Asset Management

Re: Computer Asset Management

Re: Computer Asset Management

Re: Computer Asset Management

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions