Hi

I have a link to another spreadsheet in a cell (C8) which returns a value
"TEST"

I want to use this value in another formula, in this case an IF statement.

Example

=IF(C8="TEST",H81250/1250,"N/A")

However i cant get the formulae to work because I suspect that even although
cell C8 returns a value of "TEST" that the formula is actually reading the
"link formula" which returns the result "TEST"

How do I get my formula to recognise the word "TEST" instead of the formula
that returns it?

Thanks in advance

John

I don't see the problem - maybe I'm not looking at it properly.

Book2.xls, Sheet1, cell C8 has "TEST" in it.

Book1.xls, Sheet1, cell A1 has formula =[Book2]Sheet1!$C$8 and displays TEST

on same sheet in Book1, at say H49, put formula
IF(A1="TEST",H81250/1250,"N/A")

or also in Book1, any sheet, any cell you could put:
=IF([Book2]Sheet1!$C$8="TEST,H81250/1250,"N/A")

But I see a (potential) problem with the reference H81250 - unless you are
using Excel 2007, that is an invalid row - rows in pre-Excel 2007 only go to
65536. It may be that if you fix that row reference it might work better for
you. You don't say what kind of error you are getting with it as it is.

"John Calder" wrote:

Hi

I have a link to another spreadsheet in a cell (C8) which returns a value
"TEST"

I want to use this value in another formula, in this case an IF statement.

Example

=IF(C8="TEST",H81250/1250,"N/A")

However i cant get the formulae to work because I suspect that even although
cell C8 returns a value of "TEST" that the formula is actually reading the
"link formula" which returns the result "TEST"

How do I get my formula to recognise the word "TEST" instead of the formula
that returns it?

Thanks in advance

John

Thanks for your prompt reply.

1st up let me apoligise for making a mistake in the example I gave you. It
should have been

Example

=IF(C8="TEST",H8/1250,"N/A")

I am using Excel2K

Next I will give you "exactly" what I have.

The link I have in the cell is as follows:

In the cell C8 i have the following formula:

=IF('\\stlwolsvr01\Amm\19Apps\FTP\Orgfin\[paint_stocks.xls]paint_stocks'!C20="","",'\\stlwolsvr01\Amm\19Apps\ FTP\Orgfin\[paint_stocks.xls]paint_stocks'!C20)

This returns a value called "BONDERITE 1310ZAL"

In the cell H8 i have the following formula:

=IF('\\stlwolsvr01\Amm\19Apps\FTP\Orgfin\[paint_stocks.xls]paint_stocks'!H8="","",'\\stlwolsvr01\Amm\19Apps\F TP\Orgfin\[paint_stocks.xls]paint_stocks'!H8)

This returns the value of 600

I have placed the following formula in cell K8

=IF(C8="BONDERITE 1310ZAL",H8/1250,"N/A")

This returns the value of "N/A"

This is despite the fact the cell C8 does in fact have BONDERITE 1310ZAL in
it and that cell H8 does in fact have 600 in it.

I hope this helps

John





"JLatham" wrote:

I don't see the problem - maybe I'm not looking at it properly.

Book2.xls, Sheet1, cell C8 has "TEST" in it.

Book1.xls, Sheet1, cell A1 has formula =[Book2]Sheet1!$C$8 and displays TEST

on same sheet in Book1, at say H49, put formula
IF(A1="TEST",H81250/1250,"N/A")

or also in Book1, any sheet, any cell you could put:
=IF([Book2]Sheet1!$C$8="TEST,H81250/1250,"N/A")

But I see a (potential) problem with the reference H81250 - unless you are
using Excel 2007, that is an invalid row - rows in pre-Excel 2007 only go to
65536. It may be that if you fix that row reference it might work better for
you. You don't say what kind of error you are getting with it as it is.

"John Calder" wrote:

Hi

I have a link to another spreadsheet in a cell (C8) which returns a value
"TEST"

I want to use this value in another formula, in this case an IF statement.

Example

=IF(C8="TEST",H81250/1250,"N/A")

However i cant get the formulae to work because I suspect that even although
cell C8 returns a value of "TEST" that the formula is actually reading the
"link formula" which returns the result "TEST"

How do I get my formula to recognise the word "TEST" instead of the formula
that returns it?

Thanks in advance

John

You probably have unseen characters in cell C8.

BONDERITE 1310ZAL

Might be:
<spaceBONDERITE 1310ZAL
BONDERITE 1310ZAL<space
<spaceBONDERITE 1310ZAL<space
<char160BONDERITE 1310ZAL
etc
etc

Try one of these:

=IF(TRIM(C8)="BONDERITE 1310ZAL",H8/1250,"N/A")

=IF(ISNUMBER(SEARCH("BONDERITE 1310ZAL",C8)),H8/1250,"N/A")

Biff

"John Calder" wrote in message
...
Thanks for your prompt reply.

1st up let me apoligise for making a mistake in the example I gave you. It
should have been

Example

=IF(C8="TEST",H8/1250,"N/A")

I am using Excel2K

Next I will give you "exactly" what I have.

The link I have in the cell is as follows:

In the cell C8 i have the following formula:

=IF('\\stlwolsvr01\Amm\19Apps\FTP\Orgfin\[paint_stocks.xls]paint_stocks'!C20="","",'\\stlwolsvr01\Amm\19Apps\ FTP\Orgfin\[paint_stocks.xls]paint_stocks'!C20)

This returns a value called "BONDERITE 1310ZAL"

In the cell H8 i have the following formula:

=IF('\\stlwolsvr01\Amm\19Apps\FTP\Orgfin\[paint_stocks.xls]paint_stocks'!H8="","",'\\stlwolsvr01\Amm\19Apps\F TP\Orgfin\[paint_stocks.xls]paint_stocks'!H8)

This returns the value of 600

I have placed the following formula in cell K8

=IF(C8="BONDERITE 1310ZAL",H8/1250,"N/A")

This returns the value of "N/A"

This is despite the fact the cell C8 does in fact have BONDERITE 1310ZAL
in
it and that cell H8 does in fact have 600 in it.

I hope this helps

John





"JLatham" wrote:

I don't see the problem - maybe I'm not looking at it properly.

Book2.xls, Sheet1, cell C8 has "TEST" in it.

Book1.xls, Sheet1, cell A1 has formula =[Book2]Sheet1!$C$8 and displays
TEST

on same sheet in Book1, at say H49, put formula
IF(A1="TEST",H81250/1250,"N/A")

or also in Book1, any sheet, any cell you could put:
=IF([Book2]Sheet1!$C$8="TEST,H81250/1250,"N/A")

But I see a (potential) problem with the reference H81250 - unless you
are
using Excel 2007, that is an invalid row - rows in pre-Excel 2007 only go
to
65536. It may be that if you fix that row reference it might work better
for
you. You don't say what kind of error you are getting with it as it is.

"John Calder" wrote:

Hi

I have a link to another spreadsheet in a cell (C8) which returns a
value
"TEST"

I want to use this value in another formula, in this case an IF
statement.

Example

=IF(C8="TEST",H81250/1250,"N/A")

However i cant get the formulae to work because I suspect that even
although
cell C8 returns a value of "TEST" that the formula is actually reading
the
"link formula" which returns the result "TEST"

How do I get my formula to recognise the word "TEST" instead of the
formula
that returns it?

Thanks in advance

John

I think T. Valko is probably on to something. To test that theory:
Pick a couple of nearby empty rows, or insert two rows below C8 for this
test. I will assume for these examples that you now have 2 empty rows at 9
and 10.

In A9 enter this formula:
=CODE(MID($C$8,Column(),1))
in A10 (right below the other formula) enter this formula:
=CHAR(A9)

Next 'extend' or 'fill' those two formulas on out the row until you start
getting #VALUE errors.
Where you do NOT have #VALUE errors, there is some character in C8. The
cells in row 9 show you the ASCII value of that character, and the cells in
row 10 right below show the ASCII character itself. You should see
"BONDERITE 1310ZAL" spelled out one character at a time in row 10, and
immediately following the "L" at the end you should be getting a #VALUE
error. If things are as you think them to be, the "L" should be in column Q
with the first #VALUE error in column R. If that's not the case, look for
perhaps an extra space between BONDERITE and 1310ZAL or an extra one or two
at either the beginning or end of the phrase.


"John Calder" wrote:

Thanks for your prompt reply.

1st up let me apoligise for making a mistake in the example I gave you. It
should have been

Example

=IF(C8="TEST",H8/1250,"N/A")

I am using Excel2K

Next I will give you "exactly" what I have.

The link I have in the cell is as follows:

In the cell C8 i have the following formula:

=IF('\\stlwolsvr01\Amm\19Apps\FTP\Orgfin\[paint_stocks.xls]paint_stocks'!C20="","",'\\stlwolsvr01\Amm\19Apps\ FTP\Orgfin\[paint_stocks.xls]paint_stocks'!C20)

This returns a value called "BONDERITE 1310ZAL"

In the cell H8 i have the following formula:

=IF('\\stlwolsvr01\Amm\19Apps\FTP\Orgfin\[paint_stocks.xls]paint_stocks'!H8="","",'\\stlwolsvr01\Amm\19Apps\F TP\Orgfin\[paint_stocks.xls]paint_stocks'!H8)

This returns the value of 600

I have placed the following formula in cell K8

=IF(C8="BONDERITE 1310ZAL",H8/1250,"N/A")

This returns the value of "N/A"

This is despite the fact the cell C8 does in fact have BONDERITE 1310ZAL in
it and that cell H8 does in fact have 600 in it.

I hope this helps

John





"JLatham" wrote:

I don't see the problem - maybe I'm not looking at it properly.

Book2.xls, Sheet1, cell C8 has "TEST" in it.

Book1.xls, Sheet1, cell A1 has formula =[Book2]Sheet1!$C$8 and displays TEST

on same sheet in Book1, at say H49, put formula
IF(A1="TEST",H81250/1250,"N/A")

or also in Book1, any sheet, any cell you could put:
=IF([Book2]Sheet1!$C$8="TEST,H81250/1250,"N/A")

But I see a (potential) problem with the reference H81250 - unless you are
using Excel 2007, that is an invalid row - rows in pre-Excel 2007 only go to
65536. It may be that if you fix that row reference it might work better for
you. You don't say what kind of error you are getting with it as it is.

"John Calder" wrote:

Hi

I have a link to another spreadsheet in a cell (C8) which returns a value
"TEST"

I want to use this value in another formula, in this case an IF statement.

Example

=IF(C8="TEST",H81250/1250,"N/A")

However i cant get the formulae to work because I suspect that even although
cell C8 returns a value of "TEST" that the formula is actually reading the
"link formula" which returns the result "TEST"

How do I get my formula to recognise the word "TEST" instead of the formula
that returns it?

Thanks in advance

John

On the other hand - perhaps you have one too many spaces in your equation
between BONDERITE and 1310ZAL. That's easy to check also, just click K8 and
click in the formula bar and use arrow keys to move through the phrase and
make sure there aren't any extra spaces after the E in BONDERITE.

"John Calder" wrote:

Thanks for your prompt reply.

1st up let me apoligise for making a mistake in the example I gave you. It
should have been

Example

=IF(C8="TEST",H8/1250,"N/A")

I am using Excel2K

Next I will give you "exactly" what I have.

The link I have in the cell is as follows:

In the cell C8 i have the following formula:

=IF('\\stlwolsvr01\Amm\19Apps\FTP\Orgfin\[paint_stocks.xls]paint_stocks'!C20="","",'\\stlwolsvr01\Amm\19Apps\ FTP\Orgfin\[paint_stocks.xls]paint_stocks'!C20)

This returns a value called "BONDERITE 1310ZAL"

In the cell H8 i have the following formula:

=IF('\\stlwolsvr01\Amm\19Apps\FTP\Orgfin\[paint_stocks.xls]paint_stocks'!H8="","",'\\stlwolsvr01\Amm\19Apps\F TP\Orgfin\[paint_stocks.xls]paint_stocks'!H8)

This returns the value of 600

I have placed the following formula in cell K8

=IF(C8="BONDERITE 1310ZAL",H8/1250,"N/A")

This returns the value of "N/A"

This is despite the fact the cell C8 does in fact have BONDERITE 1310ZAL in
it and that cell H8 does in fact have 600 in it.

I hope this helps

John





"JLatham" wrote:

I don't see the problem - maybe I'm not looking at it properly.

Book2.xls, Sheet1, cell C8 has "TEST" in it.

Book1.xls, Sheet1, cell A1 has formula =[Book2]Sheet1!$C$8 and displays TEST

on same sheet in Book1, at say H49, put formula
IF(A1="TEST",H81250/1250,"N/A")

or also in Book1, any sheet, any cell you could put:
=IF([Book2]Sheet1!$C$8="TEST,H81250/1250,"N/A")

But I see a (potential) problem with the reference H81250 - unless you are
using Excel 2007, that is an invalid row - rows in pre-Excel 2007 only go to
65536. It may be that if you fix that row reference it might work better for
you. You don't say what kind of error you are getting with it as it is.

"John Calder" wrote:

Hi

I have a link to another spreadsheet in a cell (C8) which returns a value
"TEST"

I want to use this value in another formula, in this case an IF statement.

Example

=IF(C8="TEST",H81250/1250,"N/A")

However i cant get the formulae to work because I suspect that even although
cell C8 returns a value of "TEST" that the formula is actually reading the
"link formula" which returns the result "TEST"

How do I get my formula to recognise the word "TEST" instead of the formula
that returns it?

Thanks in advance

John

Thanks Guys !

T Valko was spot on, I think as this data is downloaded from a mainframe as
a .CSV file then converted to an excel file on the server is the reason that
there is unseen spaces in the data set. But thanks to you both the problem is
now rectified...WELL DONE !!!!!

John


"JLatham" wrote:

On the other hand - perhaps you have one too many spaces in your equation
between BONDERITE and 1310ZAL. That's easy to check also, just click K8 and
click in the formula bar and use arrow keys to move through the phrase and
make sure there aren't any extra spaces after the E in BONDERITE.

"John Calder" wrote:

Thanks for your prompt reply.

1st up let me apoligise for making a mistake in the example I gave you. It
should have been

Example

=IF(C8="TEST",H8/1250,"N/A")

I am using Excel2K

Next I will give you "exactly" what I have.

The link I have in the cell is as follows:

In the cell C8 i have the following formula:

=IF('\\stlwolsvr01\Amm\19Apps\FTP\Orgfin\[paint_stocks.xls]paint_stocks'!C20="","",'\\stlwolsvr01\Amm\19Apps\ FTP\Orgfin\[paint_stocks.xls]paint_stocks'!C20)

This returns a value called "BONDERITE 1310ZAL"

In the cell H8 i have the following formula:

=IF('\\stlwolsvr01\Amm\19Apps\FTP\Orgfin\[paint_stocks.xls]paint_stocks'!H8="","",'\\stlwolsvr01\Amm\19Apps\F TP\Orgfin\[paint_stocks.xls]paint_stocks'!H8)

This returns the value of 600

I have placed the following formula in cell K8

=IF(C8="BONDERITE 1310ZAL",H8/1250,"N/A")

This returns the value of "N/A"

This is despite the fact the cell C8 does in fact have BONDERITE 1310ZAL in
it and that cell H8 does in fact have 600 in it.

I hope this helps

John





"JLatham" wrote:

I don't see the problem - maybe I'm not looking at it properly.

Book2.xls, Sheet1, cell C8 has "TEST" in it.

Book1.xls, Sheet1, cell A1 has formula =[Book2]Sheet1!$C$8 and displays TEST

on same sheet in Book1, at say H49, put formula
IF(A1="TEST",H81250/1250,"N/A")

or also in Book1, any sheet, any cell you could put:
=IF([Book2]Sheet1!$C$8="TEST,H81250/1250,"N/A")

But I see a (potential) problem with the reference H81250 - unless you are
using Excel 2007, that is an invalid row - rows in pre-Excel 2007 only go to
65536. It may be that if you fix that row reference it might work better for
you. You don't say what kind of error you are getting with it as it is.

"John Calder" wrote:

Hi

I have a link to another spreadsheet in a cell (C8) which returns a value
"TEST"

I want to use this value in another formula, in this case an IF statement.

Example

=IF(C8="TEST",H81250/1250,"N/A")

However i cant get the formulae to work because I suspect that even although
cell C8 returns a value of "TEST" that the formula is actually reading the
"link formula" which returns the result "TEST"

How do I get my formula to recognise the word "TEST" instead of the formula
that returns it?

Thanks in advance

John

T's often right on top of things. I'm going to chip in a dollar here and
vote his post as having answered the question - since it pretty much did
exactly that.

"John Calder" wrote:

Thanks Guys !

T Valko was spot on, I think as this data is downloaded from a mainframe as
a .CSV file then converted to an excel file on the server is the reason that
there is unseen spaces in the data set. But thanks to you both the problem is
now rectified...WELL DONE !!!!!

John


"JLatham" wrote:

On the other hand - perhaps you have one too many spaces in your equation
between BONDERITE and 1310ZAL. That's easy to check also, just click K8 and
click in the formula bar and use arrow keys to move through the phrase and
make sure there aren't any extra spaces after the E in BONDERITE.

"John Calder" wrote:

Thanks for your prompt reply.

1st up let me apoligise for making a mistake in the example I gave you. It
should have been

Example

=IF(C8="TEST",H8/1250,"N/A")

I am using Excel2K

Next I will give you "exactly" what I have.

The link I have in the cell is as follows:

In the cell C8 i have the following formula:

=IF('\\stlwolsvr01\Amm\19Apps\FTP\Orgfin\[paint_stocks.xls]paint_stocks'!C20="","",'\\stlwolsvr01\Amm\19Apps\ FTP\Orgfin\[paint_stocks.xls]paint_stocks'!C20)

This returns a value called "BONDERITE 1310ZAL"

In the cell H8 i have the following formula:

=IF('\\stlwolsvr01\Amm\19Apps\FTP\Orgfin\[paint_stocks.xls]paint_stocks'!H8="","",'\\stlwolsvr01\Amm\19Apps\F TP\Orgfin\[paint_stocks.xls]paint_stocks'!H8)

This returns the value of 600

I have placed the following formula in cell K8

=IF(C8="BONDERITE 1310ZAL",H8/1250,"N/A")

This returns the value of "N/A"

This is despite the fact the cell C8 does in fact have BONDERITE 1310ZAL in
it and that cell H8 does in fact have 600 in it.

I hope this helps

John





"JLatham" wrote:

I don't see the problem - maybe I'm not looking at it properly.

Book2.xls, Sheet1, cell C8 has "TEST" in it.

Book1.xls, Sheet1, cell A1 has formula =[Book2]Sheet1!$C$8 and displays TEST

on same sheet in Book1, at say H49, put formula
IF(A1="TEST",H81250/1250,"N/A")

or also in Book1, any sheet, any cell you could put:
=IF([Book2]Sheet1!$C$8="TEST,H81250/1250,"N/A")

But I see a (potential) problem with the reference H81250 - unless you are
using Excel 2007, that is an invalid row - rows in pre-Excel 2007 only go to
65536. It may be that if you fix that row reference it might work better for
you. You don't say what kind of error you are getting with it as it is.

"John Calder" wrote:

Hi

I have a link to another spreadsheet in a cell (C8) which returns a value
"TEST"

I want to use this value in another formula, in this case an IF statement.

Example

=IF(C8="TEST",H81250/1250,"N/A")

However i cant get the formulae to work because I suspect that even although
cell C8 returns a value of "TEST" that the formula is actually reading the
"link formula" which returns the result "TEST"

How do I get my formula to recognise the word "TEST" instead of the formula
that returns it?

Thanks in advance

John