How to fill cells from one worksheet to another

[kozz]

Hello: I am trying to create a spread sheet to track what I eat and the associated nutrients. I have created one worksheet ( called "Food List") that lists the food and its nutrients in a row of cells( ie. Food name - Protein - Carbs - Sodium - Fats - Sugar ). I have given each food an ID #. What I would like to do next is on the worksheet I created ( called "Food Log") is to be able to enter the food ID# and have the food name and nutrients for that food to fill in a row of cells that are labeled the same as in the Food List worksheet.

I'm kind of new at this any guidance would be appreciated

Thanks
kozz

[mehmetcik]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[kozz]

Thank you Mehmetcik for helping.

I have attached the workbook that has 3 worksheets.

The Daily Diet Log worksheet ( one Empty and one Populated) is what I am trying to
automatically fill when just entering the Food ID from the Food Nutrient Chart
worksheet. For right now I will enter the date and time manually whenever I put in a Food ID number.


Thanks
kozz

[mehmetcik]

formula for D9. =OFFSET(Sheet1!$A$1,MATCH($C9,Sheet1!$A:$A,0)-1,COLUMN()-3)

Fill Right and down.

[kozz]

Thank you Mehmetcik. Tomorrow's task will be to try to figure out the formula you used to make it work and learn from that. I will also try to figure out how to get rid of the #N/A when there is no entry in the Food ID cell

Thanks again for your help

kozz

[mehmetcik]

Formula for D8.

=IF(C8="","",OFFSET(Sheet1!$A$1,MATCH($C8,Sheet1!$A:$A,0)-1,COLUMN()-3))

1. =MATCH($C8,Sheet1!$A:$A,0) Match the contents of C8 in Column A of Sheet1. The $ fixes Column C and Column A as you fill right.

2. =offset(Sheet1!$A$1, Row from 1. -1, Column -3) Return the Value of the Cell where the value is found offset by column -3)

3. =IF($C8="","", Formula ) If Column C is empty then return nothing otherwise calculate the formula

[kozz]

Hi Mehmetcik. Thanks for your help. I understand most of what you did and why but I'm getting lost on the -1 of the MATCH part of the formula and on the COLUMN()-3 part. I tried to figure out why you did this but I don't see why. If you have time could you explain why you did this or direct me to an online explanation. I looked but could not find or understand

Thanks
kozz

[mehmetcik]

We are looking at the offset function.

We are offsetting from cell $A$1. The dollar signs fix the cell reference to A1 when we fill right or fill down.

Enter 101 in cell C8 on Sheet1(3)

So if we look for the code 101 it is in row 5 of sheet1.

So we need to look at Row 5. If we offset A1 by 5 rows we end up looking at A6. Which is wrong. So Offset by 5 - 1 gives A5. So that explains the Minus 1.

The first Cell that we are expecting a result in is Cell D8 on Sheet1(3) but we want that to return Data from Cell B5 in Sheet1.

Ok So If We offset Cell A1 by the column number for column "D" ie 4 then our formula will return the contents E5 which is wrong. We want the contents of B5.
So we offset A1 by the column number for column "D" ie 4 minus 3. This returns the contents of B5.

At we fill right, the formula is adjusted and each cell returns the right data.

[kozz]

Hi Mehmetcik:

I appreciate your help but I'm still confused about how/why D8 in Sheet1(3) plays in the OFFSET function when looking up a value in Sheet1. I would think it would be 1 column to the right of column A in Sheet1. I played with the formula by changing some number for the columns but it didn't come back with the right value and i don't understand why

Thanks for your help
kozz

[mehmetcik]

If Cell C8 on Sheet1(3 Contains 101 then

We want Cell D8 on Sheet1(3 to return "Apple Sauce (no sugar)" which is the contents of Cell B5 on Sheet1

B8 = Cell A1 on Sheet1 offset by 4 rows and 1 column

= Cell A1 on Sheet1 offset by the (row number of A5-1 ) =4 and the column of (D8 -3) =1

[kozz]

Thanks Mehmetcik: I think I understand now... I think!. However, I do not understand why the same formula ( nothing changes) is used for all the cells in a row. Shouldn't the formula for E8 on sheet1(3) be different than D8? The data for E8 comes from C5 on sheet1 so shouldn't the formula have a -4 instead of a -3 when referencing A1?

Thanks for answering all my questions
kozz

[mehmetcik]

Ok.

You are not understanding that the column() function returns the column number of the cell it is in.
So in D8 Column() returns 4 and in E8 it returns 5.


The formula in D8 is =IF($C8="","",OFFSET(Sheet1!$A$1,MATCH($C8,Sheet1!$A:$A,0)-1,COLUMN()-3))

The $ signs mean that the column numbers do not change as we fill right so the formula in E8 is still:-

=OFFSET(Sheet1!$A$1,MATCH($C8,Sheet1!$A:$A,0)-1,COLUMN()-3))

So =OFFSET(Sheet1!$A$1,MATCH($C8,Sheet1!$A:$A,0)-1,COLUMN()-3)

Offset Cell A1 in Sheet1 by ( 5-1 ) rows and Column number for cell E8 -3 columns

ie

Offset Cell A1 in Sheet1 by 4 rows and Column number for cell 2 columns

This returns the contents of C5.