Complicated Excel Formula

emily2309

Board Regular
Joined
Nov 17, 2009
Messages
51
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

Legend
Joined
Jun 19, 2002
Messages
63,651
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

Code:
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

Board Regular
Joined
Nov 17, 2009
Messages
51
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..
 

emily2309

Board Regular
Joined
Nov 17, 2009
Messages
51
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?
 

emily2309

Board Regular
Joined
Nov 17, 2009
Messages
51
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 :)
 

Forum statistics

Threads
1,082,569
Messages
5,366,360
Members
400,886
Latest member
Fchel

Some videos you may like

This Week's Hot Topics

Top