Create a drop down list box with specific data!

[Spellbound]

Stumped again on how to create a drop down list box which contains specific data based upon the input of another cell.

Using the attached example; I am trying to create the drop down list box in cell C6 on the OUTPUT DATA worksheet.

I need it to show the loan numbers for the customer whose name and reference number are shown in cells C4 and C5 respectively. Which in this example is Rudolf Reindeer, customer number 758073.

I want the list box to only show the loan numbers that have a balance greater than zero based upon the figures shown in Colum I of the MAIN DATA worksheet.

This would mean that for this customer only 4 of the 6 loan numbers listed in Column E of the MAIN DATA worksheet would be shown in the list box.

598
606
672
861

I have tried searching for a solution on the forum but there does not appear to have been a similar query that I can find.

Hopefully, someone out there will have the answer.

Thanks in advance ...spellbound

[ratcat]

G'day Spellbound,

I'm not sure this will suit your conditions. I try to make all the formula dynamic has possible.

Also trying to keep your layout intacted and not doubling up on data entry.

Now the juicy bits.

On the 'Main Data' sheet I only moved the Loan column across to the left. Adjust for the formulas on the 'Output Data' sheet.

On 'Output Data' sheet:

Adjust the formulas in the range C2:H6

Col L is an array formula is get the loan numbers accordingly to the name.

Col N is an array formula to return single unique entries.

Col O is an array formula to take out the blanks in Col N.

The drop down boxes at C4 and C6 both have a offset formula looking at the col L and col N.

All those columns can be hidden.

I've also add a VB, so everytime you change the name the loan number will clear and then you can select a fresh list of numbers.

I'm not entirely happy with this workbook, but its a start.

Play around and see if you like.

Cheers

[Spellbound]

Hi ratcat

Firstly, I want to say 'thanks' for taking so much time and trouble to try and work out a solution to my problems.

It will be a few days before I have time to play around with your example but I did notice that it only opens as a 'read only' file and that many of the cells that should contain the results are empty.

This makes me wonder if you are using Excel 2007 as I am still using Excel 2003 which has the addon that allows me to read Excel 2007 files but I believe there are a number of differences between the versions, which could cause this problem. So I would not be able to use formulas or functions which have come from Excel 2007.

It would help if you could explain why the need to move the 'loan' column for this purpose, as this will affect other sections of the main workbook.

Regards ...spellbound

[ratcat]

...... I did notice that it only opens as a 'read only' file and that many of the cells that should contain the results are empty.

This makes me wonder if you are using Excel 2007 as I am still using Excel 2003 which has the addon that allows me to read Excel 2007 files but I believe there are a number of differences between the versions, which could cause this problem. So I would not be able to use formulas or functions which have come from Excel 2007.

My computer chuck a wobbly and not knowing it affected the file.
Btw I've always had Excel 2007 default save as 97-2003 files for day one.

File must be corrupted ??

When you come across a 'read only' file and you need to make changes. Just re save the file a different name. If you didn't know.

See attached for re save version

It would help if you could explain why the need to move the 'loan' column for this purpose, as this will affect other sections of the main workbook.

Regards ...spellbound

For you, there is no need to move that col.