Linked worksheets
 

I have a problem regarding linking records in one
worksheet to another where the original data has headings
on some rows, not all the rows are required and where
additional rows are regularly added.

I apologise for this post being so long, but I have tried
to explain exactly what I'm trying to acheive.

I have data in worksheet 1 that appears as follows:

Ref Company Date Amount
Category 1
456/100/001C Bloggs & Co 12/12/03 £2000
456/100/001LC Bloggs & Co 12/12/03 £4000
456/100/001LT Bloggs & Co 12/12/03 £1500
12/101/002LT Smith & Co 15/11/03 £1000
12/101/002LC Smith & Co 15/11/03 £3000
Sum Category 1 £11500

Withdrawn Category 1
789/100/001T Co & Co 12/12/03 £2000
789/100/002T Cob & Co 12/12/03 £1000
Sum of withdrawn Category 1 £3000

Category2
456/100/002T ABC 14/12/03 £1000
456/100/006C ABC 14/12/03 £4000
12/101/003C Alpha Beta 22/11/03 £1000
Sum Category 2 £6000

Withdrawn Category 2
456/100/100C Peter Piper 22/12/03 £1500
456/100/200C Smith Bros 22/12/03 £1500
Sum of withdrawn Category 2 £3000

etc etc...

I do not want to incorporate the withdrawn records, and I
want to ensure that when a new record is added it is
picked up in the linked worksheet.

So, I want worksheet 2 to look like:
Ref Company Date Amount
456/100/001C Bloggs & Co 12/12/03 £2000
456/100/001LC Bloggs & Co 12/12/03 £4000
456/100/001LT Bloggs & Co 12/12/03 £1500
12/101/002LT Smith & Co 15/11/03 £1000
12/101/002LC Smith & Co 15/11/03 £3000
456/100/002T ABC 14/12/03 £1000
456/100/006C ABC 14/12/03 £4000
12/101/003C Alpha Beta 22/11/03 £1000

This will then enable me to link this table directly to an
Access database.

The easiest solution I initially thought of was to have
the records in different worksheets so the 'withdrawn'
records were separate. But the worksheet 1 data is
currently linked to another worksheet that is required for
other reports, so I don't want to change the original
format.

Can anyone help me?

Linked worksheets
 

Hello Amanda,

You have not given us the Excel version you are working with. I am working
with Excel 2003 and when I copy the data on sheet 1 that I want to transfer
to sheet 2 and paste it there, a little tag appears which has an option
called 'Link Cells'.

You may also want to check out the Camera feature (look in Excel help)

Hope this helps,
Amit

"Amanda" wrote in message
...
I have a problem regarding linking records in one
worksheet to another where the original data has headings
on some rows, not all the rows are required and where
additional rows are regularly added.

I apologise for this post being so long, but I have tried
to explain exactly what I'm trying to acheive.

I have data in worksheet 1 that appears as follows:

Ref Company Date Amount
Category 1
456/100/001C Bloggs & Co 12/12/03 £2000
456/100/001LC Bloggs & Co 12/12/03 £4000
456/100/001LT Bloggs & Co 12/12/03 £1500
12/101/002LT Smith & Co 15/11/03 £1000
12/101/002LC Smith & Co 15/11/03 £3000
Sum Category 1 £11500

Withdrawn Category 1
789/100/001T Co & Co 12/12/03 £2000
789/100/002T Cob & Co 12/12/03 £1000
Sum of withdrawn Category 1 £3000

Category2
456/100/002T ABC 14/12/03 £1000
456/100/006C ABC 14/12/03 £4000
12/101/003C Alpha Beta 22/11/03 £1000
Sum Category 2 £6000

Withdrawn Category 2
456/100/100C Peter Piper 22/12/03 £1500
456/100/200C Smith Bros 22/12/03 £1500
Sum of withdrawn Category 2 £3000

etc etc...

I do not want to incorporate the withdrawn records, and I
want to ensure that when a new record is added it is
picked up in the linked worksheet.

So, I want worksheet 2 to look like:
Ref Company Date Amount
456/100/001C Bloggs & Co 12/12/03 £2000
456/100/001LC Bloggs & Co 12/12/03 £4000
456/100/001LT Bloggs & Co 12/12/03 £1500
12/101/002LT Smith & Co 15/11/03 £1000
12/101/002LC Smith & Co 15/11/03 £3000
456/100/002T ABC 14/12/03 £1000
456/100/006C ABC 14/12/03 £4000
12/101/003C Alpha Beta 22/11/03 £1000

This will then enable me to link this table directly to an
Access database.

The easiest solution I initially thought of was to have
the records in different worksheets so the 'withdrawn'
records were separate. But the worksheet 1 data is
currently linked to another worksheet that is required for
other reports, so I don't want to change the original
format.

Can anyone help me?

Linked worksheets
 

Oops, sorry. I'm using Excel 2000. I've just tried
copying and pasting a number of rows but I don't see any
tag. Is this a 2003 feature? Will look in Excel Help.

Thanks,
Amanda

-----Original Message-----
Hello Amanda,

You have not given us the Excel version you are working
with. I am working
with Excel 2003 and when I copy the data on sheet 1 that
I want to transfer
to sheet 2 and paste it there, a little tag appears which
has an option
called 'Link Cells'.

You may also want to check out the Camera feature (look
in Excel help)

Hope this helps,
Amit

"Amanda" wrote in
message
...
I have a problem regarding linking records in one
worksheet to another where the original data has headings
on some rows, not all the rows are required and where
additional rows are regularly added.

I apologise for this post being so long, but I have tried
to explain exactly what I'm trying to acheive.

I have data in worksheet 1 that appears as follows:

Ref Company Date Amount
Category 1
456/100/001C Bloggs & Co 12/12/03 £2000
456/100/001LC Bloggs & Co 12/12/03 £4000
456/100/001LT Bloggs & Co 12/12/03 £1500
12/101/002LT Smith & Co 15/11/03 £1000
12/101/002LC Smith & Co 15/11/03 £3000
Sum Category 1 £11500

Withdrawn Category 1
789/100/001T Co & Co 12/12/03 £2000
789/100/002T Cob & Co 12/12/03 £1000
Sum of withdrawn Category 1 £3000

Category2
456/100/002T ABC 14/12/03 £1000
456/100/006C ABC 14/12/03 £4000
12/101/003C Alpha Beta 22/11/03 £1000
Sum Category 2 £6000

Withdrawn Category 2
456/100/100C Peter Piper 22/12/03 £1500
456/100/200C Smith Bros 22/12/03 £1500
Sum of withdrawn Category 2 £3000

etc etc...

I do not want to incorporate the withdrawn records, and I
want to ensure that when a new record is added it is
picked up in the linked worksheet.

So, I want worksheet 2 to look like:
Ref Company Date Amount
456/100/001C Bloggs & Co 12/12/03 £2000
456/100/001LC Bloggs & Co 12/12/03 £4000
456/100/001LT Bloggs & Co 12/12/03 £1500
12/101/002LT Smith & Co 15/11/03 £1000
12/101/002LC Smith & Co 15/11/03 £3000
456/100/002T ABC 14/12/03 £1000
456/100/006C ABC 14/12/03 £4000
12/101/003C Alpha Beta 22/11/03 £1000

This will then enable me to link this table directly to an
Access database.

The easiest solution I initially thought of was to have
the records in different worksheets so the 'withdrawn'
records were separate. But the worksheet 1 data is
currently linked to another worksheet that is required for
other reports, so I don't want to change the original
format.

Can anyone help me?


.