Formulas containing variable input

[ym4life]

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(B99), 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]

Try...

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

Hope this helps!

 

[ym4life]

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

 

[Morrigan]

Try this

=Sum($B$9:B9)


Hope this helps

 

[Domenic]

Thanks a lot for the suggestion, I tried it and every imaginabl
combination of formulas using INDIRECT, but i keep on getting Value an
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 yo
a sample file

 

[Guest]

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