Linking two values, then to be able to change one to see how the other is affected

[mpeters87]

Hi there,

I’ve been struggling to figure this out, and I’d appreciate any help.

I have a very basic table. One column is the name of products, the second column how many were sold, and the third column how much money was made off those sales. I’d like to set up away to link the two values. I’d then like to be able to change the value in the number sold to see how it would affect the money made, and to be able to set a money made target, to be able to see how many would need to be sold to make that much money.


Writing out it seems so simple but I’m just not familiar with how to do this. I’ve attached an example worksheet. Let's assume the columns are A, B and C.

Thank you in advance.

Note: the actual sheet I’d be using uses vlookups to populate the cells, but I have left them as values for the sake of this.

	Quantity	Profit
Sesame Seeds	4,500	31
Quinoa	4,800	960
Bulgur & Quinoa	594	650
Monkey Nuts	2,520	1,246
Peanuts R&S	3,024	978
Macedonian	11,880	2,793
Onassis Giant Beans	2,288	627

<colgroup><col width="169" style="width: 127pt; mso-width-source: userset; mso-width-alt: 6180;"><colgroup><col width="64" style="width: 48pt;" span="2"><tbody>
</tbody>

 

[Toadstool]

A cell can either be a value or a formula (unless you use VBA) so I'd leave your three columns unchanged and add three more for overrides and results:

	A	B	C	D	E	F
1		Quantity	Profit	Override Quantity	Override Profit	Message
2	Sesame Seeds	4,500	31	50,000		Sales of 50000 gives profit of 344
3	Quinoa	4,800	960		2,500	Profit of 2500 needs sales of 12500
4	Bulgur & Quinoa	594	650
5	Monkey Nuts	2,520	1,246
6	Peanuts R&S	3,024	978	25,000		Sales of 25000 gives profit of 8085
7	Macedonian	11,880	2,793
8	Onassis Giant Beans	2,288	627		1,000	Profit of 1000 needs sales of 3649

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

mpeters87​

Worksheet Formulas Cell Formula F2 and down =IF(E2&D2="","",IF(D2="","Profit of "&E2&" needs sales of "&INT((B2/C2)*E2),"Sales of "&D2&" gives profit of "&INT(D2/(B2/C2)))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

I could add a check that you don't enter both overrides and I could use TEXT to format the number results but I'll keep it simple.