how do i shift data but have formulas stay in place?

I am trying to make a simple form to track my body weight and measurements, but knowing next to nothing about Excel I am having trouble figuring out how to get it to do what I want.

Column A contains the labels of what measurements are going to be taken (i.e. Neck, Right Bicep, Left Bicep, ect...). Column B will be where the coressponding data will go. I want there to be a button that shifts all the columns, starting with B to the right so that B would now be free to enter the most current values. I am trying to set it up so that B1 shows the current date, but when I click the button that moves everything over, I want whatever the value is in B1 at the time to moved over so that I can look at the columns and see when the data was recorded. I would enter the date manually, but at some point, I would like to be able to bring up a chart that shows progression and those dates.

any ideas?

sorry it took so long to get back in here, i have had a crazy past week.


as far as the oldest numbers being farthest to the right, instead of thinking of it as a chart showing progression, think of it as a blog or news site where you want the most up-to-date relevant stuff at the beginning but you can always scroll along and see yesterdays news if you need to.

the reason i am going with the vertical layout is that i want to be able to load this into Excel Mobile on my phone and i will only be able to see 3 or 4 columns, but many more rows. so i need to put the longer of the two vertically and have the most recent info on screen without scrolling. this is also why i want a button to insert a new column, since i am already working within limited viewable space.

is this possible?

Is this what you are looking for?

ChemistB

i want to put a formula or something that shows the current date. maybe even better (if this is possible), there could be something like when you pay your bills online, where there is just a little calendar icon that you click and it pops a calendar of the current month, and when you click a day, it inserts that date into the field...anyway, when you click the "New Data" button it needs to shift whatever the current values in column B are while keeping the formulas in place. so the date formula (or calendar icon) need to be fixed to B1.

I do not have the skill to set up the calender option. Try searching the forum maybe or asking in the programming folder. I modified the macro so that it requests a date from you. This has the advantage of flexability (say you are putting in yesterday's measurements). Also in the macro, I put in the code to have it automatically set it to todays date and I disabled these lines (remove the ' from in front of those two lines to inable and delete or disable the inputbox line). If you want current date to alway show, you can insert a blank row before your table, fix the ranges in the macro, and put in the formula =TODAY() in A1.

Hope this helps.

ChemistB

thanks for all your help guys, i think i got things working how i want them now. i found out that Excel Mobile can't use macros, so i would have to enter this manually on the phone, but in Windows, its all working fine.