+ Reply to Thread
Results 1 to 4 of 4

Values assigned to A3 Sheet 2 applied to any cell in sheet one when A3 is true

  1. #1
    Registered User
    Join Date
    03-29-2015
    Location
    California
    MS-Off Ver
    2010
    Posts
    2

    Values assigned to A3 Sheet 2 applied to any cell in sheet one when A3 is true

    Hello,
    Thank you for letting me in this forum. I'm new to this so I'm not sure what to expect. I will try to make this as simple as I can.
    I have a workbook with two sheets. The items in sheet 1 change daily. The items in sheet 2 are constant. So, sheet 2 is food and their values. Sheet 1 is food consumed today. So, in sheet 2 I have (A1)Apple (A2 Calories) 30 (A3Carbs) 5 and so on. What I want is, if I type Apple in any (A1) cell on sheet 1 the rest of the data to be filled in from (Apple) Row on sheet 2.
    My mother has lung cancer and is not eating well. She thinks she is eating enough. So, I created this work book to track what she eats so we can see exactly how many calories she is getting each day. I have been, copy past, copy past and that works fine. But, it would be real slick if it would do it for me.

    Thank you very much for your time.

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Values assigned to A3 Sheet 2 applied to any cell in sheet one when A3 is true

    Create your food table on Sheet2 like this...

    Data Range
    A
    B
    C
    1
    Food
    Calories
    Carbs
    2
    Apple
    30
    5
    3
    Banana
    50
    10
    4
    Carrot
    10
    5
    5
    Pasta
    120
    45
    6
    Bread
    70
    12


    Then, on Sheet1 you enter the foods eaten for that day like this:

    Data Range
    A
    B
    C
    1
    Food
    Calories
    Carbs
    2
    Pasta
    3
    Banana


    Enter this formula in B2:

    =IF($A2="","",VLOOKUP($A2,Sheet2!$A:$C,COLUMNS($B2:B2)+1,0))

    Copy across to C2 then down until you get blanks.

    Data Range
    A
    B
    C
    1
    Food
    Calories
    Carbs
    2
    Pasta
    120
    45
    3
    Banana
    50
    10
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    03-29-2015
    Location
    California
    MS-Off Ver
    2010
    Posts
    2

    Re: Values assigned to A3 Sheet 2 applied to any cell in sheet one when A3 is true

    That worked !! Thank you, Biff, That's awesome. I had to change a few little things because my data went out to AH but that was not to hard to figure out. I'm going to share this with others who are working on nutrition for sick family members. They will love it. I will credit you and this site for sure. My original had Breakfast, Lunch and Dinner down column A so it shows errors in those rows but I'll play around with it and try to figure it out. Thank you again! You Da Man!

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Values assigned to A3 Sheet 2 applied to any cell in sheet one when A3 is true

    You're welcome. Thanks for the feedback!

    if you need more help getting rid of those errors just let me know.

    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Macro assigned to button in Cell A3 copies data in range B3:Z3 to another sheet...
    By tche misere in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-02-2014, 10:33 AM
  2. Replies: 9
    Last Post: 09-25-2012, 11:47 AM
  3. Sum values based on TRUE/FALSE property on different sheet?
    By grantpanderson in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-19-2010, 11:38 AM
  4. Replies: 2
    Last Post: 04-01-2010, 11:19 AM
  5. Replies: 1
    Last Post: 03-16-2007, 08:25 AM

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