Attempting to create a formula/code to have excel automatically move a column over

So first thing first, new to the forum, not so new to excel, but this is my first job using it. I made a spreadsheet that will allow me to input sales of scratchers (based on the number in the stack), and compare it to the previous day's numbers, giving me a total $ amount of sales of scratchers. My question is this, is there a way to have excel move column C to column B, leaving column C clear by changing the date at the bottom of the page? I don't know how clear of an explanation this is but if it's not i can link the file. Any help would be appreciated, I work with some complete morons that cant use a calculator to add/subtract basic numbers so I need to make this sheet as easy to use as possible.

P.S. - I have attached a before and after file of what i want to happen with this. Thanks again

Hello & Welcome to the Board,

Please take a few minutes to read the Forum Rules about thread titles and then amend your title accordingly.

After this, try posting a sample workbook with a before and after of what you want to achieve.

Just to be clear, in cell B27 you have a date and when you change that date you want the contents of column C moved to column B and column C cleared.

If this is correct, will the sheet be more than the current 19 rows? If it will I would suggest we move the date to some place else on the spreadsheet just to make life easier.

Give this a try...

Make sure you tell those moron's you stayed up late building this

Yes it is possible to do just about anything, but, we need to set the ground rules as clearly as possible.

How do you want the numbers saved? To another sheet so you can just refer to them by date? Do you really need them to come back into the table of data or is the referencing to another sheet fine?

With the date at the bottom you change, when do you and don't you want the table to change? Whether there is a data validation box with dates or somebody manually enters a date the macro will fire. Do you only want to have the macro fire when they change the date or how?

This needs to be thought out.

Okay let's give this a whirl for starters.

Everything right now is blank. Enter in Today's totals which we are calling the 24th. Once you enter the totals for today press the transfer button which will take those totals for copy them to sheet tab data.

Next change the date to the 25th.

Macro will move today's totals to col B and clear col C

Now select the 24 again and the totals for the 24th should reappear.

If you go to the 26th it will not work right, but let's just start with this and see how it goes.

I'm messing around with the macro, trying to get it to copy data from both column C and column B, so that if i transfer data on say the 24th, in both column, and go to the 25th, i can go back to the 24th and have both column as they were when i entered them.

Ok, worked on this a little more and here is what I came up with.

Notice three tabs:
1) for sales
2) for data storage
3) for data validation

Right now B23 only has one date >> 24 June 11

Enter data for today in col C >> select transfer data

Tomorrow when you open the file the next date will be stored on the data validation tab so now two dates will show in the data validation B23

Change the closing date to >> 25 June 11

Data from col C will be moved to col B and col C cleared

Enter data for today in col C >> select transfer data

Now if you were to select 24 June 11 the 24 June 11 data would be pulled into col C.

For the next days all the process should work the same.

Let me know what you think.

Simplified...

This is a feature I do not use, but I believe you need to add the file as a trusted location.

I do not have 2003, but maybe this is a start.

http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

One method I use quite often is to force macros to be enabled.

In other words, when the file opens one sheet will show in-which you put a message, "macros must be enabled to use this file".

Once the macro is enabled the sheet will open properly.

You're welcome...glad it has all worked out for you

If you are satisfied with the answer provided, please don’t forget to mark the thread as solved.

How to mark a thread Solved
Go to the first post
Select
-- Edit
-- Go Advanced
-- Below the word Title you will see a dropdown with the word No prefix
-- Update to Solved
-- Save