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
86 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

828 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