+ Reply to Thread
Results 1 to 4 of 4

Combining OFFSET, Indirect and IF functions

  1. #1
    Registered User
    Join Date
    10-17-2013
    Location
    Newcastle
    MS-Off Ver
    2010
    Posts
    25

    Combining OFFSET, Indirect and IF functions

    This bit of a tricky one to explain,I roughly know what my equation should look like, but it keeps failing. Its all part of a wider spreadsheet but I will only put down that bits that impact this part of the sheet.

    In my cell I need it to input a value from a different spreadsheet. The input value is text.

    I currently use this formula and it works perfectly for what I need.

    =(OFFSET(INDIRECT("sheet2!D"&$A7),R$6,25,1,1))

    This would a return a variety of results such as "excluded","compliant","non-compliant".

    I would like to arrange an IF function so that if the answer is Excluded to keep it as Excluded. But anything else to use this formula instead.

    =(OFFSET(INDIRECT("sheet2!D"&$A7),(R$6-2),25,1,1))

    Can anyone possibly help me on this?

  2. #2
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Combining OFFSET, Indirect and IF functions

    =if((OFFSET(INDIRECT("sheet2!D"&$A7),(R$6-2),25,1,1))="Excluded","Excluded",OFFSET(INDIRECT("sheet2!D"&$A7),(R$6-2),25,1,1)))

    or if you need cell to go blank then
    =if((OFFSET(INDIRECT("sheet2!D"&$A7),(R$6-2),25,1,1))="Excluded","",OFFSET(INDIRECT("sheet2!D"&$A7),(R$6-2),25,1,1)))
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Combining OFFSET, Indirect and IF functions

    Quote Originally Posted by dowell89 View Post
    I currently use this formula and it works perfectly for what I need.

    =(OFFSET(INDIRECT("sheet2!D"&$A7),R$6,25,1,1))
    Wouldn't this formula give you the same result?

    =INDEX(Sheet2!AC:AC,$A7+R$6)

    Then for your IF function

    IF(INDEX(Sheet2!AC:AC,$A7+R$6)="Excluded","Excluded",INDEX(Sheet2!AC:AC,$A7+R$6-2))
    Audere est facere

  4. #4
    Registered User
    Join Date
    10-17-2013
    Location
    Newcastle
    MS-Off Ver
    2010
    Posts
    25

    Re: Combining OFFSET, Indirect and IF functions

    Thanks for the replies.

    I will try that index function to see if it speeds up the spread sheet as its starting to get a little slow.

+ 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. 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
  2. [SOLVED] Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions
    By Mike Barlow in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  3. Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions
    By Mike Barlow in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  4. Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions
    By Mike Barlow in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-05-2005, 11: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