Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
paste formulas between workbooks without workbook link | Excel Discussion (Misc queries) | |||
Input Form vba help | Excel Discussion (Misc queries) | |||
Problem with named formula's | Excel Worksheet Functions | |||
Way to make Excel only run certain formulas on a worksheet? | Excel Discussion (Misc queries) | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |