Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically changing range of functions for monthly columns
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically changing range of functions for monthly columns
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. -- redstang423 ------------------------------------------------------------------------ redstang423's Profile: http://www.excelforum.com/member.php...o&userid=37333 View this thread: http://www.excelforum.com/showthread...hreadid=570736 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to have a chart automatically adjust range as I add data entri | Excel Discussion (Misc queries) | |||
How to have a chart automatically adjust range as I add data entr. | Charts and Charting in Excel | |||
Automatically insert time without changing. | Excel Discussion (Misc queries) | |||
How to see columns from which values were not returned by HLOOKUP functions? | Excel Worksheet Functions | |||
hiding columns automatically | Excel Discussion (Misc queries) |