building YTD figures by adding to a previous months total

[axialtilt]

HI All,

a little help here please? I've had a look on the forum and although
found references to similar problems did not manage to find an existing
solution.

I'm thrying to build a spreadsheet that allows me to report monthly
figures by taking last months YTD figure, adding this months sales and
obtaining a new YTD.

I have a sheet for each month.

so far I am using :

='April '!M7+May!M7+June!M7+July!M7..... etc

which does work as I go along, but means that once you add in the new
month's figures the previous months all reflect that as well.

Is there a "last month" function?

In the past I did it manually, editing the reference to "last month"
and giving specific cell locations and changing that specific cell
location in each month. Unfortunately I have hundreds cell locations to
change and it means adding a new customer or product is very time
consuming.

I've also tried using :

=February!L7+M7

and then changing "febraury!" to "march!" using a global find and
replace for each month. This is much quicker but again it is very
difficult to add in new products or customers.


any ideas please?

 

[Guest]

Hi,

try to use Indirect and Address embeded, use an auxiliar cell to type the
last month eg. March (assuming this auxiliar cell is F3)

so: =indirect(address(7,13,1,1,$F$3))

when
7 = line
13 = M column (13th column)
1 = absolute or relatvie (could be 1, 2, 3 or 4)
1 = A1 or R1C1
$F$3 = March
=March!M7

hth
regards from Brazil
Marcelo




"axialtilt" escreveu:

 

[Roger Govier]

Hi

I would insert 2 new sheets and call them First and Last.
Drag them to a position before your First Month and after your last
month respectively.
On your Summary sheet, which should be outside of the "sandwich" created
by First and Last enter
=SUM(First:Last!M7)

By moving the position of Last, you can have your summary show totals up
to any given month.

--
Regards

Roger Govier


"axialtilt" <[email protected]>
wrote in message
news:[email protected]...

 

[axialtilt]

brilliant - thank you Marcelo

 

[Guest]

thanks for the feedback,

Regards
Marcelo

"axialtilt" escreveu:

 

[axialtilt]

Thanks Roger, interesting approach to the problem. I would like to try
to build it so that all was needed was to enter the numbers each
month.


Marcelo,

I got your idea working really nicely - for one cell.

I can't see how you can copy that formula down a column so that it
automatically increments the row number as it goes down. I thought that
making the abs_num 3 or 4 should do that, but apparently not.



I've included a test spread sheet if anybody would be so kind to take a
look...

The indirect address function is in cell A7 of the May sheet.

What I would like to do is drag/copy that cell down to A10, then
duplicate the May sheet so all I have to change is the month number to
3 and the sheet title to June to set up the next month.

cheers

Axial


+-------------------------------------------------------------------+
|Filename: indirectaddress.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5081 |
+-------------------------------------------------------------------+

 

[Guest]

Hi

two ways,

=indirect(address(7,13,1,1,$F$3))

the 7 is the row number you can substitue by row() or create an auxiliar
column to "ID" the numbers you are looking for before copy it down.

hth
regards from Brazil
Marcelo

"axialtilt" escreveu:

 

[axialtilt]

Marcelo, I used the first method and it works very nicely thankyou!

cheers

Axial