Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with variable ranges
I need some help making some of my reports more automated. What I'm trying
to accomplish is being able to fill in a reference cell for the current month, and have ranges in varying functions adjust to include the new month. This way I can enter the new data and not worry about having to go and adjust every formula to include it. For example, if January's data is in cell C4 and I want to sum the data for January thru August I'd like to be able to write the formula to SUM(C4:C("current month")). I've tried doing this various ways, but have been unsuccessful. Thanks for any suggestions. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with variable ranges
This article from Jon Peltier should help get you started. It applies to
dynamic charting, but the concept is the same. http://pubs.logicalexpressions.com/P...cle.asp?ID=246 -- HTH, Barb Reinhardt "Nelson B." wrote: I need some help making some of my reports more automated. What I'm trying to accomplish is being able to fill in a reference cell for the current month, and have ranges in varying functions adjust to include the new month. This way I can enter the new data and not worry about having to go and adjust every formula to include it. For example, if January's data is in cell C4 and I want to sum the data for January thru August I'd like to be able to write the formula to SUM(C4:C("current month")). I've tried doing this various ways, but have been unsuccessful. Thanks for any suggestions. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with variable ranges
Pls post in ONE group only. -- Don Guillett Microsoft MVP Excel SalesAid Software "Nelson B." <Nelson wrote in message ... I need some help making some of my reports more automated. What I'm trying to accomplish is being able to fill in a reference cell for the current month, and have ranges in varying functions adjust to include the new month. This way I can enter the new data and not worry about having to go and adjust every formula to include it. For example, if January's data is in cell C4 and I want to sum the data for January thru August I'd like to be able to write the formula to SUM(C4:C("current month")). I've tried doing this various ways, but have been unsuccessful. Thanks for any suggestions. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with variable ranges
Sample:
A1 = 3 << Your Variable (months or columns to stretch out to) B3 = 123 C3 = 456 D3 = 789 formula in cell A3 =SUM(B3:INDIRECT(ADDRESS(3,A1+1,4))) HTH, "Nelson B." wrote: I need some help making some of my reports more automated. What I'm trying to accomplish is being able to fill in a reference cell for the current month, and have ranges in varying functions adjust to include the new month. This way I can enter the new data and not worry about having to go and adjust every formula to include it. For example, if January's data is in cell C4 and I want to sum the data for January thru August I'd like to be able to write the formula to SUM(C4:C("current month")). I've tried doing this various ways, but have been unsuccessful. Thanks for any suggestions. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with variable ranges
Better alter cell A1 below to $A$1
=SUM(B3:INDIRECT(ADDRESS(3,A1+1,4))) to =SUM(B3:INDIRECT(ADDRESS(3,$A$1+1,4))) so when you copy the formula down it works !! "JMay" wrote: Sample: A1 = 3 << Your Variable (months or columns to stretch out to) B3 = 123 C3 = 456 D3 = 789 formula in cell A3 =SUM(B3:INDIRECT(ADDRESS(3,A1+1,4))) HTH, "Nelson B." wrote: I need some help making some of my reports more automated. What I'm trying to accomplish is being able to fill in a reference cell for the current month, and have ranges in varying functions adjust to include the new month. This way I can enter the new data and not worry about having to go and adjust every formula to include it. For example, if January's data is in cell C4 and I want to sum the data for January thru August I'd like to be able to write the formula to SUM(C4:C("current month")). I've tried doing this various ways, but have been unsuccessful. Thanks for any suggestions. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with variable ranges
How do I do this for vertical data in the range?
"JMay" wrote: Better alter cell A1 below to $A$1 =SUM(B3:INDIRECT(ADDRESS(3,A1+1,4))) to =SUM(B3:INDIRECT(ADDRESS(3,$A$1+1,4))) so when you copy the formula down it works !! "JMay" wrote: Sample: A1 = 3 << Your Variable (months or columns to stretch out to) B3 = 123 C3 = 456 D3 = 789 formula in cell A3 =SUM(B3:INDIRECT(ADDRESS(3,A1+1,4))) HTH, "Nelson B." wrote: I need some help making some of my reports more automated. What I'm trying to accomplish is being able to fill in a reference cell for the current month, and have ranges in varying functions adjust to include the new month. This way I can enter the new data and not worry about having to go and adjust every formula to include it. For example, if January's data is in cell C4 and I want to sum the data for January thru August I'd like to be able to write the formula to SUM(C4:C("current month")). I've tried doing this various ways, but have been unsuccessful. Thanks for any suggestions. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named ranges scope / workbook/worksheet level named ranges- changeswith variable use... | Excel Programming | |||
Variable ranges | Excel Programming | |||
Counting variable ranges and auto-summing variable ranges | Excel Programming | |||
Variable ranges | Excel Worksheet Functions | |||
Sum Variable Ranges | Excel Worksheet Functions |