Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / November 2006

Tip: Looking for answers? Try searching our database.

create view tabs

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
buzzweetman@gmail.com - 07 Nov 2006 15:28 GMT
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 - 07 Nov 2006 15:50 GMT
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

Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

> 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
[quoted text clipped - 26 lines]
> Thanks for any help.
> Buzz
buzzweetman@gmail.com - 07 Nov 2006 17:06 GMT
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

> I put your data on Sheet1
> On sheet2 I type into B1 the word Mammal
[quoted text clipped - 53 lines]
> > Thanks for any help.
> > Buzz
Bernard Liengme - 07 Nov 2006 20:20 GMT
Change reference to Sheet1$A$2 to INDIRECT("Sheet1!A2")
OR tell users to add below last entry
best wishes
Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

> Bernard,
>
[quoted text clipped - 82 lines]
>> > Thanks for any help.
>> > Buzz
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.