Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: incrementing worksheet names dynamically

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #2
    Board Regular
    Join Date
    Apr 2002
    Posts
    124
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

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

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #4
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #5
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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