Copying a Changing cell value to one cell

[mbesspiata]

I have a report where I have the total of ins and outs for the month but I want to show the last months total ONLY in one cell p8. I will use that one cell for calculations in another sheet in the workbook. How can I have one cell show a value that will move from c9 to n9 over the course of a year? Cells c7 and c8 will be totaled to c9 and I need that value to show up in p8 but each new month the total will need to move to the cell to the right.

Mike

[gebobs]

Not knowing much about the structure of your sheet other than what I've been able to decipher from your post, here's my first stab at it...

If the headings of your columns (i.e. row 1) are dates, you can do an HLOOKUP:

=HLOOKUP(DATE(YEAR(NOW()),MONTH(NOW())-1,1),C1:N9,9,FALSE)

Basically, the HLOOKUP looks for the date that is one month previous to now. Of course, this won't work so well when it's January and it looks for a month=0.

Attached is test.xlsx illustrating how this is done.