hey aladin y indirect doesnt work for no adjacent range or cell?y do we ve to write in same column or rows name like u gave example of USA and France?y indirect doesnt work>?
Is there any way to have a value attached to selections in the first and then dependant selections?
For example, assume these are selections from a list
A1: Package1 A2: all supplied A3 (I want this to be the price)
So ideally i would like package1 to be $190, and all supplied to be -$70, so the total in A3 to be $120.
the list in A1 has about 15 selections, and the list in A2 has about 10 selection (as conditions to A1), so hoping there is a simple way to calculate the total other than typing in every possible total costs.
Thanks in advance
Shouldn't that be just:
A3:
=SUM(A1:A2)
where A3 is a formula cell, not a data-validated cell.
LOL, very good haha. Let me try to explain better
So in A1 and A2 are dropdown lists. A1 has selections "Package1" through to "Package8", and A2 has selections "addextra", "lessextra" and "allsupplied".
Package 1 in reality has a value of $190, and All Supplied in reality has a value of -$70, so if i supplied a customer with Package 1 all supplied, the total price would be $120.
Is there a way to Select Package1 in A1, but excel recognises this selection to imply $190, and select Allsupplied in A2 for excel to recognise this as -$70, to give a total in A3 as $120.
The 2 ways i can think of is 1- use the if function multiple times, very time consuming, or 2- have columns in between with conditional dropdown lists giving only the values applicable to each package, and then sum those columns as you suggested. I'm just hping there is an easier way.
Thanks
I have a similar problem. I have 7 worksheets basically one for each supplier, each sheet has list of products, description, qty and cost. I have created a drop down for each supplier in B2 and cell C3 will return the product list for that supplier (indirect function). What i would like to do is also bring the cost across as well. The target columns in the worksheets are A? and D?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' automatic DropDown when the cell is clicked, the drop values display
Dim lngValidType As Long
On Error Resume Next
lngValidType = Target.Validation.Type
If lngValidType = 0 Then Exit Sub
If lngValidType = 3 Then SendKeys "%{down}"
End Sub