Auto Populate Lists in Excel with Service, Gross, Net and Profit

[kimmleo]

I have a small business that I work for part time, I am basically doing the invoicing and billing. My issue is I have an excel sheet that contains services, gross costs, net costs and profits. What I want to do is create an excel document that if a service is chosen, it will auto populate the gross, net and profits. I have been searching on Google for the correct excel videos but it is difficult to search for something if you are not quite sure what to call the formula or function you are looking for.

What I would love to do is get an excel expert that could help me get my worksheet into a more easy to use and manageable place. How do I find reliable and inexpensive excel help which for an expert would probably take them 20 minutes to solve!

Please help I am so frustrated!!! Kim

[mcmahobt]

Most of the people on the forum provide help pro bono. If you can supply a sample workbook, and provide what you would like the expected results to be, we can try and provide more help.

[kimmleo]

Okay so I attaching a sample worksheet, on tab one I have a list of services, gross costs, net costs and profits. What I am trying to accomplish is I would like to create a list of services on my billing form tab two so if I choose a service (column G) it will auto populate the gross (column I), net (column K), etc (this would greatly lessen my chance of errors, because I am currently manually adding these rates) and then on the utilizing the same list of services and gross rates I want the same list of services and gross rates to auto populate on my invoices tab three as well.

I have been searching and searching online on how to do this but if you are not aware of what to call the formula/function it is so hard to search!

Thanks, Kim

[kimmleo]

I would be fine to create this on my own just need to be pointed in the right direction of which formulas or function would fit my needs.

[mcmahobt]

See attached for an example. I may be off the mark for what you want, in which case, more explanation on your part may be useful. Basically, I turned your Services on your Rate Sheet into a Data Validation list, so in column G on your Bill Form worksheet you'll notice you can now select a service based on the dropdown. The Gross, Gross Total, Net, Net Total, and Profit are all then calculated automatically (and pulled based on a match from the Rate Sheet). The only thing I was unsure of here was Hours (since that was pre-populated).

You'll then notice that Invoice updates the Date, Service, and Hours based on what is entered into the Bill Form worksheet. The records within Bill Form don't have to be entered contiguously for them to be shown as such in the Invoice worksheet (that is to say, if you enter data into Row 2 and Row 5 in the Bill Form worksheet (as shown in the example), Invoice will already consolidate those records automatically).

Hope this helps!

[kimmleo]

OMG getting closer!! I am so excited you made my day!! Okay so the number of hours on the bill form at first is estimated (for sending quotes) and then changes to actual hours once the job is completed would be a variable (based on actual hours worked) and the profit is column M which is column J - column L, which I would then total at the the bottom. As for the invoice each invoice is different so I would want the ability to use the Services and Gross on that but coming from the same validation list not from the bill form.

I can see you are an excel expert and you do understand my needs, is there anyway we can set-up a quick meeting, so I can create a document for 2016 and you can help me set up my companies needs? These formulas are a bit above my head!

[kimmleo]

Or should I just set up my 2016 workbook and you can help me?? Wow I can't believe you do this pro bono it is such a nice thing to do saving someone HOURS and HOURS on their life how can I begin to thank you?? LOL

[mcmahobt]

See attached for another iteration. Which field do you want Gross to be looked up in based upon the Services selected within the Invoice worksheet?

As to your document for 2016, if you post your workbook here and add your requests/questions, I along with other board members can more easily help you.

[kimmleo]

I want to use the Services and the gross rates in the invoice so using the same list when the service is chosen the gross will be populated using the same logic as the billed form (except with only the services and gross rate fields being used, I can manually do the rest). Does that answer your question?? And thank you so much!! The second example I believe is much better than the first!

[mcmahobt]

If I understood you correctly, see attached.

[kimmleo]

Yes perfect!! Thank you so much now I just need to figure out how to apply this to my current excel workbook and/or my 2016 workbook, which I have not started yet. I am going to work on this over the weekend and will probably post again on Monday to get help with the formulas. Thanks again and have a great weekend!

[kimmleo]

Hi I hope you had a wonderful weekend, so using the 2016 excel sample attached I was wondering if I could get help with what was previously discussed in the forums threads before. So I wanted on the billing form when the service is chosen it populates the gross, net and profit the only variable that should be changeable is the hours. Want the same services and gross on the invoice. just a question, could you explain a little bit about how you are doing this (functions and/or formulas) so I can look up lessons on how to learn more about this and also for the bill form can I also have the ability to do a drop down with names and how would I add or remove people from that list without ruining the function? Thanks again so much, you are such a lifesaver!

[mcmahobt]

Wherever you want your dropdown of names to be placed, select that cell (or range of cells), and select Ribbon>Data>Data Validation>Allow List>In the Source Box enter:

Please Login or Register  to view this content.

and select Okay. This will enable a dropdown list for your employees, and will dynamically change as you add/remove employees. As for your other requests about adding a Services dropdown and incorporating a gross/net/profit result (on the the Billing form), that was already shown in a previous workbook I had uploaded for you, but if you need more help just let me know.

[kimmleo]

You know that ole sayin' ... Give a man a fish and you feed him for a day; teach a man to fish and you feed him for a lifetime. What I wanted to do is learn on how I can make the services attach to the different rates. Could you tell me what you did in your sample, so I can try and figure it out and try it myself on my 2016 workbook. If you want to just tell me what functions and formulas to look up I want to learn how to do it... I know you made a data validation list with the services, not sure how to do the rest... Thanks again, Kim

[mcmahobt]

You can approach it from two ways:

A VLOOKUP() or Vertical-Lookup is a database function, meaning that it works with database tables – or more simply, lists of things in an Excel worksheet http://www.howtogeek.com/howto/13780...okup-in-excel/

The function (or combination of functions) I used in your workbook were INDEX/MATCH. It also functions as a database function (when used together), but is more robust than VLOOKUP() in that you can perform matrix lookups http://www.randomwok.com/excel/how-to-use-index-match/

The lookup essentially takes a cell, looks through a range of cells for a match to the previous cell, and returns the value of a cell associated with the initial range (within the same row/column).

[kimmleo]

Dear excel genius, by the time I figure all of that I will probably have missed all of 2016 (LOL). I am hoping you will give me a holiday miracle and help me with the example that is attached (please). As per our previous conversations for the billing form I want to have a drop down of the TPI Employee Names and also when I pick a service I want the gross and net rates to come up in the billing form (so that when I add the hours "the variable" it will calculate the gross and net rates and profit) and then for the invoice I want to use the service and the gross from the same lists to come up in the invoices. If you could help me with this it would save me so much time in 2016 and give me some time to enjoy my life a bit!

There is an actual job (Hunter) attached that we can use as our first example (please)!

Thank you so much for your help!

[mcmahobt]

Example attached. You didn't specify where you want the gross values inserted into the Invoices worksheet, so I have omitted that for now. Also, if I recall correctly, this has already been done in previous example workbooks I have submitted.

[kimmleo]

So on the billing form the gross total =sum of col G * col H, the net is col G * col J and the profit =sum of col I - col K and on the invoice column J is the gross rate that is linked to the service (from the same lists) so when you pull up a service on the invoice it is attached to the gross rates (same as the billing form is).

Oh you don't know how much you are helping me out with this, I am so grateful! Kim

[mcmahobt]

See attached.