• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 98
  • Last Modified:

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

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
Dave Kong
Asked:
Dave Kong
  • 6
  • 5
  • 2
  • +1
1 Solution
 
mlmccCommented:
Basic idea would be

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

mlmcc
0
 
Richard DanekeCommented:
You need an IF statement for the comparison and then, use the suggestion from above.
0
 
mlmccCommented:
Why is an if required?  If they are the same the multiplication will be by 1

mlmcc
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
gowflowCommented:
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
    .DisplayAlerts = 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
    .DisplayAlerts = True
End With

End Sub

Open in new window



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
 
Dave KongAuthor Commented:
Gowflow:

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

Dave
0
 
Dave KongAuthor Commented:
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
 
gowflowCommented:
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
 
Dave KongAuthor Commented:
Will do later today.  Thanks!

Dave
0
 
gowflowCommented:
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
 
Dave KongAuthor Commented:
You deserve a day off!  Have a great time!

Dave
0
 
gowflowCommented:
Sorry for the delay had to take my son to ski for the weekend !!! :)
back to biz

gowflow
0
 
Dave KongAuthor Commented:
I hope you had a great ski weekend.  I am closing out this case and have opened a new case for the final work:

http://www.experts-exchange.com/Software/Office_Productivity/Spreadsheets/Q_28599000.html
0
 
Dave KongAuthor Commented:
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
 
gowflowCommented:
Tks for the very nice appreciation and comment.
gowflow
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 6
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now