formula to change sheet number when dragged

**italy426** · 12-02-2010, 12:03 PM

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.

**NBVC** · 12-02-2010, 12:20 PM

I think the only way that would be possible if you somehow index the allowable range for each sheet.

Is there something about those rows that we can index only those necessary values.. like, for example are they the only numeric entries in column S, is there text in the same row that we can use as condition to check, etc....

Maybe a sample workbook would help.

**italy426** · 12-02-2010, 02:18 PM

I have uploaded my workbook. we are looking at the table "added tasks 2011" starting in column X. so far the sheet is blank and each line can potentially be different so i dont think that linking to something else in the row would work unless you see somthing. i guess i could add a specific coulum for that linking purpose but im not sure how i would code that....really i dont think i need to use the INDIRECT function here. Im not sure of any other way to get the formula in index to the next sheet without using it.

thanks for your help.

**NBVC** · 12-02-2010, 03:00 PM

Here's one way.

Add a couple of helper columns that determine the first to last rows to count based on being between rows with titles in column B Task List and Task Status.

So, In Metrics, U3:

=MATCH("Task List",INDIRECT(TEXT(W3,"00")&"!$b:$b"),0)+1

in Metrics, V3:

=MATCH("Task Status",INDIRECT(TEXT(W3,"00")&"!$b:$b"),0)-1

both copied down.

Then, so formula copying will be easier, change the dates in X2:AI2 to the first of each month, e.g. Jan 1, 2011, Feb 1, 2011... etc.. and format as custom: mmm

Then change formula in X3 to:

=COUNTIF(INDIRECT(TEXT($W3,"00") & "!$S"&$U3&":$S"&$V3), ">="&EOMONTH(X$2,-1)+1)-COUNTIF(INDIRECT(TEXT($W3,"00") & "!$S"&$U3&":$S"&$V3),">"&EOMONTH(X$2,0))

copied down and across all the monthly columns.

**italy426** · 12-02-2010, 04:30 PM

Thank you for all your help. I had to enable the analysis tool pack for the eomonth function to work but once i did that the code worked exactly as i needed it! definataly beats entering all the sheet names and dates in by hand.

one last thing, as im sure you can tell by my number of posts i am new to this forum and cannot figure out how to mark this task as solved or to contribute to your reputation. I am on my work computer and we do not have up to date flash players or java apps. I can follow the directions for both, but i do not see a button to contribut to your reputation or an edit button for the original post.....

thanks again for you help.