How do I target cells selected by the user from a formula?

[Matu]

Hi Forum,
I'm trying to make an automatic quote using Excel.
Basically, I have an excel file containing 4 sheets:

1st sheet: a vertical list of products with a price next to each
2nd sheet: a vertical list of delivery options with a price next to each
3rd sheet: a vertical list of packaging options with a price next to each

What I'm trying to do is make a 4th sheet that shows the total amount according to what the user selects in each of the 3 previous sheets. E.g. if the user selects "product 5", "delivery option 9", and "package option 3", then the total shown in the 4th sheet would be the sum of the three individual prices.

The problem is, I don't know how to target dynamically selected cells from a formula. Is it possible to do this?

Thank you very much for any help.

 

[BrianB]

Probably best set up using 3 comboboxes to make the selections.
1. Open Forms toolbar & draw a combobox.
2. Right click it and 'Format Control'
3. Click in range box and select range in sheet
4. Set suitable Cell Link range (single cell which gives number of selected item in list)
5. To show which item was selected, in another cell put formula like
=INDEX(Sheet1!A1:A5,A1,1)
assumes Cell Link is cell A1 in current sheet and the Sheet1 range is your list.

There is obviously more that can be done, but this will depend on your setup.

 

[Peter_SSs]

Matu

Welcome to the Mr Excel board!

Possible solution. Assume sheets are called Product, Delivery, Packaging and Quote.

On Product sheet, name the range (single column) containing the list of products 'Product'. Name the range (two columns) containing the product list and the prices 'ProductTable'. Similarly name ranges 'Delivery', 'DeliveryTable', 'Packaging' and 'PackagingTable' on the relevant sheets.

On the Quote sheet set up like shown below with headings in row 1. Apply Data Validation to cell A2. In the 'Allow' box put List and in the 'Source' box put =Product

Do similar Data Validation in cells B2 (=Delivery) and C2 (=Packaging).

In cell D2 put the formula:
=VLOOKUP(A2,ProductTable,2,0)+VLOOKUP(B2,DeliveryTable,2,0)+VLOOKUP(C2,PackagingTable,2,0)

Post back if any of the steps need clarification.

Mr Excel.xls
	A	B	C	D
1	Product	Delivery	Packaging	Quote
2	Product5	Delivery3	Packaging1	115.00
3
Quote

 

[Matu]

Can't believe it!

Half an hour after posting this topic, it had moved to "page 3" within the list and had 0 replies. I lost every hope.

A day later I come back and find my posting in "page infinite", but with 2 different, exellently explained and comprehensive answers that perfectly solve my issue.

<big>Peter and Brian, THANK YOU VERY VERY MUCH!! </big>

Matu.

 

[BrianB]

Glad I could help.

lost every hope.

Please realise the need for a bit of patience. We are all volunteers here, and have to earn our "daily bread" somewhere else. :wink:

This site is excellent for its aids to we who supply answers - especially with its facility to see only unanswered messages. I personally go back 1 week to check.

If your message is not answered after a day or so then put a reply to it - which will bring it to the top again. If you still have no answer then it is likely that no-one knows the answer. Another reason is that the reply would take too much time - perhaps the message is badly written, or too long. Please also follow the general guidelines given for messages.

In general, the more work you have done on your own behalf the more likely an answer. As stated, our time here is limited.

 

[Matu]

Roger that!

Crystal clear Brian.

The "lost every hope" was not due to impatience of mine, but the fact that the posting had moved *that* fast made me think that soon would get lost from the sight of contributors. (Didn't know about this facility for finding ananswered messages.)

Thanks again!
Matu.