making cells change relative to each other, cant figure out

[MMA]

my question/problem is kind of weird but here goes. I'm praying
someone smart and nice with some vba knowledge can help

I'm doing a food data worksheet for my nutrition class

I have five columns per food

quantity, calories. protein, carbs, fat

the foods are also in rows

What i want to do is enter the food data in for each food and
then have it so later on if i want to change a value in any of
the columns, the rest will move relative to the one

for example, if 1 oz chicken breast is 46 calories, 1 gram fat,
0 carb, 9 protein. I can change the protein to be 25 (multiplied
by 2.778) the rest will multipy their current values by 2.7778
automatically.

I talked to some people and heard vba is probably the only way
to do it and i am clueless when it comes to that. Any help with
an appropriate script for what im trying to do would be much
appreciated

thanks in advance

 

[Alan]

One way that you could do this without delving into the depths of VBA
is by using the SOLVER add-in.

For each food relate all other "properties" to it's weight - if cell
B2 is "Chicken Breast" and C2 is "Weight" (= 1oz) then D2 (say) is
Protein (=C2*9). All other properties would need to be similarly
related. All foods would need to be 'Calibrated' against 1 oz.

To increase all values to match Protein =25 run Solver with a Target
Cell of $D$2, To Equal a Value of 25 by Changing Cells $C$2. Because
all other cells are related to C2 when Solver finds the solution for
Protein all of the other values for that food will be updated.

For this to work the Solver add-in must be on your machine and it must
also be enabled - tick the Solver add-in box under <tools><add-ins>.

All of this of course could be achieved using VBA but if you just want
a quick and easy solution ....