newbie seeks excel help - please!!

hi
hope someone can help, i have a spreadsheet that has daily sales, monthly 
sales and yearly sales which (forgive my explanation i am a complete newbie 
lol) when i enter a new value in the daily sales, i want the monthly sales 
to increases but also add the previous value, and the same with the yearly 
cell, if you know what i mean.

A1                    A2                    A3
daily sales    monthlysales    yearly sales
    1                       2                    3

at the moment im trying =A1+A2 for cell A2, and =A2+A3 for cell A3, but i 
keep getting a circular reference message,
ive tried going to tools>options>calculation and doing the iterations but to 
be honest i havent got a clue, it seems such a simple formula, ive looked on 
the net and macros have been mentioned.

any help would be really appreciated

thanks

tracie 


0
6/23/2005 11:01:59 AM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
456 Views

Similar Articles

[PageSpeed] 31

Using V BA


Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo ws_exit:
    Application.EnableEvents = False
    If Target.Address = "$A$1" Then
        With Target
            .Offset(0, 1).Value = .Offset(0, 1).Value + .Value
            .Offset(0, 2).Value = .Offset(0, 2).Value + .Value
        End With
    End If

ws_exit:
    Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



-- 
 HTH

Bob Phillips

"earthgirluk" <mattandtracie26@aol.com> wrote in message
news:%23PA96L%23dFHA.3616@TK2MSFTNGP09.phx.gbl...
> hi
> hope someone can help, i have a spreadsheet that has daily sales, monthly
> sales and yearly sales which (forgive my explanation i am a complete
newbie
> lol) when i enter a new value in the daily sales, i want the monthly sales
> to increases but also add the previous value, and the same with the yearly
> cell, if you know what i mean.
>
> A1                    A2                    A3
> daily sales    monthlysales    yearly sales
>     1                       2                    3
>
> at the moment im trying =A1+A2 for cell A2, and =A2+A3 for cell A3, but i
> keep getting a circular reference message,
> ive tried going to tools>options>calculation and doing the iterations but
to
> be honest i havent got a clue, it seems such a simple formula, ive looked
on
> the net and macros have been mentioned.
>
> any help would be really appreciated
>
> thanks
>
> tracie
>
>


0
phillips1 (803)
6/23/2005 11:46:20 AM
You need to do this with a macro.right click sheet tab>view code>copy/paste
this>modify to suit>SAVE

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
 If Target.Address = "$A$1" And IsNumeric(Target) Then _
 Range("a2").Value = Range("a2") + Target
    Application.EnableEvents = True
End Sub

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"earthgirluk" <mattandtracie26@aol.com> wrote in message
news:%23PA96L%23dFHA.3616@TK2MSFTNGP09.phx.gbl...
> hi
> hope someone can help, i have a spreadsheet that has daily sales, monthly
> sales and yearly sales which (forgive my explanation i am a complete
newbie
> lol) when i enter a new value in the daily sales, i want the monthly sales
> to increases but also add the previous value, and the same with the yearly
> cell, if you know what i mean.
>
> A1                    A2                    A3
> daily sales    monthlysales    yearly sales
>     1                       2                    3
>
> at the moment im trying =A1+A2 for cell A2, and =A2+A3 for cell A3, but i
> keep getting a circular reference message,
> ive tried going to tools>options>calculation and doing the iterations but
to
> be honest i havent got a clue, it seems such a simple formula, ive looked
on
> the net and macros have been mentioned.
>
> any help would be really appreciated
>
> thanks
>
> tracie
>
>


0
Don
6/23/2005 11:54:09 AM
Reply:

Similar Artilces:

Newbie, accnts. payable & accnts.receivable sample DB
Hi guys, I�m running access 2003, windows xp pro SP2 I've been commission in my work to do a small application to track and report the accounts payable and accounts receivable movements, I'm not an accounting savvy person :(, does anyone know of a link or sample access database that can help me with the basic structure of the tables and reports?, any help would be greatly appreciated. Thanks ...

Pasting HTML table with feet/inches column, excel changes it to da
I am copying a web page table with a roster into excel. The table includes one column with height x-zz where x is feet and zz is inches. Pasting to excel (there is no paste special option) this column converts to a date where 6-1 becomes 01-Jun, 5-10 becomes 10-May, and 6-0 becomes Jun-2000. 1) Is there a way to paste this information so that it is not converted to a date? 2) Is there a way to easily convert the date back to feet-inches? (It would be trivial if the even feet weren't changed to a different year.) 3) What is the easiest way to get around the problem? The best I have c...

Need help with http://support.microsoft.com/?kbid=212686
I am trying to use the KB article at http://support.microsoft.com/?kbid=212686 to increment an Invoice number automatically. I am confused about where they want me to put the code. They make refference to storing the code in a "Protected form template" I don't know what this is. Can someone please help Thanks That's for Word isn't it. Try John's solution at http://www.mcgimpsey.com/excel/udfs/sequentialnums.html -- HTH RP (remove nothere from the email address if mailing direct) "John" <anywhere@s.com> wrote in message news:OwiWKS0pFHA.1948@...

Excel File #2
unable to open xls file from shortcut or explorer will only open from withing excel I have changed the options / general / ignore other apps still the same issue thanks in advance Sometimes one of these works: Tools|Options|General|Ignore other applications (uncheck it) --- or --- Close Excel and Windows Start Button|Run excel /unregserver then Windows Start Button|Run excel /regserver The /unregserver & /regserver stuff resets the windows registry to excel's factory defaults. ==== You tried the first, how about the second??? Mj wrote: > > unable to open xls fil...

X axis labels won't line up under corresponding column data
Only way I can get dates (year 1998, 1999 and on etc) to fall under annual income columns is to horse around with spacing, adding and subtracting spaces in the category axis drop-down box in Chart Options. Seems you can't even use the same number of spaces between each because if you add between two of them, adding the same number of spaces doesn'tnecessarily work between the next two. I have about 8 charts to mess with and can't find an easy way to do this. did you try this righclcik x axis, clcik <format axis> choose <scale> UNCHECK <value(y) axis crosed be...

Scrolling Problem in EXCEL Visual Basic
I recently upgraded to XP, EXCEL is 2000. The scrollwheel doesn't do anything for an object (userforms, modules) in VB. It scrolls in all other windows in VB (Properties, Projects and Object Browsers). Don ...

goal seek wont calculate an accurate value past 3 decimal places
I have a traditional situation in which to use goal seek. I need the value in one cell to equal a certain value by changing another cell. But the value I want it to go to is very small like 0.04459. So when I click calculate goal seek gets the first 3 decimal places correct, but the remaining ones are off. So does goal seek not calculate past 3 decimal places or am I doing something wrong? Any help would be appreciated. Thanks. Joe Hi Joe Check column widths for starters and then have a look at cell formatting for number of decimal places. Mine works fine, Office 2000 HTH Michael &quo...

Out of Office set-up with an exchage server help wanted
Hi I am trying to configure the Out of Office Assistant in Outlook 2000 & 2002. i don't use an exchnage server. Does anyone have any links to sites with step-by-step details on setting this up and creating an oft template? Sean Did you start this quest by reading http://support.microsoft.com/default.aspx?scid=kb;en-us;248830? -- Neo [MVP Outlook] Due to the Swen virus, all e-mails sent to this account will be deleted w/out reading. "Sean" <fake@fake.com.au> wrote in message news:hmcb10h0ia7adv4f5v2u07aaaqa3o0gjbq@4ax.com... > > Hi > > I am trying to...

Cells User Select Locked after upgrade to Excel 2002
We have several Excel spreadsheets that were created and protected in Excel 97 and 2000. Many of the wks are forms used by personnel for submitting expense reports, planning travel, etc. Recently we migrated all of our users to Office XP (Excel 2002). Now when a user opens one of the spreadsheets created and protected in Excel 97/2000 in Excel 2002, enters data and then saves and closes it, when they re-open it all of the previously selected cells are un-selectable by the user. If the user just opens one of the wks and does not make any changes and then saves it, when they reopen it the ce...

Adobe to Excel
Is there a way to take a .pdf file that started as a excel file and convert it back to Excel. I am not the creator of the .pdf but I do have Adobe professional 8 and Excel 2007. Thank you. Hey Try program "Able To Extract". Worked wonders for me one time- it didn't even have any problem, although there were pages and pages of data in pdf file. Best regards ...

Very slow Excel
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Has anyone found the fix to sort out our very very slow Macs running Excel? <br><br>It's driving us all crazy!! <br><br>Come on microsoft jenkinstoby@officeformac.com wrote: > Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Has anyone found > the fix to sort out our very very slow Macs running Excel? > > It's driving us all crazy!! > > Come on microsoft Hi, This is a forum of Office users, not Microsoft employees. Fix step 1: Make sure Office updates and OSX u...

help corrupt file or not / which excel to use
Hi - I am using excel 97 and have a roster spreadsheet. I am the only one who uses it and in of the formulas used ( put in numerous cells ) the reference cell numbers have changed. the back up of the file last week have the original and correct values whilst the up to date one have changed. they have not been changed manually and are not related to other cells ? is it corrupt ? are the newer excel programs a lot more stable andrewm -- andrewm ------------------------------------------------------------------------ andrewm's Profile: http://www.excelforum.com/member.php?action=ge...

Excel Menu Problem
Have no idea what happened. Using Excel 2003. Now, I don't have a File or Edit menu. I've tried right clicking on the menu bar and selecting customize but that doesn't help. Iv'e tried reset, but nothing. 've been able to add some the File and Edit features to the View menu (which is now my first menu), but printing is gone (among other things). Now I have these menus: view, insert, format, tools, data, window, help, Adobe PDF. On rare occasion, the File and Edit menus appear (on start up of Excel) but then disappear. I'm using XP SP1. THANKS!!! RON Search for ...

Visio Newbie
Hello all, this is my first time using Visio for drawing a floor plan and I have a quick question. Yesterday, (Using a tape measure) I mapped out the floor plan of an office my company might be moving into. I wrote everything down in Inches. Is there a way once I get a room shape kinda close to teh measurements, that I can manually type in teh length for each wall? if not in inches, at least in feet. For example, I have one wall that is 154 inches ( 12.83 ft) Then the next wall is 242 incehs( 20.1 ft) The opposite wall of the first measurement is 288 inches, third wall 100 inches, then 13...

MFC memory leak, + return code help
Hi, I don`t use MFC often so maybe I`m asking a dumb question. I started noticing VC++6 reporting a memory leak when I ran the program in debug mode. Detected memory leaks! Dumping objects -> {27} normal block at 0x009A04C0, 33 bytes long. Data: < C > 00 43 00 CD CD CD CD CD CD CD CD CD CD CD CD CD {26} normal block at 0x009A0510, 40 bytes long. Data: < |L > 14 7C 4C 10 16 00 00 00 00 00 00 00 00 00 00 00 Object dump complete. Strange thing is I`m getting the same error in many of my MFC programs. So I tried removing parts of my code bit by bi...

Military Time in Excel
I would like to create a timetable in Excel with the format in the 24 hour clock without the colon, which I believe is known as military time. I am leaving the colon out for a number of reasons, aesthetic and to speed up entry. I wish to enter the time, for example, 8:30 am as 0800 and have Excel interpret it as a time, as these will be graphed, so need to be interpreted as time, and not plainly a number. At the moment Excel simply shows "800" when I type 0800. I have tried formatting all the cells as Custom "hhmm" instead of "hh:mm", but when ...

Linking Word data to a Excel spreadsheet
New Word user here. Question: Is it possible to link data that is entered into a "fillable form" field, so that the identical data is ALSO entered simultaneously into a specified Excel cell? Hi MBR, Yes, via vba (The macro language Word & Excel use). However, it's not something a new user is likely to find easy to set up, unless already fairly well experienced with vba. -- Cheers macropod [Microsoft MVP - Word] "MBR" <MBR@discussions.microsoft.com> wrote in message news:CF3ED231-5FCA-4F2B-B651-FA5B6276A4F7@microsoft.com... > New Wor...

Formatting Dates in excel
I have about 2000 rows (x4 in columns) of dates in 2 formats and they are "0/00/00 and 00/00/0000". How can I quick fix this problem into just one format for all like this way "00/00/00"? I tried using the formula and it did not help at all and the other way was for me to use "format cells, category-date, type-00/00/00”. Then, after I have to manually go into each cell and double click it and that the only way it will format it. Please help there must be an easier way to fix this problem. Try Data>Text To Columns and tab through to the third sectio...

Reorganize excel listing
I need to develop a product/order list from an existing list in a different format. I have attached both. Is there VBA code that can do this for me.?? CustNo P1 P2 P3 P4 P5 P6 P7 A1234 15 18 A3245 10 B3456 D3459 23 35 From the above sheet, I need code to create listing as follows: On the first sheet the field names P1,P2,P3…..are product code CustNo ProdCode Quantity A1234 P1 15 A1234 P3 18 A3245 P5 10 D3459 P2 23 D3459 P7 35 Thanks for any ideas. To reorganize the data, you can use the "unpivot' technique described by ...

Is there a way to unsave a file in Excel?
I accidentally replaced a file by clicking on the wrong file to save as. I would really appreciate it if there is a way to resolve this issue. Sharon, Not unless you have a backup, if not on a disk maybe form an e-mail you sent, if on a network check and see if the server backups up itself at some time. -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Sharon" <Sharon@discussions.microsoft.com> wrote in message news:F...

key combination help!
Hi to everyone in N.G. I would like to known if there is a way to assign keyboard shortcuts, like Ctrl+G or Ctrl+F3, by VBA code? This is my code: ----------------------------------------------------------------------- Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer) If KeyCode = vbKeyTab Then Screen.ActiveControl.Text = StrConv(Screen.ActiveControl.Text, vbProperCase) End If End Sub ------------------------------------------------------------------------- Private Sub Form_Load() Me.KeyPreview = True End Sub ---------------------------------------------------...

Excel #148
I have a coumn of data. I want to tranpose it to a row (I know how to do this) AND reverse the order of the data. In other words...I want the last value in the column to be the first value in the row. ...

Importing excel symbols
I have a linked excel file that has text type with symbols. The symbol shows up in my report as a text not the unicode type symbol. Is there a way to keep the unicode type symbol in access? On Wed, 5 Dec 2007 11:55:04 -0800, scharsch <scharsch@discussions.microsoft.com> wrote: What version of Access? Are you using the same font? -Tom. >I have a linked excel file that has text type with symbols. The symbol shows >up in my report as a text not the unicode type symbol. > >Is there a way to keep the unicode type symbol in access? ...

excel export available
I have a query that I wish to export the results to Excel. However, the Excel button on the Export Data tab is grayed out? It used to work. Any ideas of what makes the Excel Export unavailable? Thanks, Mike ...

How will I do Excel user-defined function to extract letters from string
How will I extract letters from this string like... to: AB-123456-45 AB BCD-678901-23 BCD E-23454 E if I this bottom code extract numbers from a the same string: Function ExtractNum(rCell As Range) Dim iCount As Integer, i As Integer Dim sText As String Dim lNum As String sText = rCell For iCount = Len(sText) To 1 Step -1 If IsNumeric(Mid(sText, iCount, 1)) Then i = i + 1 lNum = Mid(sText, iCount, 1) & lNum End If If i = 1 Then lNum = CInt(Mid(lNum, 1, 1)) Next iCount ExtractNum ...