Defining names
Posted by Jeff Mischler on August 27, 2001 7:36 AM
I am trying to get Excel to Autofilter based on a bold font. Here's my setup, I have a spreadsheet that we use for inventory, we have set minimums on our inventory using conditional formatting so when an item drops below a certain level its bolded and also has a color background. This helps alot, now we want to be able to autofilter the bolded font down at a smaller list that we can then print out. I found an example on the Microsoft web site with an exact example of this. In the example you define a "Name" that has a formula to filter on bold font, here it is,
=GET.CELL(20,OFFSET(INDIRECT("A2"),ROW()-2,0))
In the worksheet then you have in two cells
Bold?
=bold_cell
This example works great, but I need help in modifying the formula to work with our spreadsheet. Our quantities are in Column D, about rows 7-343, thats the column we want to filter. If anyone can help with this it would be greatly appreciated! Hope this is enough infomation, thanks in advance!
Jeff
Posted by Aladin Akyurek on August 27, 2001 7:40 AM
Why not use the conditions that you used for conditional formatting as criteria in Advanced AutoFilter?
Aladin
Posted by lenze on August 27, 2001 7:44 AM
Jeff: The example from Microsoft will not work for you. It uses the root format of the cells to sort. You are using conditional formatting, which is NOT the same. That said, however, if you can create a conditional format, you can use the same criteria in a criteria range for an Advance Filter. See Excel help for examples.
Posted by Jeff Mischler on August 27, 2001 11:56 AM
Thanks for this tip, I understand what your suggesting, and have tried it. I set the criteria range, and I understand how to filter on text and such, but do not understand how to filter on a bolded font? I tried several formula's such as
ISBOLD=cell.Range("D7-343").Font.Bold
but this did not work, any more suggestions?
Thanks in advance for your time and consideration on this!
Jeff
Posted by lenze on August 27, 2001 12:11 PM
Jeff: Aladin is not suggesting you filter on the font, but on the condtion that causes the font to be Bold. If you use conditional formatting to turn values Bold that are <5, then use <5 in your criteria
Posted by Aladin Akyurek on August 27, 2001 12:39 PM
Jeff,
Here is an example that shows what I have in mind.
{"Inventory","","","";
"<=50","","","";
"","","","";
"","","",";
"Product","Inventory","Price","Date";
"p1",100,2.5,"d1";
"p2",50,4.6,"d1";
"p3",80,7.9,"d2"}
The above sample occupies A1:D8.
The actual data occupies the range A5:D8.
Assuming that we want to select rows of data where Inventory is less than or equal to 50. (Note that this criterion could be used in conditional formatting to render e.g., in bold the inventory levels that meet the aforementioned criterion.)
In A1 enter the FIELD/LABEL of interest from the data range as it appears in this range.
In A2 enter: <=50 [ Note that this is entered literally as text ]
Activate A5.
Activate Data|Filter|Advanced Filter...
On Advanced Filter window, you'll see a List Range already filled in (if needed, you can adjust the range address).
As Criteria Range enter: $A$1:$A$2
As Action, activate Copy to another location and as value of Copy to enter a cell address (one with enough free cells around, that is, a range that can house the selection in the same or another worksheet).
And click OK.
You're done.
If unsure, post the condition(s)/criteria on ground(s) of which you bold a cell value (those used in conditional formatting)?
Aladin
Posted by Jeff Mischler on August 27, 2001 12:52 PM
:i will give this a try, thanks for all your help on this!
Jeff
Jeff,
