Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts
    I'm sure that this topic has been covered before, but my search didn't find what I need.

    We have a column with the names of the worksheets in the next column we want to put a formula to use the worksheet name from the column A and the same cell C4 from each worksheet, where the client name is typed. I know that I have used a cell value to get me the worksheet name in a formula but can't remember how to do it.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can use the INDIRECT function for this. For example, if the worksheet names start in A2, enter the following formula in B2:

    =INDIRECT("'"&A2&"'!C4")

    and fill down as far as needed.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thanks, Hans.

    Is there are formula to get the worksheet tab name into a cell?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The formula

    =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

    will return the name of the sheet containing the cell with the formula.

  5. #5
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thanks again, Hans.

    Can you explain the formula to me? What does the +1 and the 256 mean in the formula?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    CELL("filename",A1) returns a reference to the worksheet including the complete path of the workbook that contains cell A1, like this for example:

    C:\Excel\[Book1.xls]Sheet1

    (See CELL - Excel - Microsoft Office Online)

    FIND("]",CELL("filename",A1)) returns the position of the ], i.e. the character immediately before the sheet name.

    FIND("]",CELL("filename",A1))+1 is the position of the character after the ], i.e. the first character of the sheet name.

    MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256) returns the substring that starts at the first character of the sheet name, with length 256. Since the sheet name is at most 31 characters long, 256 is more than enough to extract the entire sheet name (you could use 31 instead of 256).

  7. #7
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thanks, Hans.

    Sometimes the formulas just need a little translation to get them to make sense, at least to me.

Posting Permissions

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