Find All Assets Assigned To Individual

**Spencer** · 11-02-2007, 09:04 PM

I have a large asset spreadsheet which lists all the assets involved in my current project. These assets consist of computers, cameras, gps, flash drives, etc. Each asset has its own row which shows the make, model, serial number, etc. The rest of the columns for each asset show who that asset was assigned to for that week. We are constantly having to switch around who has what and Excel has helped tremendously to record all that.

Each individual gets assigned anywhere from 1 to 10 assets each week. I can easily find all the assets assigned to an individual for any given week by using the autofilter feature. What I would like to do though is have some sort of form where I could enter the individuals name, and the week which I want records of, and have the form automatically populated. This form would then be printed out and I could have the individual sign the document acknowledging receipt of all the items listed.

This form or "query" would have to search down a particular column (the week I am interested in) and find each instance of that individuals name (a variable number anywhere from 1 to 10). For each instance it would have to find the make, model, and serial number of that asset and populate that data into the form.

All the asset data is on sheet 1 and I would like to have this form on sheet 2.

Please let me know if there is any further explanation I can give you to help get me pointed in the right direction.

Thanks, Spence

**mikerickson** · 11-02-2007, 09:30 PM

As I understand it, your column headers are (more or less) like:

AssetName, AssetDescription, WhoWeek1, WhoWeek2, ...

You could use AutoFilter on Sheet 1, set the filter for "WhoWeekN" to the person in question and print out only the left side (AssetName & description) to have them sign.

If you want a macro to do that, posting a redacted workbook would be helpful.

**Spencer** · 11-02-2007, 09:54 PM

The column descriptions are Make, Model, Type, Tag, Serial Number, Password, Email Address, Assigned To Week Of ..., Assigned To Week Of..., Assigned To Week Of..., etc.

The problem that I have just using the Autofilter method is that the Make, Model, and Serial Number columns are not right next to each other so even if I select just those three columns using the CTRL key they will print on separate pages (or at least the non-contiguous Serial Number data).

The Autofilter method will work, although I would have to move some columns around which are in that order for a reason. I was just hoping that I could create a nice looking form which would populate itself just by entering the persons name and week of. I am holding each individual responsible for the safe return of the equipment and I would like it to look professional as well have a blurb on there about their liability.

**Bjornar** · 11-03-2007, 04:14 AM

If the autofilter option is OK for you to use, but your problem is that you have columns that you don't want to print, you could make a "mirror sheet" with only the columns you want and use this for printing and filtering.

If your data is in Sheet1 and the colums you want in your print is A and E, and your "Assigned to week of.." columns start at column H you put the following formulas in Sheet2

Cell A1: =Sheet1!A1
Cell B1: =Sheet1!E1
Cell C1: =Sheet1!H1

Copy cell C1 for as many colums you need in Column D..E..F.....
Copy row 1 down for as may rows as you need to get the whole table.

Now you can use Autofilter on this Sheet and set the print range to print column A and B. On this sheet you can also do some more formatting etc, to make the print look professional.

Find All Assets Assigned To Individual

LinkBack

Thread Tools

Rate This Thread

Display

Find All Assets Assigned To Individual

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions