I am currently dealing with large formulas, such as the below, for a financial spreadsheet (replaced sheet reference names for confidentiality). The formula needs to be the same for each month however we are unable to have for example, January in column A, February in column B, March in column C... as that would allow me to just simply drag across. There are 3 columns per month, so, for example, the below formula is in the first column (A) for January, then there are 2 other columns (B + C) for other data relating to January, but then I need this below formula in February's first column (D), then there are two more columns for February (E + F), and so on for the remaining months...
(please ignore the 'if' function section of this)...
=IF(C43>="09",(TEST!P6+TEST!P13)+(TEST!C4+TEST!C20+TEST!C21+TEST!C22+TEST!C23+TEST!C24)/24+(TEST!C25/23)+TEST!C26+TEST!C27+TEST!C28+TEST!C29, 0)
So if the above formula applies for January all cell references are 'C', then February's would need to be 'D', March 'E' and so on. The beginning two 'P' references need to stay the same for all months which can be anchored without a problem.
So far I have tried to hide the relevant columns, only have the first column of each month visible, and drag the formula along but excel still recognises these columns so January's would be 'C' and February's would be 'F' rather than 'D'.
Is there any way you can copy and paste/drag a formula across hidden cells whilst only increasing the column value by one??
Bookmarks