incrementing worksheet names dynamically

dmstarr · Apr 10th, 2002, 07:52 PM

have numbered tabs for worksheet names in my workbook and have created a summary sheet that computes from each sheet. Cannot get the @#$@$ formula to let me mathematically change the sheet name tho. Would like to copy and paste one row of formulas for all 50 rows of the summary.

eg:
COUNTIF('8'!$G$3:$G$143, "gary")

would count the occurrences in sheet 8.
I'd _like_ to have it be
COUNTIF('prevrowval+1'!$G$3:$G$143, "gary")

where prevrowval starts as 0 (a constant), and the first actual row of the table ends up with the value of 0+1 in the formula, corresponding to sheet named 1 in my workbbook.

Stupid question I know but I can't figure it out without having to program in VB and I _really_ think there's an easier way.

cpod · Apr 11th, 2002, 08:43 AM

If you are starting in row 2 of your summary sheet:

=COUNTIF(indirect(row()-1) & "!$G$3:$G$143", "gary")

dmstarr · Apr 11th, 2002, 11:13 AM

Thanks for previous post, but Excel 2000 seems to find it has an error. Can u explain functional syntax so I can debug it?

is row() a tricky way to use the row number as a sheet name?

Al Chara · Apr 11th, 2002, 12:52 PM

I don't think you can do it without VBA, sorry.

For i = 1 To ThisWorkbook.Worksheets.Count
Cells(i, 1).Formula = "=COUNTIF(" & i & "!G3:G143,""gary"")"
Next i
_________________
Hope this helps.
Kind regards, Al.

[ This Message was edited by: Al Chara on 2002-04-11 11:54 ]

Al Chara · Apr 11th, 2002, 02:03 PM

Ok, I got it to work, try the following setup:

Cell A1: =COUNTIF(INDIRECT(B1),"gary")
Cell B1: ="'"&ROW() & "'" & "!G3:G143"

Then copy the two cells down as far as you want. Is this what you want?

Apr 11th, 2002, 02:03 PM	#5
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	Ok, I got it to work, try the following setup: Cell A1: =COUNTIF(INDIRECT(B1),"gary") Cell B1: ="'"&ROW() & "'" & "!G3:G143" Then copy the two cells down as far as you want. Is this what you want? __________________ Kind regards, Al Chara

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 10th, 2002, 07:52 PM	#1
dmstarr New Member Join Date: Apr 2002 Posts: 2	have numbered tabs for worksheet names in my workbook and have created a summary sheet that computes from each sheet. Cannot get the @#$@$ formula to let me mathematically change the sheet name tho. Would like to copy and paste one row of formulas for all 50 rows of the summary. eg: COUNTIF('8'!$G$3:$G$143, "gary") would count the occurrences in sheet 8. I'd _like_ to have it be COUNTIF('prevrowval+1'!$G$3:$G$143, "gary") where prevrowval starts as 0 (a constant), and the first actual row of the table ends up with the value of 0+1 in the formula, corresponding to sheet named 1 in my workbbook. Stupid question I know but I can't figure it out without having to program in VB and I _really_ think there's an easier way.

Apr 11th, 2002, 08:43 AM	#2
cpod Board Regular Join Date: Apr 2002 Posts: 124	If you are starting in row 2 of your summary sheet: =COUNTIF(indirect(row()-1) & "!$G$3:$G$143", "gary")

Apr 11th, 2002, 11:13 AM	#3
dmstarr New Member Join Date: Apr 2002 Posts: 2	Thanks for previous post, but Excel 2000 seems to find it has an error. Can u explain functional syntax so I can debug it? is row() a tricky way to use the row number as a sheet name?

Apr 11th, 2002, 12:52 PM	#4
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	I don't think you can do it without VBA, sorry. For i = 1 To ThisWorkbook.Worksheets.Count Cells(i, 1).Formula = "=COUNTIF(" & i & "!G3:G143,""gary"")" Next i _________________ Hope this helps. Kind regards, Al. [ This Message was edited by: Al Chara on 2002-04-11 11:54 ]