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?action=getinfo&userid=26330
View this thread: http://www.excelforum.com/showthread.php?threadid=396035

0
8/16/2005 6:00:51 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
751 Views

Similar Articles

[PageSpeed] 42

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?action=getinfo&userid=21059
View this thread: http://www.excelforum.com/showthread.php?threadid=396035

0
8/16/2005 11:50:17 AM
Thanks Brian,

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

The only way I can think of is to copy the whole sheet and pastespecia
"values" but then I would lose all the "pull" formula's. I just need 
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 los
formula's and I need the spreadsheet next year).

Thanks again

--
Innee
-----------------------------------------------------------------------
Inneed's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2633
View this thread: http://www.excelforum.com/showthread.php?threadid=39603

0
8/16/2005 11:03:31 PM
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?action=getinfo&userid=21059
View this thread: http://www.excelforum.com/showthread.php?threadid=396035

0
8/17/2005 5:51:04 AM
Reply:

Similar Artilces:

How do I bridge the gaps in data in (XL 2007) Area Chart
XL 2007 doesn't have the TOOLS-Options-Interpolate menu and choices. This goes for a line chart as well. Thx Click the chart to activate it In the Design tab of Chart Tools, click Select Data A dialog box opens, look at lower left corner for "Hidden and Empty Cells" button and click it You get three options: gaps, zero and connect. Alternative (works in all version of Excel); in the empty cell type =NA(). It will display as #N/A and will be ignored by the chart engine best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email ...

Outlook keeps prompting for logon..
User is on Outlook 2k3, latest service packs, updates, etc. Mailbox lives on Exchange 2007 (SP1). Everytime the user starts Outlook they're prompted to logon. I've disabled the logon prompt at startup option (and enabled it and then disabled it again), but it's still happening. I've tried it with cached mode on and cached mode off. I've tried it with offline mode enabled and disabled. I've tried deleting his profile and recreating it. I've tried switching to an RPC/HTTPS connection. Finally, i completely uninstalled Outlook 2k3 and installed Outlook 2k7. Not...

How do I get an automatic equal sign to begin the formula bar?
How do I get an automatic equal sign to appear in the formula bar in Excel for Microsoft 2003? This happened for me in the 2000 version and I could just click on it with my mouse pointer, but now all that appears is the function sign with no equal sign after it. It makes my job a lot more time consuming to have to type in the equal sign while building formulas. Please help, anybody? The = icon disappeared in newer versions of excel (xl2002???). You can add an = icon Tools|customize|commands tab|Insert category (Scroll down the list until you find the = icon) And drag it to your fav...

Help Old addresses keep coming up
When I go to send an email to some people their old email addresses keep popping up together with their current ones. I do not have them listed in contacts or in an address book. It is a minor annoyance. I am using Outlook 2003. It is a minor annoyance. How do I delete these old addresses? Where they show up is where I am entering the e-mail address on the item being e-mailed. Thanks You did not specify how you were entering the addresses. Presumably you are using autocompletion and they are...

Formula error since 10.5.6 update ...
Hi, I have been working with a spreadsheet for years without any problem of this sort. Since my recent 10.5.6 upgrade a part of a formula does not return a correct result. My problem seems to be related to the incorrect result of this part of my formula: TEXTE($N$5;"dd-mm-yy"). In this example, N5 is equal to "31-10-09". This formula should return : 31-10-09 It returns "dd-10- yy". Funny, the "31" is replaced by "dd" and "09" by "yy", while the month number is correct. Any idea with this is all about ? ...

Confirmation of receipt keeps trying to send and fails
Help! I received an email message that requested a confirmation that it had been received. I told it to send confirmation, but it failed when attemtping to send it and produced an error. I tried deleting the email message and everything in the draft items and Outbox, but every time I switch on Outlook the darn thing tries to send this email confirmation. It seems there is nothing I can do to stop it, even though I cannot see it! I have tried uninstalling Office 2003 and reinstalling it, but all of my settings are remembered as are all the old emails. I just cannot seem to get rid of it! ...

Simpler way to suppress missing data?
I created a sheet 8-10 cells where the user can fill in values. There are 20-25 cells containing the results of calculations based on those input cells. Initially, the input cells are blank. This causes various results in the derivative cells. Some of them get Value errors, some get division by zero errors, and others get zero. I want any cell that depends other cells to show a blank until all source cells have data. The best way I could find to do this is to replace a formula like =(B2*C2)+(D2*E2) with this monstrosity =IF(OR(B2="",C2="",D2="",E2="...

Access 2 Excel
I exported an annual worklist from an Access table to Excel. There is column for tasks, one for frequency (TD, D, TW, W, TM, M, Q, SA, A twice daily, daily, twice monthly, etc) and a column for each month. The month columns have the same coded data as the frequency colum depending on when the task is done. The problem is that I tried to make a pivot table to count how man tasks (by frequency type) are done each month. Excel is counting th empty cells as if they had data. Search and replace doesn't work. even tried replacing the blank data in Access with ~ and re-exportin it. Ex...

Keep Lines Together; Keep with Next
I'm working in Vista. When I select lines and click on "Keep Lines Together," the lines do not stay together. They stay together if I also click "Keep with Next," but I'm working on a booklet in which the pages are 1/4 of a letter-sized page, and a lot of space (4 lines) is left at the bottom and wasted if I also click "Keep with Next." Why won't "Keep Lines Together" keep the lines together? The project is a small, bound country club booklet, with names, addresses, phones, etc. Each entry might be 2, 3, or 4 lines. Any h...

"Bring to Front" Data Labels?
I have a chart that has lots of data labels, so many that some of the labels become partially obscured. I have a routine that runs whenever a label is clicked upon, and one of the things I'd *Like* to do is to mimic a drawing object "Bring to Front" function. Is this possible? The way the chart is constructed might help (or hurt) my chances: I am graphing a tree chart, instantiated as an xlXYScatterLines Chart, and each "node" in the tree is represented by a series with two points (node and parent node). Each series gets one datalabel (at the node, i.e. the f...

Data Design & Filter
I am using Vista and Office 7. I have data that is the analogous form of "Part Numbers" and "Locations". I have Col A with various "PartNos" and Columns B through K of "Locations". If Location1 has the specific part number, then the Location cell is "TRUE". If Location1 is missing the part, then the Location cell is "FALSE". I would like to use the dropdown filter to select the part number in ColA, and then show only those locations that are true. At present, when I select the part number, I see all locations with t...

Excel graphs
Hi, I'm trying to display two sets of data, with identical X-axis on the same Y-axis, but am finding it hard. I don't want 4 bars for each X value, but a graph with 4 X-catergories containing 2 bars each, then an identical x-axis with a further similar data set (i.e. 4 x 2 bars) - anyone have a clue how to do this? I've tried just making two graphs and deleting the Y-axis from one, but the X-axis never line up in something like Powerpoint when I put them together! Thanks! Forgot to say, this is for Excel 2000 bar graphs. "AndyB" wrote: > Hi, > > I'...

why do my pictures keep disappearing?
Although I have checked the box in 'pictures' any form of graphic or word art that I have inserted into my publication keeps disappearing! If I click in the general direction of the insertion the bounding dots appear, but no picture Are you sure you have view, pictures, detailed display checked? If so try updating your graphics driver... -- Mary Sauer MS MVP http://office.microsoft.com/ http://www.msauer.mvps.org/ news://msnews.microsoft.com "JG" <JG@discussions.microsoft.com> wrote in message news:57E101AC-A758-400A-9269-3B68B754CA0E@microsoft.com... > Altho...

how can I keep a tab in place?
I have workbooks with multiple worksheets. I would like to keep the summary tab in a fixed (first) position so that the user can easily, quickly find it without using the l<, <, >, >l (arrow) tabs. I use hyperlinks to get from the summary tab to the other tabs, and I know I can hyperlink tab back... but I'd rather not use the hyperlinks too much. Tools - Protection - Protect workbook (for structure) Or, create a custom button/macro that navigates to the summary sheet. -- Best Regards, Luke M "missy8892" <missy8892@discussions.microsoft.com>...

Advice required
I need to create some graphs from data that is derived from a long series of separate calculations on my source data. For aesthetic reasons, I would like to hide all the "interim" calculations on the spreadsheet, but I've noticed that as soon as I hide the interim data (i.e set the row height to zero), the detail on the graphs disappears. I presume that this is a design feature feature of Excel (I supoose I can just about accept the rationale for it), but does anyone know whether there's a way around this? Many thanks. CRS. Use white fonts -- Regards, Peo Sjobl...

formula... PLEASE HELP
Hi, I have what I think is a formula...on Sheet 1 I have a total $ amount for items sold. I typed in L3+M2 (the L column is profit and M is the total..NOW..on Sheet 2 I have the same thing for different items, I would like to take sheet 2's M column and add it to sheet 1's. Is this possible? Thank you soooo much. Rosie =L3+M2+'Sheet2'!M2 as an example -- Regards, Peo Sjoblom "Roelamp" <Roelamp@discussions.microsoft.com> wrote in message news:91EA3B4A-EB37-47E9-A734-FA0736F9C9E8@microsoft.com... > Hi, > I have what I think is a formula...on >...

Dependant Data Validation List
I want to create a column with a data validation pull down list, the list of which changes dependant upon a value in another cell. Does anyone know how to do this? -- Stabilos ------------------------------------------------------------------------ Stabilos's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28962 View this thread: http://www.excelforum.com/showthread.php?threadid=486901 Stabilos, have a look here, http://www.contextures.com/xlDataVal02.html -- Paul B Always backup your data before trying something new Please post any response to the newsgrou...

Copy data from multiple worksheets to worksheets in a number of other spreadsheets
I have a performance spreadsheet containing multiple worksheets, each worksheet holds performance data for an individual fund. Each of these funds has its own spreadsheet containing static information about the fund. I need macro that will copy the performance data from the performance spreadsheet to each individual fund spreadsheet. The cells to be copied are the same for each worksheet (B2:D65). Each worksheet in the performance spreadsheet is named with a fund code, this fund code is also the name of the respective worksheet in each fund spreadsheet. I need the macro to copy cells B2:D65...

Outlook 2003 keeps asking for password for POP3 account
I am running WinXP Pro and Office 2003. My Outlook 2003 keeps popping up intermittently a pop-up box requesting me to re-enter the POP3 account password. Now, there is nothing wrong with the password already entered (check box to save password), so all i am having to do is click OK, but it is really annoying. Does anyone know what i can do to stop Outlook from doing this? Thanks for your help. ...

How do I keep the home tool bar on top
An hour ago the home tool bar stoped being displayed. I can restore it but every time i hit a key the tool bar disapers. How do I keep the tool bar on top while I am typing. You've minimized the Ribbon. Double-click any tab on the Ribbon to restore it. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "RobertTaylor" <RobertTaylor@discussions.microsoft.com> wrote in message news:081A95C4-76B9-47E2-A4E6-83A080233AD0@microsoft.com... > An hour ago the home tool bar stoped being displayed. I can rest...

Reading Excel data from web pages
How can I read Excel cells' contents from a web page (HTML, ASP, etc)? ...

Export Data to Excel
Hi All, Please help me out in the following scenario. 1. We have problem in exporting Marketing List data to excel.Dynamic Pivot table or Direct both the options are failed to export the data. It throws an exception like "SQL Server Connection failed/access denied" and when i try to use SQL Authentication , the same error continues. Note: the Client Machine is in the same domain. Windows authentication works perfectly when i test it in DSN Creation . And If we need to work in the Machine which is in the workgroup instead of Domain, Can we able to export the data? ...

Cleaning Messy Data
I’m trying to fix a messy imported data set. The CLEAN formula removes unprintable characters, but also removes separation between multiple words. Is there a way to replace unprintable characters with spaces or delimiters such as commas? Thanks. -- Jay You could use the SUBSTITUTE formula to replace some text (eg character code 160) with some other text (eg space or comma, or both). Further details can be found in Excel Help. Hope this helps. Pete On Dec 11, 4:16 pm, Jay <J...@discussions.microsoft.com> wrote: > I'm trying to fix a messy imported data set. The CLEAN fo...

Adding rows of data to 2 worksheets that are linked with formulas to eachother...
I have 2 worksheets. Sheet 1 contains address information for names of people we contact (including the names). Sheet 2 contains some of the contact information, including about 15 columns of dates that aren't included in sheet 1. I need to add in about 100 rows of new data that isn't included on either sheet. My problem is that the data that is the same on sheet 2 is linked to the cells in sheet 1. Rows in sheet2 read (=Sheet1!A2)(=Sheet1!B2)(=Sheet1!C2)(=Sheet1!G2),etc (they skip some colmns from Sheet 1) for every row. How do I add the extra 100 rows in without mixing up the d...

How to keep userform always on the top of all windows application
Hi All!! Is there some that when I execute an excel user form, it *remains o the top * of all the windows applications that are currently running until & unless I, manually, minimize or close it. Regards, Jat -- JAtz_DA_WA ----------------------------------------------------------------------- JAtz_DA_WAY's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2668 View this thread: http://www.excelforum.com/showthread.php?threadid=40059 You may get an answer to your question--but it'll involve both making the userform always on top plus adding those minim...