Adding rows/columns to linked file

I have 1 master workbook and about 20 children workbooks. The children are
excel spreadsheets with several rows and columns of budget data. The master
workbook is one huge spreadsheet with all of the data from the other children
on one sheet. ALL WORKBOOKS ARE FORMATTED IDENTICALLY. I have esatblished
links in the master by using copy,paste link from each child workbook. I use
the master combined summary workbook sheet to run pivots.

Ocassionally I have to add rows/column to the children, which naturally
throw off the master. To avoid this, I have attempted to use the following
formula that i have obtained from this discussion it is:

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 am not sure why the first part of the formula is
supposed to =0 and what, if anything is supposed to go in the " " following
the 0. Further, i also received a filename message. The master is open.

Next will this formula work if the data on the children is all different but
formatted the same? Will using the number 5 in the formula move rows 5
spaces over, or will it be up to 5 spaces over. can i use+ - at the same time
in front of the 5(orwhatever # I use) so that it will move up or down or left
or right?
Please help I think I have printed everythig about formulas and the offset
function!

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