Anyone know how to do this in Numbers?

[jakesaunders27]

Hi,

Looking at creating a sales sheet for my business.

I want to have the months across the top and then be able to choose a product from a dropdown and it puts in its price but then also displays how much profit I make and then the remaining price that needs to be paid to the manufacturer which then totals up at the end of the month so I know how much profit I make each month and how much I pay the supplier that month if that makes sense!?

something like the screenshot, hard to explain hopefully someone gets what i want. Feel free to ask me more as I could do with sorting this out.

Cheers
Jake

 

[dyt1983]

edit: To remove personally identifying information not relevant to the thread.

 

[sracer]

Take a look at this video. Keep in mind it might not work with the latest version of Numbers (if it has been as dumbed down as Pages has been).

 

[jakesaunders27]

That's not too bad to do, but if you don't know how to do it, it will be easier to do with Quickbooks! I mean, A LOT easier.

(I'm presuming you're planning on selling more than one item per month... so you'd have a line of items below each month, correct?)

Yes thats correct, I'll take a look at Quickbooks.

----------

Take a look at this video. Keep in mind it might not work with the latest version of Numbers (if it has been as dumbed down as Pages has been).

I'll take a look, thanks.

 

[dyt1983]

edit: To remove personally identifying information not relevant to the thread.

 

[jakesaunders27]

Making drop downs/pop ups in Numbers still works in 3.5 ... but making all the rest of the formulas is where he'll probably need more help.

Yep just worked out how to create a drop down just getting the formulas right now

----------

That's not too bad to do, but if you don't know how to do it, it will be easier to do with Quickbooks! I mean, A LOT easier.

(I'm presuming you're planning on selling more than one item per month... so you'd have a line of items below each month, correct?)

Quickbooks is a bit expensive for what I want to do, just wanted to do this in Numbers

 

[dyt1983]

edit: To remove personally identifying information not relevant to the thread.

 

[jakesaunders27]

Then I recommend you get rid of the drop down idea. Instead format your spreadsheet with months across the top as it is now, and down the side put each type of product you have along with its unit cost and unit profit. Then under each month, put the quantity for that month of each item. Under each month you can use =SUMPRODUCT() to total the quantity vs each unit's cost and profit.

If you insist on a drop down... then put drop downs for the quantities, if you know a maximum you would sell in a month.

Thanks so much, I'll try it out tomorrow, it's late here in the UK!

 

[dyt1983]

edit: To remove personally identifying information not relevant to the thread.

 

[jakesaunders27]

It should be pretty quick to do, I just whipped one up to make sure I didn't send you on the wrong path:

Image

Good luck with it!

p.s. here's the formula I used:

Image

Not too sure how I want to set it out, thats not really what I'm after. As I want it to work out the profit for me and how much I have to pay that was the point in the sheet or have I misunderstood you?

Maybe what I'm trying to do isn't possible?!

Even if I didn't have drop downs I would like to be able to say type PRODUCT0001 into the field and it could maybe pull the data from another sheet or something so it knows how much it costs on the site how much I make and how much I need to pay the supplier for that item?

Cheers

 

[sracer]

Not too sure how I want to set it out, thats not really what I'm after. As I want it to work out the profit for me and how much I have to pay that was the point in the sheet or have I misunderstood you?

Maybe what I'm trying to do isn't possible?!

Even if I didn't have drop downs I would like to be able to say type PRODUCT0001 into the field and it could maybe pull the data from another sheet or something so it knows how much it costs on the site how much I make and how much I need to pay the supplier for that item?

Cheers

Yes that's do-able. I suggest looking into the vlookup function... that will allow you to have a separate "reference" table of products, costs, and prices, and pull the data you want from it.

 

[dyt1983]

edit: To remove personally identifying information not relevant to the thread.