Solved

# Excel 2010:  How can I automatically adjust cell values based on the content of other cells? (follow up to #28590569)

Posted on 2015-01-12
89 Views
In ID #28590569, an Excel workbook (food log) was modified to allow the quick entry of data by selecting food item names from a dropdown list populated by a master data worksheet. Selecting a name from the list enters that name and populates adjacent cells automatically with the associated values from the master list (protein, carbs, fats, total calories and serving size).

Now that that is working beautifully, I want to automatically compare the total serving size entered by the above process to the actual serving size listed entered in column A.  If the numbers are different, the percentage difference in the portions needs to be calculated and then applied to the numbers for protein, carbs, fats and total calories, so they are adjusted to reflect the actual serving size.
Food-Log-December-2014.xlsm
0
Question by:Dave Kong
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 6
• 5
• 2
• +1

LVL 100

Expert Comment

ID: 40546845
Basic idea would be

[Protein Value] * [ColumnA] / [ServingSizeValue]

mlmcc
0

LVL 19

Expert Comment

ID: 40546939
You need an IF statement for the comparison and then, use the suggestion from above.
0

LVL 100

Expert Comment

ID: 40547077
Why is an if required?  If they are the same the multiplication will be by 1

mlmcc
0

LVL 30

Accepted Solution

gowflow earned 500 total points
ID: 40550707
As per past issues on last questions it was agreed to first fix Col A and B as need to split between figures and sizes then move forward to the calculation.

I have fixed all your Col A to reflect correct values in A and B for which kindly check and confirm.

Also have implemented in the macro the conversion so when you select an item (you should first have to fill column A with a value then it will convert all the different items as per the formula).

I have tried the first 3 items of sheet 12-1 so you can check the results.

You will need to go thru each and every item and reselect it so it bring back the figures and calculate.

Now here is how to do it.

When you hoover over the item in Col C or select the item if it shows in the combo this means it is in the table then you simply need to press F2 so it goes in edit and by pressing on the key Enter it will trigger the macro that will recalculate based on the data in A.

If the item is not found in the table and combo is blank then you obviously need to select the corresponding item and once it is selected din the combo it will then provide the calculated data.

Her eis the code that has been amended in the macro

``````Public Sub ItemsUpdate()
Dim WS As Worksheet
Dim WSFood As Worksheet
Dim sItem As String
Dim lItem As Long
Dim lRow As Long
Dim shp As Shape
Dim cCell As Range
Dim dRatio As Double

'---> Disable Events
With Application
.EnableEvents = False
.ScreenUpdating = False
End With

'---> Set Variables
Set WS = ActiveSheet
Set WSFood = Sheets("Food List")
Set shp = WS.Shapes(1)
lItem = shp.ControlFormat.Value
sItem = shp.ControlFormat.List(lItem)
lRow = ActiveCell.Row

'MsgBox "Coming from sheet: " & WS.Name & Chr(10) & "Displaying item number: " & lItem & Chr(10) & "Description: " & sItem & Chr(10) & "In Cell : " & lRow
'---> Locate the item in Food List
Set cCell = WSFood.Range("B:B").Find(what:=sItem, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)

If Not cCell Is Nothing Then
If WS.Cells(lRow, "A") = "" Then
MsgBox ("You should first Input Quantity in Col A")
Else
dRatio = Round(Val(WS.Cells(lRow, "A")) / Val(WSFood.Cells(cCell.Row, "G")), 1)
WS.Cells(lRow, "C") = sItem
WS.Cells(lRow, "D") = WSFood.Cells(cCell.Row, "C") * dRatio
WS.Cells(lRow, "E") = WSFood.Cells(cCell.Row, "D") * dRatio
WS.Cells(lRow, "F") = WSFood.Cells(cCell.Row, "E") * dRatio
WS.Cells(lRow, "G") = WSFood.Cells(cCell.Row, "F") * dRatio
WS.Cells(lRow, "H") = WSFood.Cells(cCell.Row, "G")

WS.Cells(lRow, "I") = WSFood.Cells(cCell.Row, "H")
End If

End If

'---> Enable Events
With Application
.EnableEvents = True
.ScreenUpdating = True
End With

End Sub
``````

I will post the entire workbook as it now contain the new updated values split in Col A and B.
gowflow
Food-Log-December-2014-V03.xlsm
0

Author Comment

ID: 40552372
Gowflow:

Thank you!  I am on a deadline but will check out the file later today.

Dave
0

Author Comment

ID: 40552843
Gowflow!

It's incredible!  It works beautifully.  When you enter a new item, if you fill out the actual portion first it just does the calculation.  This is even better than I had imagined.  THANK YOU!

Where do we go from here?  Do I need to open another question for you to set up the base sheet as you had suggested?

Dave
0

LVL 30

Expert Comment

ID: 40552910
yes please as this is a total different issue altogether. PLease post a copy of the address of the new question in here if you need my help. as just the ID (and the wrong one will not help !!! :)

gowflow
0

Author Comment

ID: 40554348
Will do later today.  Thanks!

Dave
0

LVL 30

Expert Comment

ID: 40554512
Pls If you need my help just wait for a sign as I will be off most of the day on Sat.
Tks
gowflow
0

Author Comment

ID: 40554610
You deserve a day off!  Have a great time!

Dave
0

LVL 30

Expert Comment

ID: 40556355
Sorry for the delay had to take my son to ski for the weekend !!! :)
back to biz

gowflow
0

Author Comment

ID: 40556526
I hope you had a great ski weekend.  I am closing out this case and have opened a new case for the final work:

0

Author Closing Comment

ID: 40556531
Gowflow has done it again!  The actual portion size is entered, the food item is selected, and the corresponding macronutrient values automatically calculate to match the portion size.  The size can be changed and the values recalculated - very flexible.  Another super job!
0

LVL 30

Expert Comment

ID: 40556635
Tks for the very nice appreciation and comment.
gowflow
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilâ€¦
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filledâ€¦
###### Suggested Courses
Course of the Month7 days, 19 hours left to enroll

#### 737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.