+ Reply to Thread
Results 1 to 6 of 6

#N/A situation

  1. #1
    Forum Contributor
    Join Date
    03-09-2007
    Location
    Australia
    MS-Off Ver
    Office 10
    Posts
    371

    #N/A situation

    Hi,

    In my Worksheet [Col c] is have the following code which performs the required Function set for it.
    But when the rows do not contain the right data it end up with #N/A
    now I know I need to wrap this array within a code which will remove/replace the #N/A
    So the question is how do I wrap the code within the function required.

    =INDEX($R$21:$R$37,MATCH(G29,INDEX($R$21:$R$37,0,1))+ (G29<>VLOOKUP(G29,$R$21:$R$37,1)),$F$1)

    regards

    Barry

  2. #2
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523
    Hi Barry,

    I'm a little confused - is it code (VBA) that's returning the value or a typical spreadsheet formula? If it's the later, you could use...

    =IF(ISERROR(INDEX($R$21:$R$37,MATCH(G29,INDEX($R$21:$R$37,0,1))+(G29<>VLOOKUP(G29,$R$21:$R$37,1)),$F$1)),"",INDEX($R$21:$R$37,MATCH(G29,INDEX($R$21:$R$37,0,1))+(G29<>VLOOKUP(G29,$R$21:$R$37,1))))

    ...to return a blank if there's an error or perform the formula if there's not.

    HTH

    Robert

  3. #3
    Forum Contributor
    Join Date
    03-09-2007
    Location
    Australia
    MS-Off Ver
    Office 10
    Posts
    371
    Hi,
    No not VB Code just a Function
    Tried the suggested but that unfortuately did not work
    Any further Suggestions?

    regards

    Barry

  4. #4
    Forum Contributor
    Join Date
    03-09-2007
    Location
    Australia
    MS-Off Ver
    Office 10
    Posts
    371
    What I sent was an exact copy of what is in the cell which is returning the #N/A

    regards

    Barry

  5. #5
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523
    Hi Barry,

    Strange as it worked for me - is calculation on automatic?

    If it is, could you zip the file and attach it (or part thereof) as it makes it much easier if I have actual file to look at.

    Kind regards,

    Robert

  6. #6
    Forum Contributor
    Join Date
    03-09-2007
    Location
    Australia
    MS-Off Ver
    Office 10
    Posts
    371
    Thanks It was just a set up [address] error on my behalf,
    many thanks

    Barry

+ 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