+ Reply to Thread
Results 1 to 10 of 10

Connecting Cells / Help with diet plan

  1. #1
    Registered User
    Join Date
    02-27-2013
    Location
    iceland
    MS-Off Ver
    Excel 2003
    Posts
    7

    Connecting Cells / Help with diet plan

    I'm making a diet plan and I am currently having problems

    In one worksheet i have nutrition info (calories/proteins/carbs/fats)

    | CAL | PRO | CAR | FAT |
    e.g. Pasta | 293 kcal | 13,0 g | 51,0 g | 4,0 g |
    etc...

    In another worksheet I have a table for total nutrition for the day

    e.g. CAL PRO CAR FAT
    | | | | |

    What i want to be able to do is to write a equation for all the calories i eat
    e.g. =('Nutrition Info'!F11*2,5)+...

    and then the cells for pro, car and fat will update correspondingly

    So far i have been manually updating each of the 4 cells (cal/pro/car/fat), but
    since the equations are the same except the letters for the cells (e.g. Calories F11, Proteins G11, Carbs H11)
    it would be really nice if i could somehow connect pro, car and fat to mimic the cal equation.

    The only thing i've found as an solution is to use auto fill, but still that isn't satisfying as auto fill erases custom endings
    (i use kkal for colories and g for pro/cal/fat, after auto fill they all have the custom ending kkal)

    I hope I've made myself clear on the problem

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Connecting Cells / Help with diet plan

    Probably if you copy the e.g. to the right it will change the letters for the cells.

    Otherwise please post an excel example, without confidentional information.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Connecting Cells / Help with diet plan

    posted on the wrong question

  4. #4
    Registered User
    Join Date
    02-27-2013
    Location
    iceland
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Connecting Cells / Help with diet plan

    Here is an example

    i've highlighted the cells i want to be able to update automatically correspondingly to the calories cell which i update manually.

    And copying the cell won't work because of the same problem as with auto filling, it erases the custom ending.
    Attached Files Attached Files
    Last edited by ingvar; 02-27-2013 at 03:37 PM.

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Connecting Cells / Help with diet plan

    I should have solved it this way.

    In the orange cells you can seek all values on the sheet Nutrition

    It is made with a defined name.

    After that I used Vlookup to find the product and the values.

    If you have questions or comments, just ask or comment.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    02-27-2013
    Location
    iceland
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Connecting Cells / Help with diet plan

    Thanks! the only problem though with your solution is that its really space consuming

    the plan was to track every day calorie intake and with your solution i would really soon run out of space
    by rewriting every single brand of food again in the total worksheet.

    but still thanks alot for the effort!

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Connecting Cells / Help with diet plan

    You only fill in once the food in the total sheet and it will be found all other times.

    The range is now selected (whitch you can see with the blue cells) in the total sheet.

    That range can be expanded.

  8. #8
    Registered User
    Join Date
    02-27-2013
    Location
    iceland
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Connecting Cells / Help with diet plan

    if i got a cell with a function, e.g. this one: =sum(a1:a3)

    isnt it possible to make the cell next to the one with the function, mimic the function execept just use the letter next to the one being used

    to make it clearer:
    CELL1: =sum(a1:a3)
    CELL2: will mimic but with the next letter, therefore =sum(b1:b3)

    lets say i change the CELL1
    CELL1: =sum (c1:c6)
    then the CELL2 will mimic: and the function in CELL2 will be =sum(d1:d6)

  9. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Connecting Cells / Help with diet plan

    b1=sum(a1:a3)

    If you drag this formula to the right will c1 be:

    c1=sum(b1:b3)

    With adding the $, the formula won't change the row or column

    e.g.
    b1=sum($a$1:$a$3)

    If you drag this formula to the right will c1 be:

    c1=sum($a$1:A$3)

  10. #10
    Registered User
    Join Date
    02-27-2013
    Location
    iceland
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Connecting Cells / Help with diet plan

    To make it clearer, i WANT to make the cell next to the one with the function mimic but with the letter next to the one being used.

    The auto fill (drag) does exactly what i'm looking for, but i really want a function that does what auto fill does. Because after auto filling the custom endings (e.g. g and kcal) gets earased

    I hope i've made my self clear, and yet again thanks alot

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1