Automatic data insertion

[pewe]

I don't have much experience with formulas in excel, and have no macro experience, and need to be pointed in the right direction to find the information to solve the issue shown below. (I have searched but with no success as I am not sure what terms to be searching on.

Here is the issue.

My sisters doctor has asked her to keep track of all the food eaten by her son who is not very well.

She has to make sure he does not take more than a certain amount based on a percentage of his GDA for certain ingredients.

To save her having to manually write down and add up the information to track it, I thought an Excel spread sheet would help so that as she fed him (which has to be regularly through the day) she could simply enter the ingredients on a spread sheet and it would accumulate the results for her instantly as she entered them.

To do this I thought we could set up a spreadsheet as follows.

Sheet 1 contains the food details:
Col A is the name of the food.
Col B is the measurement (eg weight or ml)
Col C through Col L are the quantities of the relevant ingredients for the measurement entered in Col B.

Sheet 2 is where she enters details of what she has given him
Col A the name of the food
Col B the quantity given.
As she enters the name on sheet 2 in col A, all the details from columns C to L are inserted, factored by the measurement entered in Col B (relative to the measurement in Col B sheet 1)

EG Sheet one has 'Food A' with weight 100 in col B and a value of 300 in col C, nothing in the rest of the columns D to L.
On Sheet 2 she enters 'Food A' in column A, and the value '30' in column B, so automatically a value of 90 is entered in col C (ie 300/100 x 30). Zeros are put in columns D to L on sheet 2 as no value is entered in those columns on sheet one.

I trust this all makes sense.

It may be that there is a simple way to do this that I have missed in Excel already, or it may be a very difficult procedure to implement, but either way, any guidance would be greatly appreciated.

[TMS]

I trust this all makes sense.

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook. Don't upload a picture when you have a workbook. None of us is inclined to recreate your data. Upload the workbook and manually add an 'after' situation so that we can see what you expect. In addition clearly explain how you get the results.

To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'.
To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.

On this page, below the message box, you will find a button labelled 'Manage Attachments'.
Clicking this button will open a new window for uploading attachments.

You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
Alternatively you can click the Attachment Icon to open this page.

To upload a file from your computer, click the 'Browse' button and locate the file.

To upload a file from another URL, enter the full URL for the file in the second box on this page.
Once you have completed one of the boxes, click 'Upload'.

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[pewe]

Attached is an example of what I need the worksheets to do.

I have attached 2 copies (one in 2003 format and one in 2010 format).

I think the information on sheet 2 should show what I need to do, if not and you feel inclined to assist, please ask any questions necessary to clarify.

[TMS]

On Daily Portions Sheet, in cell F2:

=IF(LEN($A2),INDEX(Foods!$A$1:$P$7,MATCH($A2,Foods!$A:$A,0),MATCH(F$1,Foods!$A$2:$P$2,0))/INDEX(Foods!$A$1:$P$7,MATCH($A2,Foods!$A:$A,0),MATCH("calories",Foods!$A$2:$P$2,0))*$B2,"")

Copy across and down.

You could use Data Validation to ensure that only valid foods are selected.

Regards

[TMS]

See the attached updated example to demonstrate the Data Validation and using the formula.


Regards, TMS

[pewe]

Thanks TM - your assistance is greatly appreciated.

There are a couple of problems I am having with the example you sent, and although I think I understand the principle you have used in the formula, I don't have enough experience to decipher it to work out where the problems might be.

The problems are -
1. Some calculated values are correct,
2. Some are not correct'
3. Some cells return an error.

I have attached an example with notes.

I also have a couple of questions regarding the 'Validation' - but I'll refer to those if we can sort out the problems first.

Thanks again

[TMS]

OK, fixed, I think, I was using calories rather than measurement ...

I've also used named ranges to reference the data on the Foods sheet, which should make it clearer, I hope.

See the amended example.


Regards, TMS

[pewe]

TM,
That seems to work a treat.
My personal thanks to you - and my sister sends her appreciation and thanks to. This will save her a lot of hassle (and work).

Thanks again.

[TMS]

You're welcome.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save