D
David Morrison
I have the details of all the members of a club in a spreadsheet. This
includes names, address, phone numbers, birth date and several other
things. Needless to say it is far too wide to be able to be printed, or
even conveniently viewed on a computer screen.
I have been trying to use additional worksheets to display subsets of
the information by just having all the cells with formulas referring to
the columns on the main worksheet. This partly works.
However, there is a problem. If I add extra rows to the main worksheet,
they do not seem to come automatically through to the secondary ones.
Instead, the cell references on the secondary worksheet are adjusted to
refer to the original rows, but the new row never gets there unless I
redefine the formulas for the whole column.
For example, on the secondary worksheet, a column will look like this
before I add the row:
='sheet1'!A2
='sheet1'!A3
='sheet1'!A4
='sheet1'!A5
If I insert a new row 4 on the main worksheet, I end up with this on the
secondary one:
='sheet1'!A2
='sheet1'!A3
='sheet1'!A5
='sheet1'!A6
There is no reference to A4 now.
So I am after ideas on how to get the result I want. I'm not sure that
it can actually be done in Excel, but it would be very convenient if it
could, as other people have to be able to look after this when I am
away, and Excel is fairly easy to understand.
I will add that some of the secondary worksheets will use Autofilter to
allow just the non-blank rows to be displayed. This is likely to make it
even more complicated....
This is being done in Excel 2004, but I could upgrade if the
functionality was available in a newer version.
Thanks for any help.
David
includes names, address, phone numbers, birth date and several other
things. Needless to say it is far too wide to be able to be printed, or
even conveniently viewed on a computer screen.
I have been trying to use additional worksheets to display subsets of
the information by just having all the cells with formulas referring to
the columns on the main worksheet. This partly works.
However, there is a problem. If I add extra rows to the main worksheet,
they do not seem to come automatically through to the secondary ones.
Instead, the cell references on the secondary worksheet are adjusted to
refer to the original rows, but the new row never gets there unless I
redefine the formulas for the whole column.
For example, on the secondary worksheet, a column will look like this
before I add the row:
='sheet1'!A2
='sheet1'!A3
='sheet1'!A4
='sheet1'!A5
If I insert a new row 4 on the main worksheet, I end up with this on the
secondary one:
='sheet1'!A2
='sheet1'!A3
='sheet1'!A5
='sheet1'!A6
There is no reference to A4 now.
So I am after ideas on how to get the result I want. I'm not sure that
it can actually be done in Excel, but it would be very convenient if it
could, as other people have to be able to look after this when I am
away, and Excel is fairly easy to understand.
I will add that some of the secondary worksheets will use Autofilter to
allow just the non-blank rows to be displayed. This is likely to make it
even more complicated....
This is being done in Excel 2004, but I could upgrade if the
functionality was available in a newer version.
Thanks for any help.
David