Creating A Database And Using The Data For Project Quotes

[shearstrength]

Using Excel 2000;

I would like to create a "Master" database of components (example; bolts, nuts, washers, description, unit price, etc.) and use this data in new worksheets to provide clients a complete project quote, without having to constantly retype the information over and over.

What would be the best approach?

Any response will be greatly appreciated!

 

[QualityEngr]

shearstrength:

Do you want to be able to enter the name of a component and have your program lookup its unit price? other information?
Or are you just looking for a dropdown menu type solution, so you can select from the menu without typing, or mis-typing, the discription of the component?
Or both?

 

[johnwm]

You can directly refer to another workbook using this type of formula in a cell:

[tt]
='C:\[fred.xls]Sheet1'!A1
[/tt]


Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

 

[Cooky]

It depends how much time & effort you are prepared to commit.

I had a similar task to create a quotation system based on information that was in a large Excel spreadsheet. I wrote a macro that was linked to a button, this macro opened a Microsoft Word template and copied information from my Excel spreadsheet (similar arrangement to yours). The template document was a quotation, which I could then add customer details, print-out and save for future reference.

You'll need to learn about Visual Basic to create the macro (I'm sure you'll get some help here!)

Good luck

Cooky

 

[shearstrength]

The idea that I had in mind was to open a template file, select a component from a list and the balance of the information would automatically be filled in other fields. I understand that this can get rather lengthy and complicated. Selecting a component from a drop down menu would be nice. The other idea that I had is to create a database in Access and import the information into Excel. Which program for this type of application?

 

[FredGarvin]

To tell you the truth, your project is right up Visual Basic's alley with it's interaction with databases. However, the question still remains how much time do you want to put into this? To write your own front end will definitely take some time with the learning curve. It's not too bad though (coming from someone who recently has gone through it).

You could, most likely, use Access' report creation features to create the quote for you based on your DB selections. Is there any reason why the process has to include Excel? If you already have Access, I'd s[end some time with it. You'll find it will do what you need.

 

[yakpol]

Shearstrength,
In Excel you can reference databases across the workbooks, and you don't need VBA to do it.

Lets your database file called "BoltDB.xls" has a table of data with the first column named "BoltID" (Insert-Name-Define or Create), and a second column "Area". Lets name the workbook you want to retrieve data to "Pick_a_bolt.xls". In the cell A2 place one of the BoltID's in B2 type formula:
=INDEX('C:\boltproject\BoltDB.xls'!Area,MATCH(A2,'C:\boltproject\BoltDB.xls'!BoltID,0))

It should give you the area of the bolt. Try, it works!

 

[MintJulep]

Shearstrength,

I think you answered your question about Excel or Access with your original question.

You want to create a database. Doesn't it then make sense to use a database program to do it?

Yes, it could be done with Excel either with or without using VB but I think you will find that Access offers more flexibility.

Access would also allow future migration to SQL server, allowing much better multi-user features.

 

[schwarz]

Shearstrength,
For about 2500 parts I use a EXCEL sheet to store all master data with unique number ( item id ) for each part.

For new costing or quote sheet I just need to enter item id in one cell and other cells which contain VLOOKUP formula get required details e.g. description, order code., price immediately. The process can be repeated for any no. of rows.

To retain only values copy the cells and paste as values at same place.

For fancy front end or very large no. of data required, VB can be used.

 

[QualityEngr]

shearstrength,

I think I know for what you are looking, if, in fact, you are still looking!

You can use a combobox on your Quotesheet to present a list of parts on your Master:
Input Range: specify the Master column in which your components are listed.
Cell Link: specify a working cell, that will not (necesarily) appear on your quote. For the purpose of explanation, lets assume you use cell A99.

Now where-ever you want fields on your Quotesheet to "link" to your Master (column B) enter a formula in the format of:
=Indirect("Master!B"&A99+1)
I am assuming your first row on your Master sheet has one header row.
For column C information, this looks like:
=Indirect("Master!C"&A99+1)
Etc., etc.

Please let me know if I am on the right track.

Thank you.

 

[shearstrength]

I would like to be able to open an excel template with predefined cells that match a master worksheet. The predefined cells would be for example "Item Name", "Item Description", "Unit Price", etc. When I open the template, which will be the worksheet that will be used to quote my clients. I would like to be able to choose an "ITEM" from the master list and automatically insert the information pertaining to that "ITEM" chosen, along with the predefined information in the predefined cells. As I enter these "ITEMS" the worksheet will automatically calculate the quotation price from the predefined unit price.

I use Excel from time to time, but never used it at the advanced level. The feedback that I've been getting is great, but I would need "The How To" to be able to set up the worksheets from the suggestions given.

 

[QualityEngr]

Shearstrength,

Not knowing what your experience level is, it would take too long for me to explain the entire process in its simpliest terms. However, we can try a few steps at a time, and see where it leads us. You should run with it as far as you can on your own, but advise if you run into difficulties, or when you are ready for more instruction. Others reading this can offer their own improvements on technique, and we can all benefit. We can hope, however, that we do not get too much chatter. Fair enough?

If you have not already done so, I suggest you start with the construction of your Master List. You do not need to complete it, but enter at least three or four items for try-outs. We can decide later whether we want to make it a separate workbook. For now just name one worksheet something like "Quote", and another "Master"

If your items have unique part or item numbers that could be easily used for look ups by the user, then disregard the following. (I am assuming the user will be familiar with only the Item Name.)

After entering a few items in the Master, go to the Quote worksheet. Click on View > Toolbars and verify that the Forms box is checked. On the Forms toolbar, click the icon for Combo Box, and then click and drag on your spreadsheet to make your box. Don't worry about the size or placement yet; you will fix that later. Right click on the box, select "Format Control", and tehn the "Control" tab. Click on the button at the far right of "Input range", and then select your Item Name column on the Master sheet (presumably column A). Excel will fill "Master!$A:$A" into the little Format Control box. Click on button at the far right of this box, which will get you back to Quote sheet. Follow the same procedure for for "Cell ink", except instead of a whole column on Master, you will select a single cell in an out-of-the-way place on Quote. For now lets make it A99. This cell will contain the row number when you make your Item selection.

Please advise if you want to continue... If yes, please also advise all your column headings on your Master sheet.

Thank you.

 

[shearstrength]

QualityEngr;

I tried the exercise mentioned above, and I notice that if my heading is called "CATEGORY" for example jam nut, for every jam nut that is inserted under this heading, that's how many jam nuts will appear in my combo-box. "Too cumbersome"

I would like to have a split window with a tree structure. So if I'm selecting a "BOLT", the tree expands to provide me a list of bolts, that has been inserted, filtering the rest of the components.

 

[QualityEngr]

shearstrength,

I did some searching on the web for the means to accomplish what you want. So far, I have found numerous requests for help for this very same problem, but no solutions. I am sure it can be done, but I am not sure yet how it can be done SIMPLY.

As an alternative, I don't suppose it would it be practical for your operators to use a unique part number to look up items?

Please advise all your headings on your Master List, so we can plan accordingly.

Thank you.

 

[QualityEngr]

shearstrength,

We can still use a combobox from the Control Toolbox (vs. Forms) that can include multiple columns. I am not entirely satisfied yet with the aesthetics, but since this is new territory for me, I am optimistic that we can make it work for you.

I am willing to walk you through the experiment, but it would be helpful to get more info. from you regarding the overall specifications of your project. So I am asking, again, what are your headings on the Master list? New questions: Approximately how many records do you anticipate? How many items, maximum, do you anticipate having on a single quote?

Thanks,
Elliot

 

[HNGRR]

You can write a letter in Excel, but not a book because it`s not a word processor.
Why try to manage resources stored in same kind of database whit Excel if you have Access or any other software?

Regards

 

[BillPSU]

HNGRR

Right On!!

Excel data can be tied together between multiple workbooks, sheets and databases and even your mainframe using Access and also create custom reports pulling information from all sources at the same time, however there is one problem, people need to learn Access which to the ,here and now, people means too much work.

 

[bpeirson]

I agree with BillPSU about learning Access.

In Excel I have a master template which refers to 3 other independant workbooks through the VLOOKUP command.

The pricing database has in excess of 5000 line items with 7 relevant data items each.

The material database has in excess of 2000 line items with the number of relevent data items ranging from 1 to 10 with an upper limit set only by the number of columns in the worksheet.

I was looking for a software package which could handle my data more easily and tried Access. I had no time to learn the intricacies of the Access so I stayed with Excel.

I later learned some VBA code with the help of Eng-tips members to fill in the features which Excel did not offer.

IMPORTANT NOTES:

1. If you use external workbooks for your database your template will link to update values in those other workbooks every time you open it. I found that the update time for my large database workbooks took up to 2 minutes to update.

My computer would freeze while the update took place.

To stop this from happening I open the database files before opening the templates which access them, then the update is almost instant.

2. I found that if my lookup table got to be too large (more than 8 columns wide on my 5000 line woorkbook) then the VLOOKUP command would not have sufficient memory to work reliably.

I have since incorporated VBA code to look up information in other workbooks which enabled me to create the material database with the potential for 65536 line items with 255 columns of information.

 

[pipewelder1999]

Forgive me if I repeat some information already given. A relational database is ideal for this type of task. A parts spreadsheet can easily be imported into access, checked for duplicate data, related to a quote table, and reported in numerous ways. I will post a link to a sample soon if you are interested.

Have a nice day

Gerald Austin
Iuka, Mississippi

http://www.weldinginspectionsvcs.com

 

[shearstrength]

Hi pipewelder1999,

Yes, I am interested. Please send me a link.

Thanks