Automatically changing range of functions for monthly columns

[Shannon]

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.