G
Guest
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
(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