How to select data for current month?

  • Thread starter Thread starter stringer
  • Start date Start date
S

stringer

I have set up a spreadsheet with a range of data for each month of thi
year. There are about 15 worksheets, all with the same format, wit
monthly columns and a total at the foot of each column.

I wish to have a summary sheet which lists the totals for the curren
month from each worksheet. I want the front summary page to updat
automatically when the month changes. How can I create a formula o
the summary page for selecting 'current month' total from each sheet
 
Stinger,

Suppose your cell is D100 and the sheetnames are :
January; February; March etc.

You could then use the formula :
=INDIRECT(CHOOSE(MONTH(TODAY()),"January","February","March")&"!D100")

(You will offcourse have to extend it with the other months and or other
sheetnames, but the idea is clear I think).

Succes

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Stringer,

Here is a version that does the SUMming, and without the CHOOSE

=SUM(INDIRECT("'"&TEXT(TODAY(),"mmmm")&"'!A1:A100"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

A.W.J. Ales said:
Stinger,

Suppose your cell is D100 and the sheetnames are :
January; February; March etc.

You could then use the formula :
=INDIRECT(CHOOSE(MONTH(TODAY()),"January","February","March")&"!D100")

(You will offcourse have to extend it with the other months and or other
sheetnames, but the idea is clear I think).

Succes

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
Click to expand...
 
Bob,

Good idea which Ididn't think of ( Text function i mean).
However I don't think that Stinger want to sum.
He/She just wants - as I read it - to show the figure of the current month
reported on the summary sheet .
Anyhow : he/she now can pick what he wants.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

Bob Phillips said:
Stringer,

Here is a version that does the SUMming, and without the CHOOSE

=SUM(INDIRECT("'"&TEXT(TODAY(),"mmmm")&"'!A1:A100"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Click to expand...
 
Hi Auk Ales (sorry I can't figure out the name),

You might be (probably are) correct. I took the Totals bit tom mean he wants
to sum, but it is probably having totals on the individual sheets.

But he has the choice, as you say.

Bob
 
Hi Bob,

The name is Auk Ales indeed. (First name Auk ; Surname Ales; I can't help
it :) )

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Major apologies. I thought Auk was a name attributed to Kiwi origins, it's
not a name we come across much in closeted UK.

Nice to meet and talk with you Auk.

(Plain old vanilla) Bob
 
Bob,

No apologies necessary (wouldn't know for what reason by the way).

I don't know anything of the Kiwi origings (have to look that up sometime).
It's a not really common name in Holland as well, but considered to be of
Fries (Help, how do I explain THAT now again) origine.
The Fries is one of the subgroups in the Dutch population, which migth be
compared a bit to the Scots of Great Britain (at both the point of view as
being a bit apart, as well as the point of NOT being Brittish (Dutch in the
case of Friezen) :) ).

Apart from that it's indeed nice to meet and talk to you.
I suppose we'll come across eachother more often (as we already did in some
threads).

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Sorry guys, you have lost me!

I don't want to sum anything, I just want to take a value from eac
spreadsheet and put it on the summary page. The summary page shows th
value for the current month from each spreadsheet, thus:

Summary sheet 1 - Current Month=February
Value A from Sheet2, Column Feb
Value B from Sheet3, Column Feb

The summary sheet should update to March figures on 1st March.

Hope this explains. Any further guidance would be welcome.

Thanks

Jim Whitto
 
Stringer,
Taking up bobs formula, but getting the summing out of it :
If the values you want to take out of the monthly sheets and put into the
summarysheet are (for instance) in cell D100, put the next formula in the
cell on the summarysheet where you want it to be shown (best is to do this
by copy and paste, mind the "'" !!)

{ The Text(today()) evaluates into February (if today is a day in february
and so on ...). }
{ This then evaluates the whole formula to
}
{ and this tells Excel to get the value of cell D100 from sheet February and
put it in the cell this formula is in }
{ The first day in march this automaticly changes to
=Indirect("March!D100"), because the text part then evaluates to march etc.}


=INDIRECT("'"&TEXT(TODAY(),"mmmm")&"'!D100"))


--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Stringer,

Sorry.
Don't know what happened there, but the message was completely scrambled.
It should be :

Taking up Bobs formula, but getting the summing out of it :

If the values you want to take out of the monthly sheets and put into the
summarysheet are (for instance) in cell D100, put the next formula in the
cell on the summarysheet where you want it to be shown (best is to do this
by copy and paste, mind the "'" !!)

=INDIRECT("'"&TEXT(TODAY(),"mmmm")&"'!D100"))


The Text(today()) evaluates into February (if today is a day in february)
This then evaluates the whole formula to :
=INDIRECT("February!D100")
and this tells Excel to get the value of cell D100 from sheet February and
put it in the cell where this formula is in.
The first day in March this automaticly changes to :
=Indirect("March!D100"), because the text part then evaluates to march etc.


--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

A.W.J. Ales said:
Stringer,
Click to expand...
Taking up bobs formula, but getting the summing out of it :
If the values you want to take out of the monthly sheets and put into the
summarysheet are (for instance) in cell D100, put the next formula in the
cell on the summarysheet where you want it to be shown (best is to do this
by copy and paste, mind the "'" !!)

{ The Text(today()) evaluates into February (if today is a day in february
and so on ...). }
{ This then evaluates the whole formula to
}
{ and this tells Excel to get the value of cell D100 from sheet February and
put it in the cell this formula is in }
{ The first day in march this automaticly changes to
=Indirect("March!D100"), because the text part then evaluates to march etc.}


=INDIRECT("'"&TEXT(TODAY(),"mmmm")&"'!D100"))


--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
Click to expand...
 
I am sorry to have to keep coming back to you on this, but I have no
explained very well.

The various worksheets are not named by month. Each worksheet has a
account name and has a number of columns with month headings, thus:

Worksheet(Account 1) Jan, Feb, Mar etc
Worksheet(Account2) Jan, Feb, Mar, etc

The summary sheet should have a list of the totals for (eg) Februar
from each sheet, thus:

Summary sheet
Account1 Feb total
Account 2 Feb total
etc

and I wish the summary sheet to replace the Feb totals with Marc
totals when the month changes.

I thought of defining names for the columns of each worksheet, bu
Excel doesn't allow me to use the same name (eg February) in eac
workshee
 
Seems to be reciprocated Stringer. As far as I can see, Auk has given you
exactly what you asked for.

Auk's formula

=INDIRECT("'"&TEXT(TODAY(),"mmmm")&"'!D100"))

will at this moment (that is a day in February) will pick up a value in the
sheet named February, in the D100 cell and put in whatever cell in the
summary sheet that you put the formula. In 15 days time, that will
automatically pick up the D100 value from the March worksheet. Obviously, if
D100 is not where you total resides, change it.

Similarly, add the same formula for the account 2, changing D100 to the
appropriate cell.

If your sheets are not name February, March, April, etc., but are Feb, Mar,
Apr, then change the formula to

=INDIRECT("'"&TEXT(TODAY(),"mmm")&"'!D100"))

Why this works is because this bit, TEXT(TODAY(),"mmm") returns the month
name string of the current month. This, "'"&TEXT(TODAY(),"mmm")&"'!D100"
strings it all together in a familiar reference formula ('February'!D100),
and the INDIRECT function gets the value that is pointed at by the string
passed to INDIRECT.

Have you tried it? If so what is not happening that should happen?
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Stringer,

I think I understand now roughly how your sheet is build up, but find it
hard to explain how to do what you want to do, and probably would need a lot
more information (where is what and so on) before I could as well.

I suggest you make a small copy of it, replace figures with non private
figures and send it to me (remember to take the nospam out of my email
addres).
Make a few remarks about where you want what, and I'll have a look at it.
I can however guarantee nothing.

(By the way Excel does allow you to apply the same names on different
sheets, you will however have to add a sheet reference to the REFERS TO path
when you give the name : If you give for instance the name Tstarea to
Sheet1!$C$1:$C$3 (via the name box; not via Insert / Name / Define) you can
do the same on sheet2 and on sheet3 and so on. Excel more or less makes
difference between names on application level and on sheetlevel.
I doubt however that there will be a solution to your problem in that
direction).

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Seeing Auk's reply makes me realise that my previous response was not spot
on. Sorry about that.

As an alternative suggestion, why not replicate the columns on the summary
sheet, that is have Jan, Feb, Mar etc on it, and then the totals formula is
easy (=SUM(Jan:Dec!A100, etc.), but highlight this month's numbers using
conditional formatting (formula of =MONTH(TODAY())=COLUMN()-1 if the months
are in B,C, etc.).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Bob Phillips said:
Seems to be reciprocated Stringer. As far as I can see, Auk has given you
exactly what you asked for.

Auk's formula

=INDIRECT("'"&TEXT(TODAY(),"mmmm")&"'!D100"))

will at this moment (that is a day in February) will pick up a value in the
sheet named February, in the D100 cell and put in whatever cell in the
summary sheet that you put the formula. In 15 days time, that will
automatically pick up the D100 value from the March worksheet. Obviously, if
D100 is not where you total resides, change it.

Similarly, add the same formula for the account 2, changing D100 to the
appropriate cell.

If your sheets are not name February, March, April, etc., but are Feb, Mar,
Apr, then change the formula to

=INDIRECT("'"&TEXT(TODAY(),"mmm")&"'!D100"))

Why this works is because this bit, TEXT(TODAY(),"mmm") returns the month
name string of the current month. This, "'"&TEXT(TODAY(),"mmm")&"'!D100"
strings it all together in a familiar reference formula ('February'!D100),
and the INDIRECT function gets the value that is pointed at by the string
passed to INDIRECT.

Have you tried it? If so what is not happening that should happen?
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Click to expand...
 
Bob - there is not a separate sheet for each month, there is a separat
sheet for each account, and each sheet has 12 columns headed January t
December. There are about 15 sheets in all. I want to take th
relevant monthly total from each account and put it onto the summar
sheet.

Auk - I am typing this at home and the spreadsheet in question is a
work! I won't be there until next Thursday, but will send you a
example then.

Many thanks

Ji
 
Stringer,

Oke. I'l be waiting for it, but count a few days for me adapting it for
your needs as well. I'm really VERY busy at the moment so I have have
somehow to find some time for it, but I *will* try it.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Stringer,

Yes, see my other post, I got this point on second reading and suggested an
alternative. If you are in a hurry, you could send it to me (as well as
Auk?) as I am not so busy<g>. Let me know whether you want to go the
original way, or explore my alternative suggestion (or even both).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob

Sorry, didn't see your second post. I think your solution would be th
simplest. I did think of it originally but was trying to be to
clever!

Thanks, both, for your advice. If I have a problem with the sheet I'l
send it, but I should be able to sort it out now.

Jim Whitto
 
Jim,

Give it a go, and you know where we are if you need more. If you do,
probably best to start a new thread, this one has gone on too long already.

Regards

Bob
 
Back
Top