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

#### Matu

##### New Member
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.

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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.

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
ABCD
1ProductDeliveryPackagingQuote
2Product5Delivery3Packaging1115.00
3
Quote

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.

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.

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.

Replies
3
Views
222
Replies
1
Views
250
Replies
0
Views
188
Replies
1
Views
410
Replies
2
Views
192

1,203,171
Messages
6,053,875
Members
444,692
Latest member
Queendom

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back