Solved: Help with building quote

[lifeson]

It has taken me ages to even write down what I am after but here goes:
I have a worksheet with 4 sheets:

1: tblQuoteLine
2: tblPackMat
3: tblPrices
4: QuoteDetail

tblQuoteLine:
This contains a list of entities that have been ordered plus the type and qty of each entity ordered

There are 2 types of entity:
Type 1 is a component
Type 2 is a pack

Column Headers are:
EntityID
EntityTypeID
Qty

tblPackMat:
This contains a list of materials that are contained within a pack
A pack (type 2) can have any number of materials linked to it.
A component (type 1) will not have any materials linked to it.
Column headers are:
PackID
MatID
Qty

tblNWAC:
This contains a list of the prices and descriptions for each of the entities
Column headers are:
EntityID
Description
NWAC (price)


These tables cannot change as they are imported from an external source.


What I am trying to achieve is:
Create a new sheet (QuoteDetail) listing the total quantity and prices of all Materials and components ordered for a quote

So from tblQuoteLine
For each entity:
If the entity is type 1 (a component) then
With sheets(?QuoteDetail?)
Check if the component has already been added then
If not then:
Create a new line with the entityID, lookup the entityDescription and unit price from tblPrice, add the qty from tblQuoteline, calculate a subtotal

If the entity has already been added then:
On the same line where the entity already exists, add the new qty to the existing qty
Recalculate the subtotal cost

If the entity is type 2 (a pack) then

With sheets(?tblPackMat?)
Check if the pack has any materials linked to it
With sheets(?tblQuoteDetail?)
If the entity has materials linked to it, then
Create a new line with the entityID, lookup the entityDescription and unit price from tblPrice, add the qty from tblQuoteline, calculate a subtotal

If the entity has already been added then:
On the same line where the entity already exists, add the new qty to the existing qty

[Trevor]

In reading your post it looks , and in my opinion, what you are trying to do is create a Database using an excel work workbook(a series of worksheetis in excel) .
my recomendation would be to make a Database using either MS Access or SQL, but if you would like to keep Excel then what it lotks like is you just want to some quantites where The ID or order ID number is the same
for the mathmatical operation of the cell you want to calculate try =Sum(nz[Worksheet]![cell number]) if you are calulatiog more then 1 field for that cel then the expresion would look like =sum([Worksheet]![cell number] *[worksheet]![Cell number]

FYI:

* = multiply
+ = add
- = subtract
/ = devide
Matmatical operations orer= perenthises, exponets, multiply, Devide, Add, subtract

[lifeson]

Thanks for the reply Trevor
I didnt actually mean to post the question and I don't know how it did get posted (Kids, I'm lokking at you:motz2: )

You are right though it is trying to use excel like a database, which I have been advised against before but unfortunately the company I work for do not allow general users to use ms access so excel is the only option.

The project is to try and create a margin analyser for our sales team.
They use a laptop which has 2 drives:

On the C drive there is a ms database which contains all the genric details - list of components, packs, selling prices, links etc
On the D drive there is a local database that contains all the information particular to that salesman eg customer details, leads, quotes, visit details etc. and is also linked to the c drive database

Using excel (which everyone has access to) I have managed to create a file that extracts:
all the leads for a salesman,
then when he selects a lead, it shows all the quotes available
then when he selects a quote it shows all the details for that quote inc items ordered, sellling price etc

From the list of items ordered for the quote I need to be able to find the cost price of all the items.

I will start a new thread with where I am stuck
This one is too wordy for anyone to read :bug: :bug: :bug:

[lifeson]

double post :bug: :bug: :bug:

[Bob Phillips]

Don't use the word database, use dataset, and all silly philosophical arguments go away. Excel works with data, so you have to have the data somewhere. Often that is better in a structured data container with data integrity checking et al (a database - did someone say Access?), but most often Excel does the job well and best.

[Trevor]

Lifesone, thanks for your reply to my reply, good enough reason to use access, although it blows my mind that a company would make a rule like that only because access is present on your pc if you have word and or excel installed,
never the less leave it an orgonization to make a rule/policy that just seems to defy logic :-p