text function?

S

SixBowls

I have the following formula that totals data for multiple tabs. Each tab is
a month and each tab is set-up the same.

=SUM('Sep-09:Jan-09'!C9,'Sep-09:Jan-09'!E9,'Sep-09:Jan-09'!G9,'Sep-09:Jan-09'!I9,'Sep-09:Jan-09'!K9,'Sep-09:Jan-09'!M9,'Sep-09:Jan-09'!Q9,'Sep-09:Jan-09'!S9,'Sep-09:Jan-09'!U9)

The formula works but I have 196 rows where the formula is used. I
currently do a find/replace when I add a new month or want to look at a
quarter. I would like the formula to reference two cells (b3 for what is now
Sep-09 and b4 for what is now Jan-09) and be able to type the sheet name in
those cells to get the period.

Also open to suggestions on a better formula.
 
M

muddan madhu

how many sheets you have between Sep-09 to Jan-09

is it Sep-09,Aug-09,Jul-09,Jun-09,May-09,Apr-09,Mar-09,Feb-09,Jan-09
 
M

Megan

Here is a suggestion on your formula which you pretty have most of it done
already.
I would set up each spreadsheet the same. So the totals are in the same cell
on each page. Insert a tab calling it First before your first spreadsheet
and add another spreadsheet after the last of your spreadsheets calling it
Last. You can hide these after you are done..but do not enter spreadsheets
in between these two tabs unless you want them to also be in your totals.

Create a summary tab and in the same cell enter "=SUM(First:Last!I27)" This
will total everything into the summary page.
 
S

SixBowls

Thanks for the reply. That will get my contract to date totals. However, my
spreadsheet will have 60 months (Sep-06 through Aug-11) and I need to pull
data for periods of time - usually calendar years or quarters.

I am playing around with the INDIRECT function now but can't seem to get it
to work.
 
R

Roger Govier

Hi

Are you sure that the formula you posted is correct?
It looks as though you are taking every other column between C and U, except
you have missed out column O

If you are taking every other column from the relevant sheet, then it could
be written as
=SUMPRODUCT((Sep!C9:U9)*MOD(COLUMN(Sep!C9:U9),2)=1))

Then, using a Summary sheet, enter in
cell A1 !C9:U9
in A2 Sep
in A3 Oct
in A4 Nov

In B2 =A2&$A$1
Copy down through cells B3:B4

in C2 you could enter
=SUMPRODUCT((INDIRECT(B2))*(MOD(COLUMN(INDIRECT(B2)),2)=1))
and copy down to C3:C4
Your result required would be =SUM(C2:C4)
--
Regards
Roger Govier

SixBowls said:
I have the following formula that totals data for multiple tabs. Each tab
is
a month and each tab is set-up the same.

=SUM('Sep-09:Jan-09'!C9,'Sep-09:Jan-09'!E9,'Sep-09:Jan-09'!G9,'Sep-09:Jan-09'!I9,'Sep-09:Jan-09'!K9,'Sep-09:Jan-09'!M9,'Sep-09:Jan-09'!Q9,'Sep-09:Jan-09'!S9,'Sep-09:Jan-09'!U9)

The formula works but I have 196 rows where the formula is used. I
currently do a find/replace when I add a new month or want to look at a
quarter. I would like the formula to reference two cells (b3 for what is
now
Sep-09 and b4 for what is now Jan-09) and be able to type the sheet name
in
those cells to get the period.

Also open to suggestions on a better formula.



__________ Information from ESET Smart Security, version of virus
signature database 4536 (20091023) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4537 (20091023) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
R

Roger Govier

Hi

I noticed an error in my posting.
The values to enter in A2 onward of your Summary sheet, would of course be
Jan-09, Feb-09, Mar-09 etc.

Having just seen your last posting, where you say you will have 60 sheets,
clearly the series will move on through till Dec-14
Just complete the series down column A, and achieve your values in Column B
You can then easily pick out your Quarterly and annual totals from this
column of 60 results.
--
Regards
Roger Govier

Roger Govier said:
Hi

Are you sure that the formula you posted is correct?
It looks as though you are taking every other column between C and U,
except you have missed out column O

If you are taking every other column from the relevant sheet, then it
could be written as
=SUMPRODUCT((Sep!C9:U9)*MOD(COLUMN(Sep!C9:U9),2)=1))

Then, using a Summary sheet, enter in
cell A1 !C9:U9
in A2 Sep
in A3 Oct
in A4 Nov

In B2 =A2&$A$1
Copy down through cells B3:B4

in C2 you could enter
=SUMPRODUCT((INDIRECT(B2))*(MOD(COLUMN(INDIRECT(B2)),2)=1))
and copy down to C3:C4
Your result required would be =SUM(C2:C4)
--
Regards
Roger Govier



__________ Information from ESET Smart Security, version of virus
signature database 4537 (20091023) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus
signature database 4538 (20091024) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4538 (20091024) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
S

SixBowls

The formula is correct. I do not use column O.

Roger Govier said:
Hi

Are you sure that the formula you posted is correct?
It looks as though you are taking every other column between C and U, except
you have missed out column O

If you are taking every other column from the relevant sheet, then it could
be written as
=SUMPRODUCT((Sep!C9:U9)*MOD(COLUMN(Sep!C9:U9),2)=1))

Then, using a Summary sheet, enter in
cell A1 !C9:U9
in A2 Sep
in A3 Oct
in A4 Nov

In B2 =A2&$A$1
Copy down through cells B3:B4

in C2 you could enter
=SUMPRODUCT((INDIRECT(B2))*(MOD(COLUMN(INDIRECT(B2)),2)=1))
and copy down to C3:C4
Your result required would be =SUM(C2:C4)
--
Regards
Roger Govier



__________ Information from ESET Smart Security, version of virus signature database 4537 (20091023) __________

The message was checked by ESET Smart Security.

http://www.eset.com



.
Click to expand...
 
S

SixBowls

Is this possible with INDIRECT? The following formula works:
=SUM(Sep09:Jan09!C9)

I would like to change it to something like this (is returning a #REF! error):
=SUM(INDIRECT(B3&":"&B4&"!"&B5))

b3 is Sep09, b4 is Jan09, b5 is C9

If I could get this to work, I can plug the rest of the formula.
 
R

Roger Govier

Hi

Well then, you could just use
=SUMPRODUCT((INDIRECT(B2))*(MOD(COLUMN(INDIRECT(B2)),2)=1))-INDIRECT(A2&"!O9")

Taking the single value of O9 away from the total would still be far more
efficient than adding up all of the individual cells.
--
Regards
Roger Govier

SixBowls said:
Is this possible with INDIRECT? The following formula works:
=SUM(Sep09:Jan09!C9)

I would like to change it to something like this (is returning a #REF!
error):
=SUM(INDIRECT(B3&":"&B4&"!"&B5))

b3 is Sep09, b4 is Jan09, b5 is C9

If I could get this to work, I can plug the rest of the formula.



__________ Information from ESET Smart Security, version of virus
signature database 4545 (20091026) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4545 (20091026) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
S

SixBowls

column o has text and the formula gives me an error. I got it to work by
changing the formula to
=SUMPRODUCT((INDIRECT(C3))*(MOD(COLUMN(INDIRECT(C3)),2)=1))+SUMPRODUCT((INDIRECT(D3))*(MOD(COLUMN(INDIRECT(D3)),2)=1))

c3 is sheet name + c9:m9 and d3 is sheet name + q9:u9

Thanks for the help!
Shawn
 
R

Roger Govier

You're more than welcome Shawn

I hadn't thought about the possibility that column O contained Text!!!
Glad you figured out the workaround to the problem.
--
Regards
Roger Govier

SixBowls said:
column o has text and the formula gives me an error. I got it to work by
changing the formula to:
=SUMPRODUCT((INDIRECT(C3))*(MOD(COLUMN(INDIRECT(C3)),2)=1))+SUMPRODUCT((INDIRECT(D3))*(MOD(COLUMN(INDIRECT(D3)),2)=1))

c3 is sheet name + c9:m9 and d3 is sheet name + q9:u9

Thanks for the help!
Shawn



__________ Information from ESET Smart Security, version of virus
signature database 4549 (20091027) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4549 (20091027) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

weeks in a month 1
Find the position of a date 6
Combo Bar-Line Graph with single list of data 1
how to use if statements or vlookups 0
Formula required for # of days Calculation 3
Transform calendarized data 4
Date / Time problem 4
Sum YTD by Code 6

Top