Total a cell starting from first sheet upto the sheet name selected from drop-down list

i suggest you change the layout of your data.

Use only 1 sheet instead of several sheets for each month.

It will make analyze data a lot easier.

Have you tried this?

from April to July

=SUM(Apr:July!B5)

the result: 220

it should be better with INDIRECT where you can make a month selection:

this will sum B5 between selected months

=SUMPRODUCT(SUM(INDIRECT("'"&B1:C1&"'!B5")))

for Cumulative column in M3 and copy down

=SUMPRODUCT(SUM(INDIRECT("'"&$K$3:K3&"'!B5")))

***just make sure that months listed in Col "K" match the tabs exactly.

If the sheets were arranged from Jan thru Dec this would be relatively easy.

However, it appears as though the sheets are arranged based on a fiscal year order.

First thing: Make sure the sheet names all use the same naming format. Some of yours use the short name format like Apr while others use the long name format like March.

Make ALL the sheet names in one format. I prefer the short name format. So, all sheet names would be:

Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, Jan, Feb, Mar

Create the reference table in K3:L14...

Data Range

	K	L
3	Apr	50
4	May	40
5	Jun	60
6	Jul	70
7	Aug	30
8	Sep	80
9	Oct	20
10	Nov	20
11	Dec	70
12	Jan	40
13	Feb	50
14	Mar	50

Enter this formula in L3 and copy down:

=INDIRECT(K3&"!B5")

Change the source of the drop down list in cell C1. As the source use =$K$3:$K$14.

Then, the sum formula would be:

=SUM(L3:INDEX(L3:L14,MATCH(C1,K3:K14,0)))

I agree with oeldere. Having one worksheet would make calculations very easy. If you are adding one worksheet for every month, it will not take long before you have too many to handle easily.
This sample file shows one way of doing this. The table will expand properly (no extra formulae or formatting required.

Originally Posted by kmahesh

Alkey your Sumproduct formula is not working , what i get is #Ref!

Please see attached file with formulas

28 rows and 17 columns are not many cells at all. Just use the date of entry for each row and you are all set.

How you set up your data is your business but if you want to keep it manageable and easy to work with the suggestion stands.

Good luck.