create view tabs

[buzzweetman]

In my workbook, my first worksheet contains rows of text.
Each row also has category columns that a user can put an "x" in, to
mark that row as being a member of the particular category.
A contrived example: (I'll use commas here to delimit columns)
,Mammal,Bird,White
Fido,X,,
Fluffy,X,,X
Polly,X,

So...
Fido is a mammal, not a bird, and not white.
Fluffy is a mammal, not a bird, and is white.
Polly is not a mammal, is a bird, and not white.

Now I want to create 3 other worksheet tabs called "Mammal", "Bird",
and "White"
I want each of these worksheets to dynamically show only the rows for
worksheet1 that apply.
So, for example, the the Mammal worksheet would show a row for Fido and
Fluffy. The Bird worksheet would have a row for Polly. And the White
worksheet would have a row for Fluffy.

I want some users of this workbook to enter a new row in the first
worksheet, and mark the categories that apply.
Later, other users should be able to choose the Mammal, Bird, or White
worksheet tab and see only the rows that are relevant.

Can someone suggest a way(s) to do this?
Thanks for any help.
Buzz

 

[Bernard Liengme]

I put your data on Sheet1
On sheet2 I type into B1 the word Mammal
In A2 I used the formula =IF(ISBLANK(Sheet1!A2),"",Sheet1!A2)
I copied this to B2 and the copied A2:B2 down 20 rows (could be any number)
Then I selected A1:B21 and used Data | Auto Filter
In the drop down on the Mammal header I selected "x" so only entries with an
"x" show on this sheet
Renamed the sheet as Mammal

Did similar thing with Sheet3 using in A2 and B2, the formulas
=IF(ISBLANK(Sheet1!A2),"",Sheet1!A2) and
=IF(ISBLANK(Sheet1!C2),"",Sheet1!C2)
in A2 and B2, respectively. Then Auto Filer for x in column C

Would be even easier with XL 2003 where on could use the List feature.

I liked your idea of using commas to delimited and overcome email problems
Please not a workbook is make of worksheets (and chartsheets); "tab" is the
name of the object used to open a worksheet.

best wishes

 

[buzzweetman]

Bernard,

Thanks for your suggestions. I wasn't aware of the AutoFilter feature.

But I have encountered a problem.
When a user enters a new rom on worksheet1, they Insert a blank row at
the top of the rows that are already there. Then they enter their text
and check the appropriate columns.

I found that the worksheet with the filter, the formulas automatically
change so that they refer to the rows they did before. What I mean
is... in my Mammals worksheet, a formula that once was:
=IF(ISBLANK(Sheet1!$A$2),"",Sheet1!$A$2)
becomes:
=IF(ISBLANK(Sheet1!$A$3),"",Sheet1!$A$3)

But I really don't want it to be that smart... because now there is no
formula in worksheet2 for the new row I inserted in worksheet1... so
it never shows up.

Did I explain that ok?
Any suggestions?

Buzz

 

[Bernard Liengme]

Change reference to Sheet1$A$2 to INDIRECT("Sheet1!A2")
OR tell users to add below last entry
best wishes