formula to change sheet number when dragged

**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.