Click to transfer item from Excel to Word

Hello Wizards.

I'm an extreme novice on excel and I'm hoping there's an easy fix for my problem.

Essentially my company has a database of all the components we need to manufacture a range of products. What we would like to do is make this database in to a kind of shopping list where we can simply click certain components and they will be automatically added to another document or workbook like a "shopping list". However the idea would be to have all the components selected added to one new page so we would have a list of all the components needed for that product.

I know there are easier ways to go about this (such as having a database for each product or simply copy and pasting into another doc.) but many of the people who would have access to this database are not very computer savvy (as well as being uber lazy) and the main issue is that our products are bespoke so some components may feature in 50 different end products.

Hope I've explained that OK and any advice would be greatly appreciated.
Many Thanks
P.Titan

Hi Project T

Couple of questions. Is the list from which you want to generate your "shopping list" static? From what I have read, it seems that you have a list of (for example) 100 components. You then want to select, say 30 of these to produce your list? Is the list then to print (or export) so someone can pick this up and work with it, almost as a picking list?

Thanks macropod. I did suggest the filter option to the uber's but apparently that's too difficult...Have no idea where to start on Access but I'll give it a bash.

TexasKersh-Yes that's exactly what we want it for. Firstly for the purchasers to buy the components and then for the engineers to assemble. So of the 100 components I want to be able to click 30 of them and they will automatically be put into another spreadsheet or similar. I did try the watchlist utility but it wasn't very useful for this.

Thanks again.

OK. Can you give me an idea of how many engineers you have and how many of these productions you will complete? If this is a small number then should be OK to do, but if you are talking large volumes, then macropod is right, it might not be the best solution to try and do this on a spreadsheet.

Project T

This is not pretty at all and is only as an example of what I think you are trying to do. The sheet shoplistselcetion is where you pick from the dropdown if a component is needed for an engineer. Joebloggspicklist is where the list is populated based on yes or no. Blank would also not pick from the list.

If this is the type of thing, then dependent on the above question about size etc, then you could do something similar.

Picker List.xlsx

TexasKersh....This is perfect!!! Thanks so much! I'm trying to expand it a little to allow a quantity selection (some of the jobs have numerous repeat components), I've set it up to allow this but I can't change the cell drop down option. They are currently yes or no and ideally I would like them to have numeric values. Can you tell me how to change this?

Thanks again.
ProjectT

Picker List.xlsxSure. The reason I have used a dropdown list like this is that it controls the contents of the cell exactly for searching purposes. (i.e. it will not let on of your Uber's enter there own version of a spelling or the such like ) I have hidden the cells but in the enclosed sheet, I have unhidden them to show you how.

I would usually put my drop down list data in a separate sheet so I can maintain it, but you can store the list contents anywhere really. create your list in cells. In the example shown, L4 and L5 for Yes and No. I have also added L6 and L7 as examples.

Select the cells you wish to enter data in by highlighting them. (in this example, Cells B4 thru B13) and click on the data menu. There should be a data validation button on the top menu bar. Once clicked you will need to select List in the allow box, and the source should refer to your selections. In this case it is =$L$4:$L$7. This now includes the two new items. You can make this list as long as you like (going back to our previous post about 100 components.)

Once you have mastered editing and selection of the drop down boxes, you can get more creative. You could have just a list of engineers and then select from the drop down box the items they need In the attached sheet I have created a third sheet called engineer selection where we can do it the other way round. I.e. have the list of components in the drop down and the engineers in the top. Have a play with them see what you think.

In my experience (and not going to claim I am even a patch on some of the "pointy heads" (Big compliment guys) on here) it is always better to think of your solution, have a play like this and then sit back down again with a blank piece of paper to create the best solution for what you need. Many times I have spent hours on a sheet and then realised if only I had thought a little deeper before jumping in, it would have been so much easier and simpler and not wasted huge amounts of time.



Make Sense ?

Yeh that's perfect thanks Texas. It's exactly what I needed.

I've been able to include- Quantity, cost per component, cost for total components included in each job (ie. if there's 10No. Comp 6) and a lot more.

Please accept a 'pointy head' point from me (I have no idea what I'm talking about here ha ha).

Cheers!
ProjectT

No problem Project T. Hope it works well for you and saves some time. Feel free to shout if you get more questions.

If you consider the post closed, please can you click on the Thread Tool link above and select SOLVED.
If you are having a good day and like the above, please feel free to click on the little "Add reputation" star at the bottom left of the post.