Database? problem

[redfour]

I am the secretary of a local gardening club. Each year the members of the club (over 200) order their seed from a catalogue containing approximately 1250 different seeds. It is a mammoth task that last year took me over 2 weeks to complete.

Since last year I have created a database in Excel which contains the records of the seeds, including reference number, description and cost of each packet of seed. I then use a template to enter each member’s individual order, using VLOOKUP. This has already saved me a great deal of time.

However, I still have to manually go through each order and count how many packets of each seed has been ordered.

Is there any way that Excel can use the data entered for each member’s order to automatically collate all the orders together to produce the bulk order?

If anyone can point me in the right direction I would be extremely grateful.

The only task left then is the sorting of the 4,000+ packets of seed delivered into each member’s order. :x

 

[PaddyD]

Hi - welcome to the board!

Something can probably be done here, but you'll need to give us more info - sheet names, data structures, a detailed, worked example etc. As it stands, the problem description is too general.

Consider posting up a relevant snapshot of your sheet (see html maker faq thread at the top of the board for how)

 

[erik.van.geit]

Hi, redfour
Welcome to the Board !!!!!
seems doable
but it would be good if you posted some "real-life-data"
therefore use Colo's HTML Maker utility for displaying your Excel Worksheet on the board

On the "Excel Questions" main page there is a link to HTML Maker FAQ. You'll find out how to post correctly.
On the main page of the Forum there is a link to a Test here forum and a link to About this Board - to ask questions about HTLMMaker ...

kind regards,
Erik

 

[Yogi Anand]

I am the secretary of a local gardening club. Each year the members of the club (over 200) order their seed from a catalogue containing approximately 1250 different seeds. It is a mammoth task that last year took me over 2 weeks to complete.

Since last year I have created a database in Excel which contains the records of the seeds, including reference number, description and cost of each packet of seed. I then use a template to enter each member’s individual order, using VLOOKUP. This has already saved me a great deal of time.

However, I still have to manually go through each order and count how many packets of each seed has been ordered.

Is there any way that Excel can use the data entered for each member’s order to automatically collate all the orders together to produce the bulk order?

If anyone can point me in the right direction I would be extremely grateful.

The only task left then is the sorting of the 4,000+ packets of seed delivered into each member’s order. :x

Hi redfour:

Welcome to MrExcel Board!

If you have set it up as a DataBase, then

1) you can use DCount/DCounta and/or DSum functions to count or sum the packages by recipient to suit your needs

2) for collating, how about sorting the data on the number of related fields

3) you may also create list of recipient names along with number of packages, for each box of seeds so that when the boxes are received, you know the names of the people and the number of packages the named recipient gets.

Depending on how your data is set up and your needs, you may also be able to use a PivotTable or DataTable for this task.

Let us know how it goes!

 

[Tazguy37]

Welcome to MrExcel Board!

It sounds like using Data, Subtotals would help, but I agree with Erik and PaddyD: a picture is worth two thousand bytes.