Adding to already linked records

[Amanda]

Hello All,

I'm using Excel 2000 and have a number of specific records
in one worksheet that i have linked to another by Copy >
Paste Specialaste Link.

Please can someone explain to me how I can add a record to
the original worksheet and for it to appear automatically
on the other worksheet? This isn't currently happening, so
have I linked the records incorrectly?

Many thanks,
Amanda

 

[Arvi Laanemets]

Hi

Link leftmost upper cell of source range to cell in target workbook - be
sure, that relative references (no $ signs) are used.
Modify the link as follows:
=IF(Link="","",Link)
(otherwise empty cells are linked as 0's)
Copy the cell with link in it to range where you want the linked values to
appear - it may include rows, where values appear in future too.

NB! Some functions return error, when source datarange has empty rows. So
when you want to use the range with such links in formulas, then you better
define dynamic named range(s) and refer in formulas to it/them.

 

[Amanda]

Hi,

I should have explained that I need to insert new records
in the original worksheet that should appear on the new
worksheet with linked records.

Is this possible?

 

[Amanda]

Hi,

I should have explained that I need to insert new records
in the original worksheet that should appear on the new
worksheet with linked records.

Is this possible?

 

[Arvi Laanemets]

Hi

But they do!

An example
On Sheet1, you have table FirstName, LastName, Birthday (the first row is
header row)- let it be empty exept headers at start.
On Sheet2, into A1 enter the formula
=IF(Sheet1!A1="","",Sheet1!A1)
Copy the formula in Sheet2!A1 to range p.e. Sheet2!A1:C100
Now you see on Sheet2 also headers for table like on Sheet1
Enter some entries into table on Sheet1 - as long as you don't go beyond
100th row, all entries are duplicated on Sheet2.

Or do you need something different?

 

[Paul]

Hello All,

I'm using Excel 2000 and have a number of specific records
in one worksheet that i have linked to another by Copy >
Paste Specialaste Link.

Please can someone explain to me how I can add a record to
the original worksheet and for it to appear automatically
on the other worksheet? This isn't currently happening, so
have I linked the records incorrectly?

Many thanks,
Amanda

If I understand you correctly, you cannot do what you are asking for. As a
simple example, if you copy 5 cells and then use Paste Links, all you are
doing is to paste 5 formulas in one go. Without repeating the process after
adding further data, 5 is all you will ever have. If you want a sixth link,
you will have to add a sixth formula separately, and so on.

Excel is a spreadsheet, not a database. It makes no assumptions about data
added to the rows below data you currently have. In a database, all cells in
a 'column' represent the same piece of data for different records. This is
not so for a spreadsheet. It may be that you would be better off using a
database.

 

[Amanda]

Hi,

Thank you for your reply. I'll try and explain what I'm
trying to achieve.

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 (inserted)
at the end of each category 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.

Any suggestions for this?

 

[Amanda]

Hi,

Thanks for your reply, and I understand what you're
saying. The spreadsheet is the basis for a linked table
in Access, but in its current format is not compatible -
this is why I was trying to pull out the necessary records
using links from one worksheet to another.

Apart from changing the way the original spreadsheet is
used do you think I'm wasting my time in trying to
overcome this problem?

Thanks,
Amanda

 

[Arvi Laanemets]

Hi

I see now your problem more clearly. Your main problem is that you haven't
designed your workbook as database, and as result it's very difficult to
make it behave as one. (By 'designing a workbook as database' I mean that
whenever a table is editable, it's placed on separate worksheet.)

So when you aren't stucked with current design, maybe you consider some
changes.
You can design your input table as
Category, Ref, Company, Date, Amount
with withdrawn table having similar structure on different sheet. Or you use
single input table with an additional field (Withdrawn?) in it. Ref's are
entered in order they appear. To get information about certain category, you
can use Autofilter feature, or you design a report sheet, where you select a
category (and maybe other parameters too) and wanted information, based on
entries in input table, is displayed using formulas. You can even get a
continous list of entries for category into report sheet, using a couple of
hidden columns in input table - such design was lately described by me in
same NG here (Thread: Crystal Harriman, Copying from data, 11.12.2003 7:54).

With such design, you can link table(s) to Access database (or get data into
Access using ODBC query) without any problems.


--
(When sending e-mail, use address (e-mail address removed))
Arvi Laanemets



Hi,

Thank you for your reply. I'll try and explain what I'm
trying to achieve.

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 (inserted)
at the end of each category 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.

Any suggestions for this?