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

1. ## 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. ## 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. ## Re: Formula in one work sheet wont accept result from another in a different work sheet.

Originally Posted by Belinea2010
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.

4. ## 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. ## Re: Formula in one work sheet wont accept result from another in a different work sheet.

Originally Posted by Tony Valko
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. ## Re: Formula in one work sheet wont accept result from another in a different work sheet.

Originally Posted by Tony Valko
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. ## 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. ## Re: Formula in one work sheet wont accept result from another in a different work sheet.

Originally Posted by Belinea2010
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. ## 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. ## 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. ## 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.

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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