Transfering a changing cell value progressively through a workbook

I am creating a payment application form in excel 2007 using windows 7. Each 
worksheet represents one months invoice. Say I have a formula in "sheet 1/ 
cell Q7" that sums the total billed to date for a particular budget line 
item. This value will be transfered to a the next months payment application 
"sheet 2/ cell K7" this becomes the total amount of previous applications, 
then "sheet 2/ Q7 is =sum(K7,M7,O7) this value is transfered to "sheet 3/ K7, 
and so on and so on, until the completion of the job. How do I acomplish 
this.   
-- 
Thanks
Mike
0
Utf
2/4/2010 2:33:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
580 Views

Similar Articles

[PageSpeed] 46

If you're willing to use a User Defined Function this becomes quite
easy....... 

Function PrevSheet(rg As Range) 
    n = Application.Caller.Parent.Index 
    If n = 1 Then 
        PrevSheet = CVErr(xlErrRef) 
    ElseIf TypeName(Sheets(n - 1)) = "Chart" Then 
        PrevSheet = CVErr(xlErrNA) 
    Else 
        PrevSheet = Sheets(n - 1).Range(rg.Address).Value 
    End If 
End Function 

Example of use......................

Say you have 12 sheets,  sheet1 through sheet12...........sheet names don't
matter.

In sheet1 you have a formula in A10  =SUM(A1:A9)

Select second sheet and SHIFT + Click last sheet 

In active sheet A10 enter =SUM(PrevSheet(A10),A1:A9)

Ungroup the sheets. 

Each A10 will have the sum of the previous sheet's A10 plus the sum of the
current sheet's A1:A9


Gord Dibben  MS Excel MVP

On Wed, 3 Feb 2010 18:33:01 -0800, Mike1558
<Mike1558@discussions.microsoft.com> wrote:

>I am creating a payment application form in excel 2007 using windows 7. Each 
>worksheet represents one months invoice. Say I have a formula in "sheet 1/ 
>cell Q7" that sums the total billed to date for a particular budget line 
>item. This value will be transfered to a the next months payment application 
>"sheet 2/ cell K7" this becomes the total amount of previous applications, 
>then "sheet 2/ Q7 is =sum(K7,M7,O7) this value is transfered to "sheet 3/ K7, 
>and so on and so on, until the completion of the job. How do I acomplish 
>this.   

0
Gord
2/4/2010 4:45:07 AM
I tried this and what I get in the cell that I enter PrevSheet(Q7)what shows 
up in the cell is #REF!. I have been unable to make this produce a value. 
What did I do wrong? I copied the UDF to vis basic like you discribed in the 
previous email but there must be something that I did wrong.

Thanks 

Mike 
-- 
Thanks
Mike


"Gord Dibben" wrote:

> If you're willing to use a User Defined Function this becomes quite
> easy....... 
> 
> Function PrevSheet(rg As Range) 
>     n = Application.Caller.Parent.Index 
>     If n = 1 Then 
>         PrevSheet = CVErr(xlErrRef) 
>     ElseIf TypeName(Sheets(n - 1)) = "Chart" Then 
>         PrevSheet = CVErr(xlErrNA) 
>     Else 
>         PrevSheet = Sheets(n - 1).Range(rg.Address).Value 
>     End If 
> End Function 
> 
> Example of use......................
> 
> Say you have 12 sheets,  sheet1 through sheet12...........sheet names don't
> matter.
> 
> In sheet1 you have a formula in A10  =SUM(A1:A9)
> 
> Select second sheet and SHIFT + Click last sheet 
> 
> In active sheet A10 enter =SUM(PrevSheet(A10),A1:A9)
> 
> Ungroup the sheets. 
> 
> Each A10 will have the sum of the previous sheet's A10 plus the sum of the
> current sheet's A1:A9
> 
> 
> Gord Dibben  MS Excel MVP
> 
> On Wed, 3 Feb 2010 18:33:01 -0800, Mike1558
> <Mike1558@discussions.microsoft.com> wrote:
> 
> >I am creating a payment application form in excel 2007 using windows 7. Each 
> >worksheet represents one months invoice. Say I have a formula in "sheet 1/ 
> >cell Q7" that sums the total billed to date for a particular budget line 
> >item. This value will be transfered to a the next months payment application 
> >"sheet 2/ cell K7" this becomes the total amount of previous applications, 
> >then "sheet 2/ Q7 is =sum(K7,M7,O7) this value is transfered to "sheet 3/ K7, 
> >and so on and so on, until the completion of the job. How do I acomplish 
> >this.   
> 
> .
> 
0
Utf
2/4/2010 8:50:10 PM
Only way I can get #REF! is if I enter the formula on first sheet.

There is no previous sheet in that case.


Gord

On Thu, 4 Feb 2010 12:50:10 -0800, Mike1558
<Mike1558@discussions.microsoft.com> wrote:

>I tried this and what I get in the cell that I enter PrevSheet(Q7)what shows 
>up in the cell is #REF!. I have been unable to make this produce a value. 
>What did I do wrong? I copied the UDF to vis basic like you discribed in the 
>previous email but there must be something that I did wrong.
>
>Thanks 
>
>Mike 

0
Gord
2/5/2010 6:33:45 PM
Reply:

Similar Artilces:

Can you insert a picture into a specific cell?
Or do they only "float" on the page. Marc They float over the worksheet, but you can adjust the size so that it seems to fit a cell exactly. If you insert a picture, you can hold the alt-key down while you move/resize the picture. When it gets close to the edge of a cell, it'll snap-to that edge. Marc wrote: > > Or do they only "float" on the page. > > Marc -- Dave Peterson ...

I want to unlock my word doc to make changes its protected
I am writing a word document the other night. I went to carry on with it tonight but found it has protected the document and won't let me continue writing or editing Word 2007? Assuming that you have activated Office, it seems your trial version of the application has expired. Time to pay the piper! -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> ...

Changing SQL Server for CRM 1.2
As my SQL server is currently on its last breath I am in the process of moving our CRM 1.2 databases to a new dedicated serever. I have detatched the databases from the old server, copied the files to the server and attached them. I have then gone into CRM deployment manager and changed to the SQL server to the new server. That all appears to work fine, no error messages. But when users try to access the CRM system they get an error and the CRM server event log fills with error messages. A few are listed below: dmLog: Failed to execute the SQL batch in the file sqlbatch.sql. MSCRM P...

transfer data from multiple columns to singlr column
I have data in form a d g b e h c f i (but larger scale) and I need it in a single column going a to z. Hi, highlight you data, copy, go to the column where you want to see the data, paste special, transpose "lc85" wrote: > I have data in form a d g > b e h > c f i (but larger scale) > and I need it in a single column going a to z. You up for using a macro? Sub ToOneColumn() 'dantuck Mar 7, 2007 &...

Territory Change
Our Sales VP has restructured all of the geographical territories that we had set up in Microsoft CRM. Our many thousands of Accounts are associated with territories. Obviously it is ludicrous to think that we would have to go one by one and change the territory on each account to the new territories. However, I have heard that there are strict rules for making changes directly to the backend SQL database. If we develop a SQL statement to change the territories assigned to each account to the new territories, are we going to be breaking anything? We don't want to screw up any de...

chart MAcro to change on activecell
Here is the macro below.. The only problem is that the columns change every month. so range (columns)keeps widening.... I have sorted it by selecting range upto column N. so it is provided for all 12 months. But now the "Grand total" column which is always the last column also gets included..(.which shouldnt be included in the range for the chart) Is there a way to modify this macro ? Sub updatechart1() Dim ThechartObj As ChartObject Dim Thechart As Chart Dim Userrow As Long Dim CatTitles As Range Dim SrcRange As Range Dim SourceData As Range If Sheets("summary").Ch...

Transferring control of CClientDC to CDC
Hi, I have a class MSWinDisplayManager which I want to take a CClientDC device context so that it's member functions can perform drawing routines on it. I want the class to have it's own CClientDC member which all the methods have access to draw on. My constructor looks like this: MSWinDisplayManager::MSWinDisplayManager(CClientDC& win) { private_win.attach(win); } This is called by the user like: CClientDC dlg(this); MSWinDisplayManager wdm(dlg); then I want to do things like: wdm.drawCars(); The problem I have is that private_win isn't getting control of the device ...

visual basic
Hi, I trying to retrieve values from a table to calculate the 14days average value of a stock closing price. However, i encounter some problem as stated beside the code as follows: Function DaysAvgs() 'Calculate the average value of a given value. Dim db As DAO.Database Dim rst As DAO.Recordset Dim varBookmark As Variant Dim numAve, numDaysAvg As Double Dim intA, intB, lngCount As Integer Set db = CurrentDb 'Open Table Set rst = db.OpenRecordset("SGX Individual Historical", dbOpenTable) rst.MoveFirst Do While Not rst.EOF intA = 1 intB = 0 varBookmark = rst.Bookmark n...

Joining text with a formula in cell #4
just to complete the thread... I found the answer. You have to change the format of the cell to custom 0.00"*" this is the only way it will show only 2 decimal places Thanks for the hel -- Mustard Hea ----------------------------------------------------------------------- Mustard Head's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1630 View this thread: http://www.excelforum.com/showthread.php?threadid=27700 ...

Changing font in Money 2004 register?
Is there any way to change the font(s) used in MS Money 2004's registers? The default font is too small and difficult for me to read. Also, is there any way to change the color scheme to something more pleasing to my eye (like you can do in Quicken...) Thanks. Nope and Nope. See http://umpmfaq.info/faqdb.php?q=136. "Debbie R." <debbimsr@bellsouth.net> wrote in message news:f5ff01c43e15$e2ae3700$a401280a@phx.gbl... > Is there any way to change the font(s) used in MS Money > 2004's registers? The default font is too small and > difficult for me to read....

IsOutLookClient() returns wrong value
IsOutLookClient() returns wrong value when both web client of crm and outlook client are running on the same workstation It looks like the same cookie(used for determining what client is running) is used by the sessions of each client. Look for "LightClient" in IsOutlookWorkstationClient() in global.js Oeps...I seem to have made a wrong assumption... Between the to clients IsOutlookClient() seems to work ok... But in outlook client the IsOutlookClient() function gives false for me...after I have opened a page from the Microsoft Crm folder structure... On another workstation it...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

Single click selects multiple cells
When clicking on a single cell multiple cells are selected. The one time solution for this is to zoom in or out. This is problematic as 60% seems to be the zoom that works most of the time but at this zoom level the cell contents do not display. The time lost and the frustration that builds is killing my productivity and office attitude. Please give all of us a permanet fix. -- Thanks Mike ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" butt...

Change the Exchange Virtual Directory to different website
I would like to remove the exchange virtual directory default website and move it another website which is currently redirecting to the website I want to delete. Meaning rather than logon to OWA as http://www.wheresmylunch.com/exchange (current default website) I want to move to http://www.getyourownsandwich,com/exchange. I am using Exchange 2000 server. Rube You would change the host header on the current website. -- Hope that helps, Dan Townsend This posting is provided "AS IS" with no warranties, and confers no rights. Please do not send email to this address, post a reply t...

Excel Opens Without Displaying Workbook
I am having issues with opening an Excel file. The file opens, but the workbook is not displayed. I tried the resolution in the article XL97: Excel Opens Without Displaying Workbook (http://support.microsoft.com/default.aspx?scid=kb;en-us;158996&Product=xlw97), but neither of the resolutions fixed the problem. Any suggestions?? Are you using Excel 97? -John Baughman Fort Collins, CO >-----Original Message----- >I am having issues with opening an Excel file. The file opens, but the workbook is not displayed. I tried the resolution in the article XL97: Excel Opens Without Di...

Referencing cells across sheets
Hi, I've created a workbook with three sheets. The first three columns of sheet 2 and sheet 3 are referenced to the first three columns of sheet 1. What I'd like to do, is set the workbook up so that if I insert a row before, between or after referenced rows in sheet one, a row will also be inserted, and referenced, in the corresponding place on sheets 2 and 3. Does anyone know how to do this? Also, if no data is entered into a cell on sheet one, the value shown in the corresponding cells of sheets '2' and '3' is '0'. I need those cells to remain bla...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

How to unlock locked cells by MACRO?!
I have locked Sheet and I need to change particular cells. But when my macro wants to change it => ERROR :/ Is there any way to unprotect these particulary cells by macro and change them? - after that action, of course, I want to lock them again. Please help me, Pedro Just unprotect the sheet, do the work, and reprotect the sheet. Kind of like: activesheet.unprotect password:="yourpassword" 'your code to change stuff activesheet.protect password:="yourpassword" Petr N�me�ek wrote: > > I have locked Sheet and I need to change particular cells. But when m...

Money 2000 Account Balance Changed Inexplicably
Opening Money 2000, which I've used without problem since late 1999, I noticed that my checking account balance was overstated by almost $2000! I went to the register to see if there was a false transaction entered and it appears that this balance change goes back years with no obvious single entry being the culprit. When I run the "balance this account" function, it shows that the closing balance from my last statement, which was correct and rectified, is now also wrong and reflects the higher balance. What do I do now? The only thing that I can think of is to restore...

How to add a button to restore all altered cells original values?
I want to add a reset button to an excel spreadsheet that will restore the values of all changed cells to the original saved ones. Any help would be appreciated. Thanks Dawn Hi this would require quite some VBA code as you somehow have to store the original values for example on a separate hidden sheet -- Regards Frank Kabel Frankfurt, Germany "Dawnybros" <Dawnybros@discussions.microsoft.com> schrieb im Newsbeitrag news:3340601E-16EE-4296-8F50-B0BAC18EA387@microsoft.com... > I want to add a reset button to an excel spreadsheet that will restore the > values of all ...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Changing language
I am running the Swedish version of Excel for XP and I need the US (or English) version. Is there a way to convert the language and all the settings associated with it? Using the swedish version is rather annoying since the formula names are translated to swedish. Thank you, Magnus ...

With and import tool can you change only item description?
Is there a way to change only the item description on a large quanity of items. What about the extended description? Thanks for your help. Use the MS SQL Data Import Tool by EMS. $65.00. The QSImport Tool available to download from Microsoft will probably work but is not supported by Microsoft. Kinnard L. Kohler Business Machines Systems 6101 South Shackleford Road Little Rock, AR 72204-8606 (T) 501-375-8380 (F) 501-375-0043 (Cell) 501-412-5686 Email: kinnard@removebmsar.com "Lisa" wrote: > Is there a way to change only the item description on a large quanity of >...

Changing a profile on Microsoft Outlook 2003
I set up two profiles through the control panel and directed Outlook to prompt me for which profile to use each time it was opened. But now it skips the prompt and goes straight to one of the profiles. I need to restore that prompt, but it won't respond to the instructions in the control panel Mail dialogue box Hi Chris, did you get the same behavior after a restart of the computer? You could try "Sart/run/fixmapi.exe" (you don�t get any confirmation message) and restart the computer again. If this wouldn�t wotk, I would create a 3rd (test) profile. Maybe Outlook don�t ...

Macro
I need a macro that help me to transfer name and address information from an specific table in excel to a template in words on specific areas and then print the word document. The reason for this is that i need to create diferents letters to be sent to the customers from the excel table. Example of the table is: soc seg, customer name, child name, customer code, add 1 , add2, city, estate, zip code. all this information will be paste on word letter template on specific areas or fields. Any suggestion!!! -- nicoro Hi IMHO the best approach would be to set up a mail merge documen...