Prep for new day/data

**bobbylan** · 11-22-2008, 01:29 PM

I am not too familiar with Excel and have spent many hours only to not be able to figure things out. I have 4 rows of data that contain ten days worth of data, have attached the file. So it is ten columns wide. I have the old data on the far left so that I can click delete and shift left and enter the new data on the far right. The problem is it's a bit time consuming for one and as I do the shift left it is also shifting my formulas as well. I have the formula set to calculate column C thru L, after I delete and shift left it then says C thru K and have to manually change each one every time. I found this lock cell feature to try and freeze the cell where the formula is, can't get that to make a difference, have also moved the formula on a seperate row altogether doesn't help.

Having said that someone gave me a file a while back and has this button that says prep new day, it doesn't seem to work however, but wondering if something like this could be created. The click of one button to delete the far left column and shift left for all 4 rows of data by one click of the button. Is anything like that possible and if so where can I learn how to create such a thing? Thank you very much.

**stanleydgromjr** · 11-22-2008, 03:49 PM

bobbylan,

4- after the shift left need to edit formula change K to L

On your attached workbook, Sheet1 does not contain any formulae in columns K and L.

There is a SUM formula in column M.

And, there is no "button that says prep new day".

Please attach another workbook with Sheet1 complete with formulae and button, and on Sheet2 manually show the change from Sheet1 to Sheet2.

Have a great day,
Stan

**bobbylan** · 11-22-2008, 10:00 PM

Thanks for responding so quickly Stan. Attached is the one I got from someone with this prep new day button, but I can't get it to do anything. Perhaps I mispoke. The formula in M says to calculate C thru L, when I do the delete and shift left it then says C thru K, I'm trying to get it to always remain C thru L, but don't know how to do that. Thanks.

**protonLeah** · 11-22-2008, 10:37 PM

An alternative:
Click cell D4 and drag down to cell L16. Move the cursor to the left border of the selected cells ( the cursor will change from the + to an arrow)
Drag the selection left one column to C, this will move nine day's worth of data one column to the left leaving column L blank...

Is this your intent??

**stanleydgromjr** · 11-22-2008, 11:11 PM

bobbylan,

I have your two workbooks, one Excel 2003?, and one Excel 2007.

There is not code attached to the command button in the Excel 2003 file.

Please attach another workbook (in Excel 2007?????) with Sheet1 complete with data, and formulae (I will add the command button), and on Sheet2 manually show the change from Sheet1 to Sheet2.

Have a great day,
Stan

**bobbylan** · 11-24-2008, 05:18 PM

Excel 2007 is fine, I just got it recently so the other stuff was created in an older format. I had someone create this for me, so I'm afraid I don't understand some of your questions. The formula shows up I believe when you click on any of the pink boxes. As far as showing changes from sheet 1 to sheet 2 I have no idea what you're asking there.

**bobbylan** · 11-24-2008, 05:26 PM

I just tried Proton's suggestion and it does the same thing changes the formula from C thur L to C thru K

**bobbylan** · 11-29-2008, 01:37 PM

=average(c4:l4)

=sum(c7:l7)

=sum(c10:l10)

=average(c13:l13)

=average(c16:l16)

**stanleydgromjr** · 11-29-2008, 07:49 PM

bobbylan,

Here you go.

See the attached workbook "Prep for new day data - bobbylan.xlsm", with the command button "prep new day".

Have a great day,
Stan

**bobbylan** · 11-30-2008, 11:17 PM

Thank you so much Stanley that is exactly what I was looking for. You have made my life much easier. I don't have to worry about screwing things up and now I can save some time, not only daily but when I have to run about 6 months worth of past data. I very much appreciate you taking the time to do this.

At first it didn't work then I realized I needed to probably turn something on. When I open it, it says Security warning some active content had been disabled. I clicked enable and then it works. I then saved it, but when I re-open I have to enable again, is there a way to set it so I don't have to enable each time? If not how else can I access this enable panel? If I click somewhere other than here at first the message disappears and the only way for me to get it again is to close out and re-open. Thanks again.

**bobbylan** · 11-30-2008, 11:56 PM

One other thing and I will be out of your hair. I believe this is a simple one. In my M column where it gives me my final values. This only pertains to the ones that are =Average. How do I get it to automatically round to the hundredth, now it goes to the thousandth? Now it will say = 1.749, would like it to do the rounding and say = 1.75 if possible.

**stanleydgromjr** · 12-01-2008, 12:58 AM

bobbylan,

You have made my life much easier. I don't have to worry about screwing things up and now I can save some time, not only daily but when I have to run about 6 months worth of past data. I very much appreciate you taking the time to do this.

For my own records, how much time do you think that the new macro will save you per day/week/month?

If you are still having problems with opening the attached workbook, you could open your original workbook, and copy the following code into it.

Right click the sheet tab you want the code in, and click on View Code. Copy the below code, and replace the original "CommandButton1" code with it.

Please Login or Register  to view this content.

See the attached new workbook "Prep for new day data - bobbylan.xlsm".

Have a great day,
Stan

**bobbylan** · 12-01-2008, 10:49 AM

Daily not such a big deal, but probably 5 minutes or so, as you know I used to click shift delete then edit the formula and do that for all 5 of them, then I would click on the formula for each one to make sure I edited properly. The real time savings is that I have to go back 6 months and do every single day, each column represents a day worth of data. 5 minutes per day times about 150 days that's the savings! Thank you very very much once again Stan. I run a service at www.spreadthetrend.com If I can be of any help please let me know.