Fixed cell references

[rhythm_man]

I want to create a form for purchase requisitions that is a spreadsheet
(using Excel 97) with 4 work sheets.

The first sheet is where the data will be entered, one row per item to be
purchased.
The other sheets will be fully formatted for printing/faxing and will
include company logo, delivery information, order numbers etc.

What I want to do is for the formatted sheets to reference various ranges of
the data entered on the first sheet.
i.e. the first formatted sheet references the data in worksheet1 cells A1:C20.
The second formatted sheet references the data in worksheet 1 cells A21:C40
and similarly for the 3rd formatted sheet, cells A41:C60.

I'd like the users to be able to cut/copy/paste cells around on the data
sheet (worksheet1), or even insert new rows, and for these changes to be
reflected in the formatted sheets
i.e. if a user moves an item 3 rows down on the data sheet by
cutting/pasting the cells, then it will also appear 3 rows lower down on the
relevant formatted sheet. If the change made the row change from row 19 to
row 22, then this item would now appear on the second formatted sheet,
instead of the first.

Neither relative nor absolute cell references seem to achieve this.
e.g. if a new row is inserted on the data sheet, these new cells were never
originally refernced on any of the formatted sheets and so do not appear
there now.
Is there another function, or some other technique for achieving this?

TIA
John

[B. R.Ramachandran]

Hi,

This might work.

In the cell A1 of Sheet 2 enter the formula,
=OFFSET(Sheet1!$A$1,ROW(A1)-1,COLUMN(A1)-1)
and fill-down the formula in A1-C20
For the sheets that follow (i.e., Sheet 3, 4, .....) the formula in A1 is
similar with a minor modification (with increments of 20 on the right hand
side of 'ROW(A1)+'.
=OFFSET(Sheet1!$A$1,ROW(A1)+19,COLUMN(A1)-1)
=OFFSET(Sheet1!$A$1,ROW(A1)+39,COLUMN(A1)-1)
=OFFSET(Sheet1!$A$1,ROW(A1)+59,COLUMN(A1)-1)
etc., etc.
One caveat is, you have to format the sheets after you fill in these formulas.

Regards.
B. R. Ramachandran


"rhythm_man" wrote:

> I want to create a form for purchase requisitions that is a spreadsheet
> (using Excel 97) with 4 work sheets.
>
> The first sheet is where the data will be entered, one row per item to be
> purchased.
> The other sheets will be fully formatted for printing/faxing and will
> include company logo, delivery information, order numbers etc.
>
> What I want to do is for the formatted sheets to reference various ranges of
> the data entered on the first sheet.
> i.e. the first formatted sheet references the data in worksheet1 cells A1:C20.
> The second formatted sheet references the data in worksheet 1 cells A21:C40
> and similarly for the 3rd formatted sheet, cells A41:C60.
>
> I'd like the users to be able to cut/copy/paste cells around on the data
> sheet (worksheet1), or even insert new rows, and for these changes to be
> reflected in the formatted sheets
> i.e. if a user moves an item 3 rows down on the data sheet by
> cutting/pasting the cells, then it will also appear 3 rows lower down on the
> relevant formatted sheet. If the change made the row change from row 19 to
> row 22, then this item would now appear on the second formatted sheet,
> instead of the first.
>
> Neither relative nor absolute cell references seem to achieve this.
> e.g. if a new row is inserted on the data sheet, these new cells were never
> originally refernced on any of the formatted sheets and so do not appear
> there now.
> Is there another function, or some other technique for achieving this?
>
> TIA
> John

[rhythm_man]

Thanks very much for that....seems to do the trick!

regards,
J. Harland

"B. R.Ramachandran" wrote:

> Hi,
>
> This might work.
>
> In the cell A1 of Sheet 2 enter the formula,
> =OFFSET(Sheet1!$A$1,ROW(A1)-1,COLUMN(A1)-1)
> and fill-down the formula in A1-C20
> For the sheets that follow (i.e., Sheet 3, 4, .....) the formula in A1 is
> similar with a minor modification (with increments of 20 on the right hand
> side of 'ROW(A1)+'.
> =OFFSET(Sheet1!$A$1,ROW(A1)+19,COLUMN(A1)-1)
> =OFFSET(Sheet1!$A$1,ROW(A1)+39,COLUMN(A1)-1)
> =OFFSET(Sheet1!$A$1,ROW(A1)+59,COLUMN(A1)-1)
> etc., etc.
> One caveat is, you have to format the sheets after you fill in these formulas.
>
> Regards.
> B. R. Ramachandran
>
>
> "rhythm_man" wrote:
>
> > I want to create a form for purchase requisitions that is a spreadsheet
> > (using Excel 97) with 4 work sheets.
> >
> > The first sheet is where the data will be entered, one row per item to be
> > purchased.
> > The other sheets will be fully formatted for printing/faxing and will
> > include company logo, delivery information, order numbers etc.
> >
> > What I want to do is for the formatted sheets to reference various ranges of
> > the data entered on the first sheet.
> > i.e. the first formatted sheet references the data in worksheet1 cells A1:C20.
> > The second formatted sheet references the data in worksheet 1 cells A21:C40
> > and similarly for the 3rd formatted sheet, cells A41:C60.
> >
> > I'd like the users to be able to cut/copy/paste cells around on the data
> > sheet (worksheet1), or even insert new rows, and for these changes to be
> > reflected in the formatted sheets
> > i.e. if a user moves an item 3 rows down on the data sheet by
> > cutting/pasting the cells, then it will also appear 3 rows lower down on the
> > relevant formatted sheet. If the change made the row change from row 19 to
> > row 22, then this item would now appear on the second formatted sheet,
> > instead of the first.
> >
> > Neither relative nor absolute cell references seem to achieve this.
> > e.g. if a new row is inserted on the data sheet, these new cells were never
> > originally refernced on any of the formatted sheets and so do not appear
> > there now.
> > Is there another function, or some other technique for achieving this?
> >
> > TIA
> > John