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?

[Marcelo]

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:

>
> 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?
>
>
> --
> axialtilt
> ------------------------------------------------------------------------
> axialtilt's Profile: http://www.excelforum.com/member.php...o&userid=36694
> View this thread: http://www.excelforum.com/showthread...hreadid=564245
>
>

[axialtilt]

brilliant - thank you Marcelo

[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]...
>
> 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?
>
>
> --
> axialtilt
> ------------------------------------------------------------------------
> axialtilt's Profile:
> http://www.excelforum.com/member.php...o&userid=36694
> View this thread:
> http://www.excelforum.com/showthread...hreadid=564245
>

[Marcelo]

thanks for the feedback,

Regards
Marcelo

"axialtilt" escreveu:

>
> brilliant - thank you Marcelo
>
>
> --
> axialtilt
> ------------------------------------------------------------------------
> axialtilt's Profile: http://www.excelforum.com/member.php...o&userid=36694
> View this thread: http://www.excelforum.com/showthread...hreadid=564245
>
>

[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

[Marcelo]

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:

>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> axialtilt
> ------------------------------------------------------------------------
> axialtilt's Profile: http://www.excelforum.com/member.php...o&userid=36694
> View this thread: http://www.excelforum.com/showthread...hreadid=564245
>
>

[axialtilt]

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

cheers

Axial