Spread Sheet Formulas and Drop Down

**sabreclaw** · 05-13-2008, 11:58 AM

Hello All,

I consider myself pretty good with excel, but there are still areas that I am not very good in. Today I was tasked to create a sheet that would allow lower units to request orders. In the sheet my boss wanted to see the cost per soldier we put on orders. That comes from the cost of roundtrip travel and how many days they will be getting paid. That is based off their rank. My problem is this, I am attaching the sheet. I created a workbook with two sheets. The first sheet is the actual sheet they will put the information, I hope to have it figure everything also. In that sheet is a column for Rank. I would like to have a drop down menu for that, I have tried but cannot get it to work. I would also like when someone selects a rank it automatically populates the next column which is "1 UTA". So what I am trying to do is this. Say the persons rank is E7, when they select that from the drop down, the next column automatically populates how much they make for one uta. On my second sheet I have the ranks and the amount they get paid for one uta. If someone could tell me how to do this, I can do the basic math to get a total. I am attaching the sheet. please fell free to e-mail me directly at [email protected] any help is greatly appreciated, or call at 785-274-1547, as you can guess I am in the military. Thanks for any help.

MarK

**royUK** · 05-13-2008, 12:07 PM

You need to use Data validation see-

http://www.excel-it.com/data_validation.htm

Then use VLOOKUP to get the corresponding cost

**davesexcel** · 05-13-2008, 12:08 PM

Hi,
You need to name the range with the list, then in data validation, in the source box type =list
I have attached an example with the vlookup formula

**NBVC** · 05-13-2008, 12:10 PM

See attached...

First I defined a named range by going to Insert|Name|Define and naming the range ='Rank & Pay Rate'!$C$4:$D$23 as "Ranks"

Then I selected cell K4 and went to Data|Validation, chose List and entered formula: =INDEX(Ranks,,1)

Then in L4, I entered formula : =VLOOKUP(K4,Ranks,2,0)

To copy the validation down, select K4 and copy, then select rest of range and go to Edit|Paste Special and select Validation.

To copy down formula, drag the little black square at the bottom right of the cell down as far as you need.

**NBVC** · 05-13-2008, 12:12 PM

Well....I guess you know what to do now..

**sabreclaw** · 05-13-2008, 12:39 PM

Wow what a response, I didn't expect that at all. Thanks so much. I tried following a lot of the instructions given, and still cannot get it to work. I think I may be doing something wrong. This is something I have always wanted to learn. I work with excel tons, but never to the degree of doing vlook up. I am going to add some more things and send what I got. I will try this with the instructions, wish me luck, if I run into a snag will post. Have to have this done today, so will be posting what I come up with. Thanks

**royUK** · 05-13-2008, 01:09 PM

Take a look at the inventory sheet here

http://www.excel-it.com/workbook_downloads.htm

It uses VLOOKUP with Data validation.

**royUK** · 05-13-2008, 01:18 PM

Just found this example of some LOOKUP Functions in my files

**sabreclaw** · 05-29-2008, 09:37 AM

Anyone

I sent the sheet out for use and there is a problem, I attaching the sheet so you can see what I am talking about. The sheet works great, but for some reason, anyone with the rank of e5 and below gives a ref error. When you select the rank and years in service it should automatically populate the pay based off the pay chart sheet. Everything above the rank of e5 and above works fine, just e5 and below. Can anyone help. Thanks

**ChemistB** · 05-29-2008, 09:52 AM

Your defined name for Scale is incorrect. You have the rows/columns width, length backwards. The correct formula should be

=OFFSET('Pay Chart'!$D$3,0,0,COUNTA('Pay Chart'!$C:$C)-1,COUNT('Pay Chart'!$2:$2))

Does that work?

ChemistB

**sabreclaw** · 05-29-2008, 09:59 AM

I used the formula you sent, and it did not match the data on the pay sheet... Like the formula above...if I put in their rank, then the years of service, it automatically references the pay chart and gives me what their pay would be. When I input the formula you sent it changed the pay but that amount didn't match the rank or years in service

**royUK** · 05-29-2008, 10:15 AM

sabreclaw

Please read the Forum Rules & amend your Thread Title

**sabreclaw** · 05-29-2008, 10:26 AM

Sorry about that, I was in a hurry and didn't think about what I was writing, changed thread name. Sorry again, and thanks for pointing it out.

Sabre