Tom,

I don't understand.
Why does it return 0 if I say return 'Sheet3!I46?

=IF(ISERROR(I45*'Sheet2'!I47/1000),'Sheet3!I46,I45*'Sheet2'!I47/1000)

IF I45*'Sheet2'!I47/1000 ISERROR, then pick up the value in 'Sheet3!I46,
otherwise return the result ot the operation, that is I45*'Sheet2'!I47/1000.

Where am I wrong? :(

Alex



"Tom Ogilvy" wrote:

If Sheet2 is empty, the cell in Sheet1 will give no value (empty cell)

that is not the case with the formula you show. With the formula you show,
it returns 0 if the cell (Sheet2!I47) is empty.

--
Regards,
Tom Ogilvy


"Metallo" wrote in message
...
Tom,

I think there's a problem in your formula, probably I did not explain well
enough.
Let's try again.

In a cell of Sheet1 (which is the cell which has to give the result)
there's
this formula:
=IF(ISERROR(I45*'Sheet2'!I47/1000),0,I45*'Sheet2'!I47/1000)
If Sheet2 is empty, the cell in Sheet1 will give no value (empty cell)

If there is no value then the cell in Sheet1 should pick up a value from
Sheet3, this value is a plain value to be picked up.
From your formula, it seems to me that you multiply I45*Sheet3, but in
reality I just need my cell to pick up the value as it is in that cell of
Sheet3.

Hope it's clearer

Thank you
Alex

"Tom Ogilvy" wrote:


=if(len(trim('Sheet2'!I47))=0,IF(ISERROR(I45*'Shee t3'!I47/1000),0,I45*'Sheet
3'!I47/1000),IF(ISERROR(I45*'Sheet2'!I47/1000),0,I45*'Sheet2'!I47/1000))

--
Regards,
Tom Ogilvy


"Metallo" wrote in message
...
Hi,

I have 11 WBs structured exactly the same way.
Every WB contains 30 WSs, the WSs Tab names are the same for every WB.

Now the problem:

One WS (Sheet1) gives results performing a calculation that involves
another
WS (Sheet2).
Below is a typical formula in a cell

=IF(ISERROR(I45*'Sheet2'!I47/1000),0,I45*'Sheet2'!I47/1000)

Everything works fine if in 'Sheet2'!I47 there's a value.
But if there's no value, obviously what I get in the WS (Sheet1) is an
empty
cell.
Mathematically is correct, but what I need instead is the following:
If there's no value, then I want the cell in Sheet1 to pick up another
value
in another WS (Sheet3).
Basically the instruction should be:
If you don't find any value in 'Sheet2'!I47 then put the value that
you
can
find in Sheet3!A1

Probably I can do this in two ways:

1) Changing the worksheet formula, but I don't know how.
2) Programmatically (also no idea how)

Thats where I need your advise, since I discovered this problem by
chance,
I
really don't know if this happens somewhere else in the other WSs and
where.
Therefore, I need something that I can apply to all my WBs and
automatically
apply the new formula were it is needed.

I hope this is clear enough and allows you to give me a good solution.

Thank you!
Alex