Formulas containing variable input
 

Hi guys:
I suck at Excel, so I would really appreciate any help. Here's the
deal, I have a huge spreadsheet in which each column contains the data
pertaining to each month. for example B is january, C is February, and
so on... I have a separate sheet where calculations are carried out,
and one such calculation requires summing up the columns of data from
all previous month including the current month. for instance, if it
were march, I would have someth like =Sum(B9:D9), for the month of june
i would have =Sum(B9:G9). it's a pain changing the formula manually
every month, so is there a way to represent the second column letter in
the formula as a variable input that depends on the current month? I
want it in the form of =Sum(B9:X9), where X is an expression that
represents the correct column. i tried nested formulas like:
=Sum(B9:('HelpSheet'!A1)9), where 'HelpSheet!'A1 is a cell in another
sheet that contains the appropriate column letter. Obvious my blind
attempts didn't work, so hope you guys can give some suggestions.
Cheers,
JR Shaw


--
ym4life
------------------------------------------------------------------------
ym4life's Profile: http://www.excelforum.com/member.php...o&userid=24768
View this thread: http://www.excelforum.com/showthread...hreadid=383345

Try...

=SUM(INDIRECT("B9:"&'HelpSheet'!A1&"9"))

Hope this helps!

ym4life Wrote:
Hi guys:
I suck at Excel, so I would really appreciate any help. Here's the
deal, I have a huge spreadsheet in which each column contains the data
pertaining to each month. for example B is january, C is February, and
so on... I have a separate sheet where calculations are carried out,
and one such calculation requires summing up the columns of data from
all previous month including the current month. for instance, if it
were march, I would have someth like =Sum(B9:D9), for the month of june
i would have =Sum(B9:G9). it's a pain changing the formula manually
every month, so is there a way to represent the second column letter in
the formula as a variable input that depends on the current month? I
want it in the form of =Sum(B9:X9), where X is an expression that
represents the correct column. i tried nested formulas like:
=Sum(B9:('HelpSheet'!A1)9), where 'HelpSheet!'A1 is a cell in another
sheet that contains the appropriate column letter. Obvious my blind
attempts didn't work, so hope you guys can give some suggestions.
Cheers,
JR Shaw


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=383345

Thanks a lot for the suggestion, I tried it and every imaginable
combination of formulas using INDIRECT, but i keep on getting Value and
Ref errors.... i'll keep on trying and any advice is much welcomed


--
ym4life
------------------------------------------------------------------------
ym4life's Profile: http://www.excelforum.com/member.php...o&userid=24768
View this thread: http://www.excelforum.com/showthread...hreadid=383345

Try this

=Sum($B$9:B9)


Hope this helps


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=383345

ym4life Wrote:
Thanks a lot for the suggestion, I tried it and every imaginable
combination of formulas using INDIRECT, but i keep on getting Value and
Ref errors.... i'll keep on trying and any advice is much welcomed

Hmmm...that's strange...it works for me. If you'd like, I can send you
a sample file.


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=383345

I would insert a range name covering all of your current and future column
totals by highlighting them and selecting: 'Insert, name, define.' You can
call it any name you wish. Once you have done this, all you have to do is
refer to the name in the formula. i.e. =sum(yourrangename). Hope this sorts
you out.

Gary

"ym4life" wrote:


Hi guys:
I suck at Excel, so I would really appreciate any help. Here's the
deal, I have a huge spreadsheet in which each column contains the data
pertaining to each month. for example B is january, C is February, and
so on... I have a separate sheet where calculations are carried out,
and one such calculation requires summing up the columns of data from
all previous month including the current month. for instance, if it
were march, I would have someth like =Sum(B9:D9), for the month of june
i would have =Sum(B9:G9). it's a pain changing the formula manually
every month, so is there a way to represent the second column letter in
the formula as a variable input that depends on the current month? I
want it in the form of =Sum(B9:X9), where X is an expression that
represents the correct column. i tried nested formulas like:
=Sum(B9:('HelpSheet'!A1)9), where 'HelpSheet!'A1 is a cell in another
sheet that contains the appropriate column letter. Obvious my blind
attempts didn't work, so hope you guys can give some suggestions.
Cheers,
JR Shaw


--
ym4life
------------------------------------------------------------------------
ym4life's Profile: http://www.excelforum.com/member.php...o&userid=24768
View this thread: http://www.excelforum.com/showthread...hreadid=383345