On Sep 6, 1:24*am, Niall wrote:
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
"Max" wrote:
Source data in Sheet2 as posted, from row2 down
In Sheet1,
Input in A2 (Cust), eg: R0335
Put in B2:
=IF($A$2="","",IF(Sheet2!A2=$A$2,ROW(),""))
Leave B1 blank
Put in C2:
=IF(ROWS($1:1)COUNT(B:B),"",INDEX(Sheet2!B:B,SMAL L(B:B,ROWS($1:1))))
Copy B2:C2 down to cover the max expected extent of data in Sheet2, eg down
to C200? Minimize/hide col B. Col C will return all destinations for the
input in A2, neatly packed at the top. You can easily build on the other
lookups in adjacent cols pointing to the multiple returns in col C.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800 Files:359 Subscribers:54
xdemechanik
---
"Niall" wrote:
Hello, I hope that some1 can help me. *I have a spreadsheet which looksup a
list in another sheet of the same workbook based on what account number is
entered in a particular cell. For example,
Sheet1
* * * * *A * * * * * * * B * * * * * * * C * * * * * * D
1 *Customer * *Destnation * *Rate * * * *Value
2 * R0335 * * * *=vlookup.. *
3
4
Sheet2
* * * * *A * * * * * * * B * * * * * * * C * * * * * * D
1 *Customer * *Destination
2 * A0022 * * * *Ireland
3 * D1034 * * * *Scotland
4 * R0335 * * * *Ireland
5 * A0022 * * * *Wales
6 * R0335 * * * *France
7 * C0078 * * * *Spain
8 * R0335 * * * *Germany
Basically, when I enter the a/c number R0335 I need cell B2 on sheet1 to
list the destinations that R0335 go to, so the operator can list the one that
is required and then cell C2 will look up the rate on B2 using the vlookup
function.
Any help would be gratefully appreciated as this is proving very hard for me
to keep calculating manually as there are about 250 per day.
Thanking you all in advance
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,R OWS($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