Formula to copy info selected from a database

Microsoft Excel 2010
January 7, 2011 at 01:29:52
Specs: Windows 7

I have a small business and I am currently upgrading the excel worksheets that I get my employees to fill out when they have completed a job out on site.

On 'sheet 1' I have basic info such as the employees name, type of job completed, hours worked on job, etc. I also have a table that lists all the materials that I have used. It lists the code, description, cost price, etc. (the entire database of materials is listed in 'sheet 2')

Is there a formula or macro that will allow for someone to select/tick a particular material item in 'sheet 2' and then this item appears in the materials table in 'sheet 1'? Then when I select another material from 'sheet 2' it will appear in the table in 'sheet 1' bellow the previous item?

By doing this it will allow for only the materials used in the job to appear in the first sheet and this can be used as a summary sheet for the job.

Any help will be greatly appreciated!

See More: Formula to copy info selected from a database

January 7, 2011 at 06:25:42
May I suggest that you go in the "opposite direction"?

Instead of pushing the data from Sheet2 into Sheet1 (which will require a macro) why not pull the data using Data Validation Drop Down lists?

This will save the user from having to go back and forth from Sheet1 to Sheet2, asking themselves "Do I already have that material?", etc.

In addition, if something needs to be changed, the user could simply choose something else from the Drop Down in any given cell and replace one item with another with no need to manually delete the original item. If you used code to add items to the bottom of the list, then anything above it that was deleted would leave a blank row and the sheets could get very messy.

Once the "material" had been pulled into Sheet1 via the drop downs, VLOOKUP could be used to pull all of the associated data into other columns in Sheet1.

You could protect and hide Sheet2 and the users would never have to access it.

Taking it one step farther, I have a workbook that I use to put together the lineup for the Softball teams I coach. It allows you to choose a player for each position in each inning via drop downs. The nice thing about it is that once you select a player in an inning, that player's name is removed from the other drop downs for that inning so that you can not accidently use the same player at 2 different positions in the same inning. If you change your mind and delete a player from a position in an inning, that name will become available in all drop downs so you can place her elsewhere.

I don't know how extensive your materials list is, but this method would prevent to same material from being chosen more than once.

If you are interested is seeing this workbook, send me an email address via Private Message and I'll send it to you. Do not post your email address in this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question