Forum Home
Press F1
Thread ID: 9475	2001-05-29 09:06:00	Excel - updating data between two sheets.	Guest (0)	Press F1

Post ID	Timestamp	Content	User
13349	2001-05-29 09:06:00	Excel - updating data between two sheets. Fairly simple - I hope. I have a spreadsheet 'Cashbook' where daily invoices are entered. The last row in the sheet totals all the previous enteries for that month. The Sheet open with an Auto_Start that sorts by the 1st column which contains a date entry. In the row for the last day of the month, in the column for ACC Levy, I use the formula F287 =SUM(F$157:F286) where F157 is the 1st entry for the month. The $ sign allows for additional enteries to be added and F287 display the updated total. No problem here. There is another spreadsheet 'Monthly' that contains Monthly summaries of Cashbook, but the rows are Transposed. How can I get the Cashbook Monthly total (which changes row position as daily enteries are added) to show in Monthly. In the Past Ive gone, Edit/Copy Edit/Paste.Special/Transpose/Values to update Monthly. I could make a simple Macro that performs the above function, but I would like Monthly to update values whenever the file is opened. Thanks in anticipation. John jhw@xtra.co.nz	Guest (0)
13350	2001-05-29 12:20:00	John, From what I've read, it sounds as though your problem is that the accruing monthly total in the first sheet moves down a row as new invoices are added above it. The accrued monthly total is added to another sheet where the months are listed across the row, and their totals are in the column below that. If this is wrong, then what I say next isn't going to make sense. Options are: 1. the accruing total (running total for the current month) could be placed in another column by inself ie. $G$1 = SUM(F$157:F286). This way you would know exactly where the total was 2. if you name the range (ie the total cell for the current month), you can just refer to its name (as long as invoices are added by inserting before it 3. do everything through code (which is a little more time consuming - but possible if you know VBA or how to work the macro recorder) I'm more than happy to look at your workbook if you feel comfortable sending it to me. Good luck. Bill	Guest (0)
13351	2001-05-31 08:31:00	Thanks for the reply. Youve got the idea, what i need is a way to get the accrued monthly total (which changes row# as entries are added0 I think i will do this in a macro as you suggest. Thanks ....John	Guest (0)
1