Canadian Mac Forums at ehMac banner
1 - 8 of 8 Posts

·
Registered
Joined
·
482 Posts
Discussion Starter · #1 ·
I'd like to add a second sheet to an excel document, where the second sheet would be exactly the same as the first except it would automatically exclude any rows where there is a "1" in a certain column.

It's for a price list, where "1" in a certain column would designate "discontinued". The second sheet would exclude the items marked discontinued.

Possible?
 

·
Premium Member
Joined
·
1,797 Posts
It would probably be easy to do in a macro, but that requires a little programming skill.

If the second sheet can have blank rows where the discontinued items would be then you don't need a macro, but the data won't be bunched together.

Another option is to do a quick sort on the first page and copy and paste the sorted data to the second page. That would be pretty easy and fast and wouldn't require any special talent.

I can't think of any way to have the second sheet automatically show the condensed data without blank rows.
 

·
Premium Member
Joined
·
9,103 Posts
For a price list that is only published occasionally, doing this manually using sort takes less than a minute and is the way to go.

That way you get all the information on the second sheet in the same order as the first sheet and you will have no blank rows.
 

·
Registered
Joined
·
943 Posts
I'm not a pro, but in the past I've duplicated the first sheet and simply deleted the columns I didn't want.
 

·
Registered
Joined
·
482 Posts
Discussion Starter · #6 ·
Long term, you may consider moving to Filemaker. Filemaker would give you infinitely more power than Excel when handling things like inventory and price sheets.
I use FileMaker extensively but I don't believe you can export to excel with formatting (borders, colours, etc.). I could do a PDF, but I want buyers to be able to fill in the spreadsheet and send back.

I'll probably just copy to a second sheet and make the modifications each time... for now anyway.
 

·
Premium Member
Joined
·
9,103 Posts
I use FileMaker extensively but I don't believe you can export to excel with formatting (borders, colours, etc.). I could do a PDF, but I want buyers to be able to fill in the spreadsheet and send back.

I'll probably just copy to a second sheet and make the modifications each time... for now anyway.
How often do you have to generate this second sheet?

Doing it manually should only take a minute or so.

Copy first sheet and paste to second sheet
Select all of second sheet
Sort by the column with the "1"s. That puts all these items together on the spreadsheet
Select the block of rows with 1's in them and delete.
You're left with the price list you want with items in the correct order.

If you have your customers fill in the spreadsheet you should lock all the appropriate cells.
 

·
Registered
Joined
·
943 Posts
I just looked at the iWork Numbers tutorial on the apple website, and it has this sort function that can do exactly what you want. How it would export to excell might be an issue, but who knows? Try the downloadable demo and check it out!
 
1 - 8 of 8 Posts
This is an older thread, you may not receive a response, and could be reviving an old thread. Please consider creating a new thread.
Top