Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Indirect cell addressing

    Dear loungers,

    I have a spreadsheet where I want to construct an address that has the data I want to retrieve.

    This is the address: 'Missing Fields 16 July'$E$4
    This is constructed like this if I wanted it as a string: ="'"&PreviousTabName&"'"&ADDRESS(ROW(),COLUMN() )

    If I just wrap INDIRECT round this it doesn't like it, which may be to do with where I put the quotation marks. The simple formula =INDIRECT("E4") works but substituting the formula above for E$ and removing the double quotes in the formula just produced a #REF error.

    What do i need to do?

    thank you......................................... liz

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Liz,

    You didn't separate the sheet name from the cells with an Exclamation Point. "!"

    ="'"&PreviousTabName&"!'"&ADDRESS(ROW(),COLUMN( ) )

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Hi Retired Geek,

    How annoying obvious! I was going blind having stared at it for so long. Thank you

    My problem then was how to wrap INDIRECT around it, I had a problem with quotation marks. Partly because I thought I had to have single quotation marks round the tab name - I removed embedded spaces and then was OK.

    For anything with this problem here is the solution:

    =INDIRECT(PreviousTabName&"!"&ADDRESS(ROW(),COLUMN ( ) ))

    liz
    Last edited by lizat; 2013-03-21 at 08:21.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If you have spaces in the tab name then you do need the quotes - I usually add them for safety anyway in case things change

    =INDIRECT("'"&PreviousTabName&"'!"&ADDRESS(ROW(),C OLUMN ( ) ))
    Regards,
    Rory

    Microsoft MVP - Excel

Tags for this Thread

Posting Permissions

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