Budgetgal wrote:
IF(OFFSET([PracticeFile.xls]OIS FY09 Worksheet!
$A9$,ROW(A9)5,COLUMN(A9)5)=0," ",OFFSET( [PracticeFile.xls]OIS FY09
Worksheet!$9,COLUMN(A9)5))

the formula wont work.


I'm not surprised. It is syntactically incorrect in many ways.
Either you have copied it incorrectly from where you first saw it, or
copied it incorrectly from your workbook, or the person who wrote it
didn't attempt to test it.

A better solution could be to look at Data Consolidate.
It is designed to take information from similarly formatted worksheets
and add the results together.

But returning to the formula, the general structure is
=IF(x=0," ",x)
where x involves a link to another location - this is just to ensure
that empty cells in the source (which would otherwise be shown as 0 by
the formula) appear to be empty in the destination.

In your case the two occurrences of x are actually different and both
are syntactically incorrect. Taking the first one:

OFFSET([PracticeFile.xls]OIS FY09 Worksheet!$A9$,ROW(A9)5,COLUMN(A9)5)
The worksheet name contains spaces so the whole workbook+worksheet name
needs to be enclosed in '...'

OFFSET('[PracticeFile.xls]OIS FY09
Worksheet'!$A9$,ROW(A9)5,COLUMN(A9)5)
(this may have wrapped onto 2 lines - it would by one line in practice)

The $A9$ is not a valid address. $ can appear before a column
designator or row number to make it absolute - it will not change as
the formula if copied to other cells. So it should be $A$9
OFFSET('[PracticeFile.xls]OIS FY09
Worksheet'!$A$9,ROW(A9)5,COLUMN(A9)5)

The 5s create syntactically incorrect expressions. you could have
ROW(A9)+5 for example, but not ROW(A9)5. I don't understand why they
were there so I suggest we take them out and see what the formula means

OFFSET('[PracticeFile.xls]OIS FY09 Worksheet'!$A$9,ROW(A9),COLUMN(A9))

What does this formula part mean?
Well, ROW(A9) returns the row number of A9, i.e. 9
COLUMN(A9) returns the column number of A9, i.e. 1
OFFSET(x,y,z) gives the cell y rows down and z columns to the right
from the cell x.
So the formula part (in this instance) is equivalent to
'[PracticeFile.xls]OIS FY09 Worksheet'!B18

When you copy the formula to other cells in the destination worksheet
the A9 references will adjust and the $A$9 will not.
So when you copy the formula to the cell to the right it will be
accessing '[PracticeFile.xls]OIS FY09 Worksheet'!C18 and when you copy
it down a cell from there it will be accessing '[PracticeFile.xls]OIS
FY09 Worksheet'!C19

I doubt that this address was what was intended, but I don't know how
your data is laid out.



Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup