# Drop down box

#### niki538

##### New Member
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

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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

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...

Last edited:
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

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

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

Replies
2
Views
127
Replies
1
Views
164
Replies
5
Views
197
Replies
1
Views
194
Replies
2
Views
146

1,220,955
Messages
6,157,041
Members
451,394
Latest member
indrajeet_rajput

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back