Thanks:  0
Likes:  0

# Thread: incrementing worksheet names dynamically

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

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

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

3. 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?

4. 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 ]

5. 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?

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•