H
Humpton
Hi there.
Hopefully my explanation of my dilemma makes sense...
I have the traditional database worksheet - about 20 columns of fields and
about 1200 rows of data. One of the fields is a classification code which
using AutoFilter is easy enough to see all the 'A' codes or 'B' codes or
whatever.
The mandate I have to work with, is that as well as this master worksheet, I
need to have a separate worksheet, one of each of the classification codes.
My goal is to have the separate worksheets update automagically when a new
record is added into the master worksheet.
I feel like I'm close, but can't get past a last hurdle. Although I can use
a macro to achieve the result I'm after, it is preferred to not have macros
at all.
What I've done is basically use a named range to copy the whole Master to
each of the other worksheets and then use AutoFilter on them. Not the most
efficient (and I'll listen to alternatives to this too) but disk space is not
an issue - fortunately. The hurdle is that when a row is inserted into the
master sheet the hidden rows don't change, but rather just show what is in
them after the insertion. If row 1 has 'Y', 2 has 'Z' and the AutoFilter
only shows row 2, if a row is inserted between 1 and 2 which contains an 'X'
the AutoFilter will now show 'X', because that is in row 2. Any other sheets
that were somehow showing Row 3 would also change and have the 'Z' showing.
Any tips for getting around this (based on the assumption you understand
what I'm asking)?
Thanks!
Stay JOLLY!
H
Hopefully my explanation of my dilemma makes sense...
I have the traditional database worksheet - about 20 columns of fields and
about 1200 rows of data. One of the fields is a classification code which
using AutoFilter is easy enough to see all the 'A' codes or 'B' codes or
whatever.
The mandate I have to work with, is that as well as this master worksheet, I
need to have a separate worksheet, one of each of the classification codes.
My goal is to have the separate worksheets update automagically when a new
record is added into the master worksheet.
I feel like I'm close, but can't get past a last hurdle. Although I can use
a macro to achieve the result I'm after, it is preferred to not have macros
at all.
What I've done is basically use a named range to copy the whole Master to
each of the other worksheets and then use AutoFilter on them. Not the most
efficient (and I'll listen to alternatives to this too) but disk space is not
an issue - fortunately. The hurdle is that when a row is inserted into the
master sheet the hidden rows don't change, but rather just show what is in
them after the insertion. If row 1 has 'Y', 2 has 'Z' and the AutoFilter
only shows row 2, if a row is inserted between 1 and 2 which contains an 'X'
the AutoFilter will now show 'X', because that is in row 2. Any other sheets
that were somehow showing Row 3 would also change and have the 'Z' showing.
Any tips for getting around this (based on the assumption you understand
what I'm asking)?
Thanks!
Stay JOLLY!
H