Drop down box

[niki538]

Hi i would like some help please, i would like to create a drop down that will select the customer’s name, which will then show their lawn size, how much they should pay and how much they have paid. Use conditional formatting to highlight in RED if they have a balance owing.

Please can i have some help

Thanks in advance Niki

 

[L. Howard]

Take a look here, seems to do what you want. If you need help adapting to you real life sheet post back.

https://www.dropbox.com/s/as13orhxfnk0d1l/Book5LawnCare.xlsx

Drop down in A1.
The vlookup in B1:D1 is done this way.

Select B1:D1 and while all three are selected type in =VLOOKUP(A1,G2:J6,{2,3,4},0). (Do not hit Enter)

Hold down the ctrl key AND the shift key and hit enter. This is array enter. Formula in formula bar will be encased with {....}

The conditional formatting formula in A1 is =VLOOKUP($A$1,$G$2:$J$6,4,0)>0

Regards,
Howard

 

[HalfAce]

Hi Niki, welcome to the board.
This is going to take a bit of explaining and that explanation makes a few assumptions that, hopefully you can re-arrange and make work for you.
First, the assumptions.
You have a data range of 5 columns, say columns G to K.
These columns are:
Customer name, Lawn size, Total price, Amount paid and Amount owed.
(Say a range of G2:K11 with G1:K1 being the column headers listed above.)

Now, say you want the 'dropdown' to be in cell A1.
For this exorcise we'll use Data Validation in A1.
Make your validation a List and make it refer to the range G2:G11.
(Now when you click the dropdown arrow in A1 you should see your list of names from G2:G11)

Now, in cells B1 through E1 we can use some VLOOKUP formulas.
In B1, enter the following: =IF(A1="","",VLOOKUP(A1,G2:K11,2,0))
In C1, enter the following: =IF(A1="","",VLOOKUP(A1,G2:K11,3,0))
In D1, enter the following: =IF(A1="","",VLOOKUP(A1,G2:K11,4,0))
In E1, enter the following: =IF(A1="","",VLOOKUP(A1,G2:K11,5,0))
This will show the lawn size, total price, the amount paid and the amount still owed for the name selected from the dropdown in A1.

Next, simply use Conditional Formatting in cell E1 with the rule being: if the value is greater than 0, format to red (or whatever).

Unfortunately, I can't really tell you where to find all the things mentioned to do this as I don't know what version of excel you're using. (I'm only using 2003 here and my 07 is at home, so if you're on anything newer than 03 I can't walk you through any of that.)

Hope (at least some of) this helps.

EDIT:
I see Howard was posting while I was still typing, so you might already be set...

 

[niki538]

Hi thanks for the help, however i am having a problem, i am doing it from an existing worksheet which have other information but it is not working.

I want to do the drop down in a new worksheet extracting the data from and existing worksheet.

Regards
niki

 

[L. Howard]

Move the example table to sheet 2, and your formula would look something like this. Where Sheet2!G2:J6 is the existing sheet and the drop down is on say sheet 1 (the new sheet)

=VLOOKUP(A1,Sheet2!G2:J6,{2,3,4},0)

You will have to re format the conditional formatting to get the red cell for balance due because it is drawing its value of determination from a different sheet now that the table is on sheet 2.

Don't forget to select all cells when you make changes to the formula and be sure to array enter.

Howard

 

[L. Howard]

Hi Niki,

Here is an example using two sheets. Drop down on one and lookup data on another.

This is the conditional formatting formula applied to the drop down cell A1 on sheet 1 =VLOOKUP($A$1,Sheet2!$G$2:$J$6,4,0)=0


https://www.dropbox.com/s/3sr3kfarr8a2q6r/Book5LawnCare%20Two%20Sheets.xlsm

Post back if you are still having trouble getting it set to suit your workbook.

Howard