Automatically changing range of functions for monthly columns

[Guest]

My problem is:
I have one spreadsheet with a master list of data, and on another
spreadsheet I have monthly columns of calculations based on this data. The
range used in the formulas for these calculations varies from month to month.
I need a way to create a new column for each month (keeping the same
formulas) without manually changing the range every time.

Any help would be appreciated!

 

[redstang423]

To change the range, you can use an INDIRECT() formula to help you out.
I have a slightly similar situation. My master list of data (collected
on different dates) can be analyzes using a special analysis sheet,
analyzing any date range I choose. I have several cells hidden - they
find the starting row/column (when sorted by date) of the column and
display the row number and column number in its own cell by using a
Count function and If statement that returns the row (if you need
specifics there, I can give you the formulas I use). You can then use
the ADDRESS() function to get a cell reference to that cell location
(it will show as ex. $A$24 even if its on another sheet). If you need
to pull out the dollar signs, you can use the various equations that
manipulate the text (ex. MID()). At this point, you should can make the
starting and ending parts of the range. If you are referring to another
sheet, you can write in one cell 'Sheet1'! and then in another use the
CONCATENATE function to put it all together to get the cell range you
are referring to. At this point, in the equations you need, if the
reference is in cell B12, use the function INDIRECT(B12) in place of
where you would normally use your cell range.