+ Reply to Thread
Results 1 to 11 of 11

Formula in one work sheet wont accept result from another in a different work sheet.

  1. #1
    Registered User
    Join Date
    01-31-2015
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    74

    Formula in one work sheet wont accept result from another in a different work sheet.

    I have a rather frustrating problem that it seems others have had also but I have yet to find a solution that works.

    I have simplified things below as I tend to give too much information.


    Basicly I have one formula that looks at another and if the result of the first is 1 then the second returns "GREAT"
    but if the result of the first is 2 then the second returns "BAD".

    I have a formula that looks at the result of a formular on a different worksheet (in the same work book) and returns
    a conditional result depending on the result of the first formula BUT its not working how it should?

    I have two work books, one called Data.xls & the other called Results.xls.

    Results.xls has a work sheet called 'Data_Grab' that uses formulas to pull specific data from Data.xls.
    These formulas are fairly basic like =IF(ISBLANK([Data.xls]Track!$F$2),"",""&[Data.xls]Track!$F$2& "").

    There is a second work sheet in Results.xls called 'Data_Sort' which does as its name suggests - it is meant
    to take the data from the 'Data_Grab' using formulas to present it in certain ways.

    It is this part that is not working.

    Lets say the formula in Cell A1 of 'Data_Sort' grabs the number 1 from cell A1 of 'Data_Grab'...

    Even the most basic formula like =IF(Data_Grab!A1=1,TRUE,FALSE) as it always shows FALSE when it should show TRUE.

    Now if i manually type the number 1 in Cell A1 of 'Data_Grab then the formula works?

    For what ever reason the formula in Data_Sort wont accept the result of the formula in Data_Grab??


    I have tried creating a 'helper' column in Data_Grab and converting the value of A1 to text which didnt work
    and I have tried converting the value to a number which also didnt work.

    The only thing that worked was if I manually type in the number 1...

    I can not find a way around this so any suggestions would be great as I can not procede any further, I am stumped.


    Thanks.

  2. #2
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: Formula in one work sheet wont accept result from another in a different work sheet.

    I suspect that there are trailing spaces or the odd "invisible" characters that you can't see.

    Can you try, in any cell, =LEN(Data_Grab!A1), it should be 1, but I suspect it will be more than that. If so, try trimming it (i.e. =TRIM(Data_Grab!A1)).

  3. #3
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula in one work sheet wont accept result from another in a different work sheet.

    Quote Originally Posted by Belinea2010 View Post
    Even the most basic formula like =IF(Data_Grab!A1=1,TRUE,FALSE) as it always shows FALSE when it should show TRUE.
    I'm thinking you have a data type mismatch.

    Data_Grab!A1 may be a TEXT value even though it looks like a number.

    Try one of these (or both!):

    =Data_Grab!A1="1"

    =N(Data_Grab!A1)=1

    Does Data_Grab!A1 contain a formula? If so, post it.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    01-31-2015
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    74

    Re: Formula in one work sheet wont accept result from another in a different work sheet.

    Hi

    Thanks for the suggestion but sadly neither worked...

  5. #5
    Registered User
    Join Date
    01-31-2015
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    74

    Re: Formula in one work sheet wont accept result from another in a different work sheet.

    Quote Originally Posted by Tony Valko View Post
    I'm thinking you have a data type mismatch.

    Data_Grab!A1 may be a TEXT value even though it looks like a number.

    Try one of these (or both!):

    =Data_Grab!A1="1"

    =N(Data_Grab!A1)=1

    Does Data_Grab!A1 contain a formula? If so, post it.

    Hi

    Thanks for the reply

    Yes the formula in Data_Grab!A1 is =IF(ISBLANK([Data.xls]Track!$A$1),"",""&[Data.xls]Track!$A$1& "")

  6. #6
    Registered User
    Join Date
    01-31-2015
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    74

    Re: Formula in one work sheet wont accept result from another in a different work sheet.

    Quote Originally Posted by Tony Valko View Post
    I'm thinking you have a data type mismatch.

    Data_Grab!A1 may be a TEXT value even though it looks like a number.

    Try one of these (or both!):

    =Data_Grab!A1="1"

    =N(Data_Grab!A1)=1

    Does Data_Grab!A1 contain a formula? If so, post it.

    Hi

    Thanks for the reply

    Yes the formula in Data_Grab!A1 is =IF(ISBLANK([Data.xls]Track!$A$1),"",""&[Data.xls]Track!$A$1& "")

  7. #7
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: Formula in one work sheet wont accept result from another in a different work sheet.

    Did you try the LEN formula? Is the result more than 1?

    Edit: Can you copy and paste "as value" some of those numbers Data_Grab column A that you have unto a new workbook and attach it here?

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula in one work sheet wont accept result from another in a different work sheet.

    Quote Originally Posted by Belinea2010 View Post
    Yes the formula in Data_Grab!A1 is =IF(ISBLANK([Data.xls]Track!$A$1),"",""&[Data.xls]Track!$A$1& "")
    Ok, a couple of things...

    Don't use the ISBLANK function just do like this:

    =IF([Data.xls]Track!$A$1="",""...

    If the cell contains a formula blank "" then ISBLANK = FALSE.

    If the referenced cell is not blank then the value_if_FALSE argument returns a TEXT value because you're concatenating quotes to the cell reference:

    ...""&[Data.xls]Track!$A$1&""

    Why are you doing that?

    Try this formula:

    =IF([Data.xls]Track!$A$1="","",[Data.xls]Track!$A$1)

  9. #9
    Registered User
    Join Date
    01-31-2015
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    74

    Re: Formula in one work sheet wont accept result from another in a different work sheet.

    Hi Tony

    =IF([Data.xls]Track!$A$1="","",[Data.xls]Track!$A$1) did the trick and works perfectly.

    Thanks for taking the time to help, much appreciated.

  10. #10
    Registered User
    Join Date
    01-31-2015
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    74

    Re: Formula in one work sheet wont accept result from another in a different work sheet.

    Thanks to everyone for your suggestions.

    Much appreciated.

  11. #11
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula in one work sheet wont accept result from another in a different work sheet.

    You're welcome. Thanks for the feedback!


    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as 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. Replies: 0
    Last Post: 11-12-2012, 04:49 AM
  2. [SOLVED] Macro wont work when sheet is protected
    By Willardio in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-21-2012, 07:44 PM
  3. [SOLVED] Why wont my search function work when i change it from all sheets to active sheet?
    By joshnathan in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-03-2012, 12:58 PM
  4. pop up date picker wont work when sheet is protected
    By jamie.c in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-17-2009, 11:28 AM
  5. Sheet Change Event wont work
    By Strugglin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-02-2009, 10:35 AM
  6. Replies: 2
    Last Post: 05-23-2008, 09:46 AM
  7. Replies: 1
    Last Post: 10-29-2005, 11:05 AM

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