Complicated Excel Formula

[emily2309]

For my spreadsheet, I am designing a program where the user can enter what they have eaten every day, and it will then give the results at the end of the week. However, my input table looks very cumbersome; it is very long incase the user needs to input a lot of food. What I want to be able to do is to have the table as only one row, allow the user to input data, which then copies onto the hidden calculations sheet I have. The user will then press a button which will clear the data from the input table, and therefore have a clear row, where they can input more food they have eaten. However, the data must stay on the hidden calculations page. Also, when they enter the next piece of data, this must go onto the cell underneath the previous one on the hidden calculations sheet.
VERY complicated - and will probably be a lot of work.. but anyone got any ideas?? I'm so stuck!!!
Thankss

 

[VoG]

Hello and welcome to MrExcel.

You will need VBA code for this. Press ALT + F11 to open the Visual Basic Editor and select Module from the Insert menu. Paste into the white space on the right

Sub Transfer()
ActiveCell.EntireRow.Cut Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub

Change Sheet2 to the name of your calculation sheet then press ALT + Q.

On your input sheet add a button from the Forms toolbar and when prompted assign the Transfer macro.

Then click anywhere in your input row and click the button.

 

[emily2309]

This does the right sort thing, except it takes the whole cell, so it takes the formatting and everything with it, which i need to stay there so that the user can enter more data. Also, it does not paste into where i want it..

 

[VoG]

Where do you want it pasted?

 

[emily2309]

into a table which uses vlookups and things. is there a way i can attach my spreadsheet on here so you can see it, then you will understand it more?

 

[VoG]

On reflection I think that you should be using a User Form. see http://www.contextures.com/xlUserForm01.html

 

[emily2309]

Thank you, but i've worked it out now using a macro, if statements, count if statements, the go to function and the concatenate function, thank you for your help though