+ Reply to Thread
Results 1 to 4 of 4

Modifying INDIRECT

  1. #1
    Forum Contributor
    Join Date
    01-18-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    130

    Modifying INDIRECT

    Hi All

    I'm using this

    =INDIRECT(K6&"!"&I6)

    to fetch the cell content from I6 on the named worksheet

    Can someone advise as to how the formula can be modified to bring the content firstly from the left of the target cell , and then from the right of it? in this case it would return the content in H6 or J6 from the target worksheet.

    I'm trying to build it into a larger formula where if H6 on the current worksheet contains the letter 'F' then it would show content from the cell to the right of the formula above. If H6 contains the letter 'G' then it would show the content to the left of the formula above.

    Grateful for any help.
    Last edited by CDandVinyl; 02-15-2020 at 01:06 AM.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Seattle, WA, USA
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    14,864

    Re: Modifying INDIRECT

    Hi CDand Vinyl,

    We really need a sample workbook to see what you have and want. I'd hate to guess at an answer without seeing an example problem and answer you expect.
    Figure out how to attach a sample workbook and it will help you get a good answer.
    One test is worth a thousand opinions.
    Click the * below to say thanks.

  3. #3
    Valued Forum Contributor hrlngrv's Avatar
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    470

    Re: Modifying INDIRECT

    Quote Originally Posted by CDandVinyl View Post
    . . .
    =INDIRECT(K6&"!"&I6)

    . . . the cell content I need.

    . . . if H6 contains the letter 'F' then it would show content from the cell to the right of the formula above. If H6 contains the letter 'G' then it would show the content to the left of the formula above. . . .
    =OFFSET(INDIRECT(K6&"!"&I6),0,IF(COUNTIF(H6,"*F*"),1,IF(COUNTIF(H6,"*G*"),-1,#REF!)))

    This returns a #REF! error when H6 contains neither F nor G.
    Last edited by hrlngrv; 02-15-2020 at 12:41 AM.

  4. #4
    Forum Contributor
    Join Date
    01-18-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    130

    Re: Modifying INDIRECT

    OK thanks for this. It works perfectly first time. I am beyond impressed!

    Problem solved!


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1