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
14
83 Views
Last Modified: 2015-01-18
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
Comment
Question by:Dave Kong
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 40546845
Basic idea would be

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

mlmcc
0
 
LVL 18

Expert Comment

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

Expert Comment

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

mlmcc
0
 
LVL 29

Accepted Solution

by:
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
    .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
 

Author Comment

by:Dave Kong
ID: 40552372
Gowflow:

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

Dave
0
 

Author Comment

by:Dave Kong
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 29

Expert Comment

by:gowflow
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:Dave Kong
ID: 40554348
Will do later today.  Thanks!

Dave
0
 
LVL 29

Expert Comment

by:gowflow
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

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

Dave
0
 
LVL 29

Expert Comment

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

gowflow
0
 

Author Comment

by:Dave Kong
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:

http://www.experts-exchange.com/Software/Office_Productivity/Spreadsheets/Q_28599000.html
0
 

Author Closing Comment

by:Dave Kong
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 29

Expert Comment

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now