Keeping data without losing Formula's
 

I have a worksheet (named monthly) with a column of data under a certain
month (which will change each time data is entered - stocktake). However
this column needs to be edited each month and has formula's in it so it
will work (not always the same data in the column).

I decided to put this data into a yearly sheet under the particular
month. The only way I can do it at the moment is to copy and paste
special. How do I (or is there a way to) put this data in a sheet that
has all the months but under the month that is signified.

In other words I don't want formula's in the yearly sheet (can't use
vlookup) as I want to keep this sheet with all the data in it!

I'm sure it's really simple but I have read the VBA help files and they
don't help me. :confused:


--
Inneed
------------------------------------------------------------------------
Inneed's Profile: http://www.excelforum.com/member.php...o&userid=26330
View this thread: http://www.excelforum.com/showthread...hreadid=396035

It's always a problem to write a 'push' option into a system that is
basically a 'Pull' system, ie, the formula is normally in the receiving
cell.

Does you reference to VBA mean that you could add a button, and use a
cell on your current worksheet to indicate the columns in the Annual
sheet into which to put the data, something like if you put F (for
column F on Sheet2) into cell A1 and

cn = Range("A1").value
Range("Sheet2!" & cn & "10") = Range("a10").Value
Range("Sheet2!" & cn & "11") = Range("b10").Value

or a row number someting like: A1 contains 20

rw = Range("A1").value
Range("Sheet2!a" & rw).Value = Range("A10").Value
Range("Sheet2!b" & rw).Value = Range("B10").Value

to copy A10 and B10 to Sheet2 cell F10 and F11 as a column thing, or
to copy A10 and B10 to Sheet2 cell A20 and B20 as a row thing.

Your totals would then copy to Sheet2 (your annual sheet etc) whenever
you pressed the button, and would use the value from A1 (or other
designated cell)

Is this in the right direction?


Inneed Wrote:
I have a worksheet (named monthly) with a column of data under a certain
month (which will change each time data is entered - stocktake). However
this column needs to be edited each month and has formula's in it so it
will work (not always the same data in the column).

I decided to put this data into a yearly sheet under the particular
month. The only way I can do it at the moment is to copy and paste
special. How do I (or is there a way to) put this data in a sheet that
has all the months but under the month that is signified.

In other words I don't want formula's in the yearly sheet (can't use
vlookup) as I want to keep this sheet with all the data in it!

I'm sure it's really simple but I have read the VBA help files and they
don't help me. :confused:


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=396035

Thanks Brian,

I can make it a "pull" system but I want to keep the monthly column of
data under that particular month in the annual sheet. Because I only
have the 1 column that changes in the other sheet.

The only way I can think of is to copy the whole sheet and pastespecial
"values" but then I would lose all the "pull" formula's. I just need a
way to "lock" the column in place (probably by pressing a button,
however I want to stay away from copying and pasting as I will lose
formula's and I need the spreadsheet next year).

Thanks again.


--
Inneed
------------------------------------------------------------------------
Inneed's Profile: http://www.excelforum.com/member.php...o&userid=26330
View this thread: http://www.excelforum.com/showthread...hreadid=396035

I take it from your reply that you are not familiar with VBA code, which
can make it 'push'

Try this simple test:

In a test workbook, put the letter G in cell A1 and a monthly figure in
cells A10 and B10

In your Excel, click on View, Toolbars and tick Control Toolbox

The first icon is 'Edit Mode' (when you click, it becomes 'Exit Edit
Mode')

In Edit mode, click on the Button icon on the same bar, then click into
a cell where you want the button to be (it is moveable later)

Rightmouse on this button, and select View Code, and you will be taken
to the code in the VB editor (where your macros are stored etc)

put the code

cn = Range("A1").value
Range("Sheet2!" & cn & "10") = Range("a10").Value
Range("Sheet2!" & cn & "11") = Range("b10").Value

into that, and close and Exit Edit Mode

in A1 put the column name that you want to save to, then click the
button.

Your figures are moved.

Change the A1 letter to any column and press button again, figures are
copied.

That should provide what you asked for, and you can set the letter (in
A1 or elsewhere, it's collected in the first line of code) by any means
you wish, ie A1 can be the result of a formula.
A10 and B10 are just two test cells, you will need to adjust the names
to suit your own needs, but let me know if you have troubles.

in Edit Mode, the Properties of the button include BackColour and
Caption which can be set to your preferences.








Inneed Wrote:
Thanks Brian,

I can make it a "pull" system but I want to keep the monthly column of
data under that particular month in the annual sheet. Because I only
have the 1 column that changes in the other sheet.

The only way I can think of is to copy the whole sheet and pastespecial
"values" but then I would lose all the "pull" formula's. I just need a
way to "lock" the column in place (probably by pressing a button,
however I want to stay away from copying and pasting as I will lose
formula's and I need the spreadsheet next year).

Thanks again.


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=396035