Spread Sheet Formulas and Drop Down

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

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

Okay I have been playing around with this all day. I set up a test page and got it to work. The issue I am having is that I cannot get a drop down menu to show up under account #. I did the validation, list but it will not let me select the field on accounts page. see attached. On my accounts sheet I have setup the vlookup so when you type a account number it automatically shows the name and location. I want a drop down menu of the account numbers from the accounts sheet to show. I can do a drop down on the accounts sheet, but when I copy the drop down to the invoice page it drops down with blank data. see attached

Create a named range on the ACCOUNTS page highlighting only the account numbers you want to add in the "Refers to" dialog box.

Next, on the invoice sheet, add the data validation to the correct column. In the Allow: dropdown, choose list. In the source box, type =Accounts (or whatever you named your range)

Let me know if that helps.

It worked, only 5 hours and 2 broken laptops and I did it. Thanks man. I am going to keep playing around with this. There is so much I can use this for in my job, its like a new toy. Thanks

Feel free to ask questions as you deem them necessary. There is no need to spend 5 hours and break 2 laptops if we can help you out!

Okay I have created another work book. There are two sheet, one is the Pay Chart, the other is the pay calculator. Now I am at a point I am getting confused again. I want a person to be able to hit a drop down menu for the grade on the pay calculator page, then hit another drop down bar for the years in service. When they select the years in service I want that to refer to the rank and give the amount of money they would recieve in the UTA column. More or less I want Joe Shmoe to be able to select the grade of a person, then how much time they have in and then for it to automatically show how much that person makes. I hope that makes sense. the problem I have is I don't know to do this. Any help would be awesome. Thanks

Try this sample file and see if it helps:

Okay, I would have to say I am floored. When I did the first vlookup I used the wizard after I figured out where that was. How did you do this? Can it be done with the wizard? Thanks a ton, that is exactly what I wanted, it rocks. Thanks, if you have time some time, could you explain the steps. Thanks

In theory this could be done with the wizard. In practice, it is not very practical.

Basically, it is a nested function (a function within a function). The INDEX function basically picks a value based on location. There are 3 arguments:
1. Range to choose from
2. Row Index
3. Column Index

For example, if you had a formula like this: INDEX(A1:B10,4,1), Excel would choose the 4th row down, and the first column, or in this case, the value in cell A4. The MATCH portion of the function give a place value based on matching. For example, MATCH(A1,B1:B10,0) would search through B1:B10 for the value of A1 (the zero at the end indicates that it must be an exact match). Excel then returns the place of A1 in the range. For example, if B4 matches A1, the answer would be 4. From there, you just piece the functions within each other.

Okay please see if you can help me with this. I almost have it complete. Need help with one last thing. On the pay calculator sheet...highlighted in yellow are three columns. I want a drop down bar that refers to the data on the sheet called per diem. The drop down bar should list the locations then once a person selects the location, it should auto populate the other two columns highlighted in yellow...hotel per diem and meal rate, based off the location. I have tried but cannot get it to work. Thanks, see attached.

Thanks a ton!!!! I showed it to my boss and he says "thanks", but he said I was missing one thing. He said I needed to extend the range for the years in service, since the guard allows people to stay in till their 60, we get a lot of people that have over 26.00 years of service. not that the pay changes to much, but there is a difference. I went in and hand jammed the years from where I left off on the pay chart, they are highlighted yellow. I then went to the pay calculator, and tried to redo the formulas, but ended up getting error after error. All I need is this. On the pay calculator, when you hit the years in service it needs to show the new years added to the pay chart, which are highlighted yellow. More or less, I just need it to work the same with the additional refernce fields on the pay chart added in. So when I select on the pay chart Rank = E7, and Years =40, it will show the pay he will receive. Right now the years only go to 26, I need that extended to the new data on the pay chart. Thanks see attached

Its awesome and serves the purpose. I still wish I could get a better understanding of how to do it. I know I can, I am just one of those people that has to see how it is done, and then can move on from there. Thanks again. Your assistance is greatly appreciated.

Mark

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. you can see sheet, can you help me with this. You can also see the remarks on the far right of the person who sent it. Thanks

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. you can see sheet, can you help me with this. You can also see the remarks on the far right of the person who sent it. Thanks

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

Please Login or Register  to view this content.

Does that work?

ChemistB

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

Are you putting that formula in the right place (Defined names)? Here's my amended spreadsheet.

ChemistB

Sorry about postig the reply in two places, will not happen again. The sheet you attached on previous page will not open.

It's a zipped copy. Here is an unzipped copy.

ChemistB

Worked awesome you and NBVC rock, thanks again. Both sheets are working great.