+ Reply to Thread
Results 1 to 5 of 5

"A value used in the formula is of the wrong data type." but works elsewhere

  1. 03-31-2013, 08:53 AM #1
    bungslea
    bungslea is offline
    Registered User
    Join Date
    01-31-2013
    Location
    Brisbane, Australia. high and dry
    MS-Off Ver
    Excel 2007
    Posts
    57

    "A value used in the formula is of the wrong data type." but works elsewhere

    I have moved this from VBA forum

    On the sheet I am working on I have this formula and it works 
    Please Login or Register  to view this content.
    It uses the sheet names in $C$14:$C$29

    On another sheet I have 
    Please Login or Register  to view this content.
    It works correctly. I have copied it to the first sheet and added the INDIRECT("'"&$C$14:$C$29&"'!

    Please Login or Register  to view this content.
    All I have done is add the INDIRECT() but I now get "A value used in the formula is of the wrong data type."
    Reply With QuoteRegister To Reply
  2. 03-31-2013, 08:57 AM #2
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: "A value used in the formula is of the wrong data type." but works elsewhere

    =IF(AD4:AD6<1,"",MIN(AD$4:AD$1000)) you cant compare AD4:AD6<1 it will only compare AD4<1
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula
    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC
    Reply With QuoteRegister To Reply
  3. 04-01-2013, 03:33 AM #3
    bungslea
    bungslea is offline
    Registered User
    Join Date
    01-31-2013
    Location
    Brisbane, Australia. high and dry
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: "A value used in the formula is of the wrong data type." but works elsewhere

    If you edit R6 to 
    Please Login or Register  to view this content.
    the lines empty, put back the :AD6 and, if I am reading it right, it checks AD4:AD6 and gives the results.

    Somebody else did the original formula, not me
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 04-01-2013, 07:38 AM #4
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: "A value used in the formula is of the wrong data type." but works elsewhere

    my mistake ,because =AD4:AD6 looks at the row its in only , and since that is in row 6 it looks at ad6 only not ad4 or ad5 try it. change ad4 or ad5 > 0 and change ad6 to 0 the whole thing goes blank
    but your vlookup should be
    =IF(R7="","",VLOOKUP(R7,AD:AE,2,FALSE))
    Last edited by martindwilson; 04-01-2013 at 07:41 AM.
    Reply With QuoteRegister To Reply
  5. 04-01-2013, 08:03 AM #5
    bungslea
    bungslea is offline
    Registered User
    Join Date
    01-31-2013
    Location
    Brisbane, Australia. high and dry
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: "A value used in the formula is of the wrong data type." but works elsewhere

    It does too

    the vlookup was just gobble de gook i threw in. The original referenced a different sheet.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1