Dec 16 2021 09:24 AM
Hello,
I am trying to create a time sheet that will populate the invoice amount. I have several drop down lists on another tab to choose certain info from that will populate on the first tab. I would like to see how to write a formula to say this:
On TS-1 If 12 - Ft. Myers is chosen from the dropdown list in B-11, it should populate the percentage for that site in cells O-17 & Q-17 respectively so that the sheet will calculate the totals for those rates. Here is the formula I have written already but as you can see, it is not returning the value needed.
='Dropdown Lists'!E72=IF(AND(OR('Dropdown Lists'!A30,'Dropdown Lists'!A34,'Dropdown Lists'!A50)), N17 + N17*45%)
Another problem is, not all the percentage rates are the same for all the sites. I am not sure if the SWITCH function would work best or the IF function, and even if I did there is so much info it is all floating around in my head, and I can't seem to see a way to get it all tied together.
Please help if possible. It doesn't seem like I am asking Excel to do something it cannot do; I think I just don't know the correct command. Also I am not able to attach the file, although I feel that would be the best way to see what I am taking about.
Thanks a million!
Dec 16 2021 10:12 AM
You're right that being able to attach a file would be the most helpful, but there are safeguards in place so that brand-new users can't inadvertently upload a virus-infected file.
SO...my sense is that you'd be better served by using a table of the various values that might apply to Ft. Myers, or Sanibel, or Cape Coral (or whatever your various locations are).
Then use VLOOKUP or one of the other lookup functions.
Let me refer you first to this website where VLOOKUP is explained. https://exceljet.net/excel-functions/excel-vlookup-function
If that isn't sufficient (or doesn't seem to fit) let me invite you to come back and, in the absence of the actual worksheet, describe more fully what is going on, what value(s) are associated with different locations, etc. With that information, I or or one of the other experts here could create a spreadsheet to demonstrate how it works.......
Dec 16 2021 10:23 AM
Here's a very simple example of how VLOOKUP can be used to return a value, based on a selection from a drop down cell. Select the city name, and the value will appear in the yellow background cell, which is where the formula is. This is a very simple example. VLOOKUP can be used with a much more complex table, with more columns. See that reference in my last message for more on how to use it.
Dec 16 2021 11:53 AM
Thank you so much for these references. I think the VLOOKUP may work, BUT what I need it to do after that is take that returned value and multiply the % with the value of another cell to return the monetary value I am looking for in the cell with the formula. Let me start from the beginning. Below is a timesheet. Ideally, a pay rate would be entered into the N column. Based on the chosen value in B-11 where the site dropdown list is the O column would then take the % that VLOOKUP found which matches that site and add the N column value to the N column value * the returned percentage to populate the correct billing rate for that pay rate or =(N18)+(N18*45%).
The tables where the percentages and sites are located is on the next tab.
I created a second table for the sites, but I am not sure I needed to and that could be complicating things a bit.
So, if a payrate is plugged into the N column the worksheet should do the math for all the rates based on the returned percentage for a given site and spit out a total invoice. I hope you have better luck than I have. I cannot seem to get around how to tie all the info together and make it do what I need it to. Maybe there is too much info needing to be calculated??
Thanks so much again for your help on this. I am so stumped, but I know there has to be a way. I love Excel for these kinds of projects.
Dec 16 2021 12:14 PM
Unfortunately the images are too hard to see......
But let me assure you that VLOOKUP can be embedded in another formula, to multiple a given value by a percentage, or increase it by a percentage that is itself looked up in a table.
(I had a career in Human Resources, worked with payroll data all the time; Excel is fully capable of handling your situation. We just need to get a working example that works for you.)
So I've added a level of complexity here to the sample file. Again just an illustration of what's possible.
Dec 16 2021 12:29 PM
This doesn't by chance help, does it?
Dec 17 2021 12:34 PM
Dec 17 2021 02:16 PM
Dec 20 2021 08:37 AM