Max,
Many many thanks for the reply but I would say that I have lost it
somewhere. I entered the formulae where instructed but nothing happened -all
columns are blank when I enter a customer number. This is exactly the sheet1
and sheet2 layouts:
Sheet1 (Billing)
A B C D E
F G H
1 Date Docket No Account Customer Destination Radial Drops
Value
2 22-4-06 12345 R0335 Mr. J Blog (List of his destinations
should drop here)
Sheet2 (Site Radial)
A B C
1 Acc Destination Radial
2 B0015 Germany 32
3 C0723 France 28
4 R0335 Ireland 4
5 F0005 Spain 26
6 R0335 Italy 31
and so on.... with account number repeating with different destinations
I just needs the list of destinations to be on a drop down list when the acc
is entered so that the operator can select the desired destination.
Again thanks and let me know if it possible and if your first reply is right
where did I go wrong.
Kindest regards
Niall
Hi Niall,
Max's formulas work very nicely. I have used them on Sheet2 to produce
a list of destinations depending on the Account No.
The only problem is that you are wanting the list of destinations to
appear in a drop down list on Sheet1. You can't produce a drop down
list in a cell just by using formulas.
Try the following...
On Sheet2 in F2...
=IF($E$2="","",IF(A2=$E$2,ROW(),""))
and Sheet2 in G2...
=IF(ROWS($1:1)>COUNT(F:F),"",INDEX(B:B,SMALL(F:F,ROWS($1:1))))
These are Max's formulas adjusted for working on Sheet2. Fill them
both down far enough so that they are able to work on all of the
Sheet2 data in columns A and B.
With those two formulas in place you will get a list of destinations
in Sheet2, starting at G2, depending on the Account No entered into
Sheet2 E2.
With the data supplied; R0335 in Sheet2 E2 results in Ireland in G2
and Italy in G3; while C0723 in Sheet2 E2 results in France in G2; etc
for the other Account numbers.
Put the heading "Destinations" into Sheet2 G1.
Select G1 then make the cells below this heading a Dynamic Named Range
named "Destinations" by going Insert|Name|Define to bring up the
Define Name dialog.
Into the Names in workbook: box type...
Destinations
Into the Refers to: box type this formula...
=OFFSET(Sheet2!$G$1,1,0,SUMPRODUCT(--(Sheet2!$G$2:$G$200<>"")),1)
This formula will handle a list of up to 199 destinations. I am
guessing that this number of destinations is unlikely to be exceeded
by any of the Account numbers. If this is not the case then increase
the 200 in the Sheet2!$G$2:$G$200<>"" part of the formula to a
suitably larger number.
Click the Add button then OK.
On Sheet1 select as many column E cells (Column E on Sheet1 is your
Destination column according to your last post) that you need to have
a data validation drop down for the applicable destinations.
Go Data|Validation to bring up the Data Validation dialog. In the
Allow: box on the Settings tab select List and in the Source: box
type...
=Destinations
then click OK.
The next thing you need is the tiniest bit of code in the Sheet1 code
module that detects which Sheet1 column E (Destination) cell has been
selected by the user so that the appropriate Account No can be entered
into Sheet2 E2 resulting in the appropriate destination values into
the drop down.
Copy this code (next 7 lines of text)...
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("E2:E" & _
Cells(Rows.Count, 3).End(xlUp).Row)) Is Nothing Then
Worksheets("Sheet2").Range("E2").Value = _
Target.Offset(0, -2).Value
End If
End Sub
then right click the Sheet1 tab and select "View Code" from the pop up
menu. Then paste the code into the Sheet1 code module. After saving go
File|Return to Microsoft Excel.
With this code in place you might have to change the level of Security
applied to the workbook. The highest level that can be used and have
macro code operate is Medium, and then when the user opens the
workbook they need to click the "Enable macros" button on the Security
dialog that pops up.
If you have any problems just email me (Look in my Profile) and I will
reply with an example workbook.
Ken Johnson
