I am having a problem getting a formula to increment the sheet number when dragged to other columns. I am currently using the INDIRECT function which works well for getting the sheet number to change but because of the nature of the indirect function when i insert a row on one of the other sheets the formula does not update the cell references with the new inserted row. I need a way to directly link the cell references and index the sheets when dragging the cell. any help would be greatly appreciated. here is my current formula:
=COUNTIF(INDIRECT(TEXT(W3,"00") & "!$S$9:$S$10"), ">=1/1/2011")-COUNTIF(INDIRECT(TEXT(W3,"00") & "!$S$9:$S$10"),">1/31/2011")
where the column W is a list of the sheet numbers starting with row 3.
the problem is when a line is inserted on one of the sheets between rows 9 and 10 i need this formula to reflect the change and switch the range from 9:10 to 9:11.
Thanks for any help.
Bookmarks