Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 1
Default 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!
  #2   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 473
Default Adding rows/columns to linked file

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
adding more columns/rows Jo Davis Excel Discussion (Misc queries) 4 September 24th 08 07:57 PM
adding specifics columns and rows yodochi Excel Worksheet Functions 1 July 25th 07 03:24 AM
Adding rows in linked spreadsheets Parcey Excel Discussion (Misc queries) 1 December 28th 05 07:28 AM
adding a row to a file will not add line to linked files bilcat1 Excel Discussion (Misc queries) 0 November 2nd 05 12:36 PM
Disable Adding or Deleting Rows and Columns Playa Excel Discussion (Misc queries) 1 January 10th 05 11:23 PM


All times are GMT +1. The time now is 03:01 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"