How to incorporate drop down to reference different table values for pay?

[barracudamuscle]

Hello,

I'm trying to create a pay calculator to predict gross/net pay which will vary depending on single, married, dependants, payroll deductions like different choices for medical/dental. I've built several sheets just to test my formulas that cover different pay structures ranging from stateside, 3rd shift, deployed, lead deployed with the married no dependants table but want to make it easy to use for other situations like single, or married with 2 dependants.

Ideally I want to only need input from a user of their wage, their deployment rate than select a drop down somewhere on the first page selecting single, married, and another for dependants, and also 401K percentage withholding.

What is the best way to do this in excel? I'm very new to excel but willing to learn, but unsure the best path forward to accomplish this.

I appreciate your time and any suggestions you may have!

Pat

[Wurum Burner]

Hi,

There are a couple of ways to get a drop down box into your worksheet. IMO the easiest is with Data Validation from the Data menu on the ribbon. Select the cell where you want the drop down. With the Data Validation dialog, on the Settings tab, select List under Allow:. In the Source: box, you can either reference a cell range or simply type the values into that box separated by commas e.g., Married,Single.

Now you'll need a table somewhere in the workbook containing the values for the different status'. You can then look up those values using VLOOKUP so that, when you select Married from your data validation cell, a VLOOKUP formula that is looking at that cell will use its value to return the corresponding Married value from your Marital Status table.

Hope that makes sense.

[barracudamuscle]

Wurum,

First off I appreciate you taking the time and replying to my post with some solutions to my problem. I figured out how to do a drop down with data validation and started with filling status "single" or "married". Additionally I created a table for the federal tax and named it "Married" on a different sheet as a draft on sheet 2(2).

I'm not sure how to setup the formula/format to have that drop down list select the table from the other sheet to use that as the lookup. Any suggestions on how best to configure my drop down for married/single to use the appropriate tax rate table I created? I haven't populated the single table as I wanted to get it working with the existing data I entered for married, then once its setup and working expand to the single tables.

Thanks,
Pat

[JeteMc]

To make it a bit easier a fictionalized 'Single_Fed' table is added to the Sheet2(2) sheet and the data validation is moved to cell A14.
Cell C15 is populated using: =VLOOKUP(B15,INDIRECT(A14&"_Fed"),1,TRUE)
Cell D15 is populated using: =VLOOKUP(B15,INDIRECT(A14&"_Fed"),3,TRUE)
Cell F15 is populated using: =VLOOKUP(B15,INDIRECT(A14&"_Fed"),4,TRUE)
Let us know if you have any questions.

[barracudamuscle]

JeteMc,

Thank you so much for taking the time to answer my question and provide an example for me to work with! This helped tremendously in setting up my worksheets!

I've come a long way thanks to your assistance and learned quite a bit along the way! I've attached what I've done, it's far from where I'd like it but its slowly getting cleaned up and easier on the eyes. I figured I get the concept working before making it all look nice.

I'm working through some issues but I think with enough googling and trial and error I can figure it out. I learned that if I name one table from one sheet it will work for the entire workbook so I may figure out how to clean up all the sheets and make one just a tax table, while still figuring out a way to show different scenarios like stateside versus deployed.

Thanks again for your time and great assistance!

Pat

[JeteMc]

Glad to be of help, Best of luck with the tax calculator.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Feel free to open a new thread should you need help with another issue.
I hope that you have a blessed day.