+ 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
    641

    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
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    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 * Add Reputation below to say thanks.

  3. #3
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    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
    641

    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)

Similar Threads

  1. [SOLVED] INDIRECT Function with multiple sheets - SUMIF, INDIRECT & MATCH
    By DJDRU in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-07-2014, 08:42 AM
  2. Sum of indirect sheet names with multiple cells (SUM, INDIRECT,SHEETNAME in cell)
    By a1b2c3d4e5f6g7h8 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-12-2013, 08:42 AM
  3. Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions
    By Andy Wiggins in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 09-06-2005, 12:05 PM
  4. Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions
    By Andy Wiggins in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 09-06-2005, 07:05 AM
  5. Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions
    By Mike Barlow in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  6. Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions
    By Mike Barlow in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  7. [SOLVED] Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions
    By Mike Barlow in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

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