Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Location
    London UK, Hertfordshire, England
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create Cell reference from Text (Excel 2000 SR1)

    Can you create a cell reference by using text contained in another cell? For example column A contains sheet names and we want a reference in column B that extracts information from its appropriate sheet.

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create Cell reference from Text (Excel 2000 SR1)

    Well, one way to do it is use INDIRECT and ADDRESS: open a new workbook and put the following in cell b1 of Sheet1: =INDIRECT(ADDRESS(1,1,,,A1)) and then type Sheet2 in a1 and this will return whatever is in Sheet2!a1. To get just the reference, leave out the INDIRECT.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create Cell reference from Text (Excel 2000 SR1)

    Hi Brooke,

    Or:

    =INDIRECT(A1 & "!A1")
    Where A1 contains the sheet name.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create Cell reference from Text (Excel 2000 SR1)

    which, let's face it, is a much simpler and neater way to go!

  5. #5
    New Lounger
    Join Date
    Jun 2002
    Location
    London UK, Hertfordshire, England
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create Cell reference from Text (Excel 2000 SR1)

    Thankyou for your replies. I used the Indirect and Address method all within a MID function to extract only part of the source cell and it works fine. Thank you again.

    Kate

Posting Permissions

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