Free Microsoft Excel 2013 Quick Reference

Excel 2007 - Icon Sets

Hi everyone,
I'm new on here and hoping that one of you kind people will be able to help me.
I am creating a spreadsheet in a trial version of excel 2007 and am trying to use the icon sets to alert the user if something is:
1. Overdue and hasn't been done (Red circle)
2. Not overdue and hasn't been done (orange circle)
3. Done (green)

I have used the following formula for the cell:
=IF(AND(F4="",G4=""),"",IF(AND(TODAY()>F4,G4=0),"400",IF(AND(F4>=TODAY(),G4=0),"200","0")))
This creates values of nothing if the cells are empty, 400, 200 or 0.

The conditional formatting used is shown on the attached file.

However, this isn't working and I can't see where I am going wrong.
Can anyone help?

Cheers,

Phil


Post your answer or comment

comments powered by Disqus
That's my task:

I have Excel files build with HTML content having the file extension .xls

<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">

For older Excel versions the file will be opened as a "normal" Excel document.

Since Excel 2007 there are two problems I have to find a solution for:

1. Stop the startup extension warning because the file content doesn't match the extension.
I solved this problem with a registry entry that stops the extension check (see: http://blogs.msdn.com/b/vsofficedeve...n-warning.aspx)

2. Set "save as" file extension depend on file content type
When the user changes the document (the document with the html content) and selects "save as" in the file type dropdown "html" is selected.
This selection depends on the file content (I tried it with xml. In this case "xml" is selected).
But the user always want to save the file as .xlsx so it's a useless work to change the type every time.

-> How can I set the default "save as" file extension for html content to .xlsx?

Thank you very much!

An Excel 2003 file has spreed sheets and chart sheets visible when opened
with Excel 2003, but some of the chart sheets are not present when the file
is opened in my Excel 2007 -- however, some are present! I sent the file to
someone else who has Excel 2007 and he is able to see all of the chart sheets
on his Excel 2007. What settings should I be tweaking to see all of the
chart sheets?

Hi there,
I have Excel 2007 worksheets set up to "Fit to Page" for printing. When I
try to print via the fax software that is installed with Vista Business
Edition, it starts to spool up thousands of pages. I think it is trying to
print each cell as a single page. I have tried manually highlighting the
area that is to be printed and selecting "Print Selection" in the printing
dialog box, but that doesn't work either.

When I print directly to my printer, for a paper output, it works fine.

I would like to start using Vista more, but have to keep rebooting into XP
everytme I need to fax something.

Any ideas? Thanks in advance.

Joe

In Excel 2007, I set up a pivot table based on a range of cells in
another spreadsheet within my workbook. I put that data into a SQL
table and now want to base the pivot table on that external data
source.

When I go to the change data source option in the pivot table menu, in
the resultant dialog box, the radio button to connect to external data
source is grayed out. What am I missing?

Bart

In Excel 2007, I set up a pivot table based on a range of cells in
another spreadsheet within my workbook. I put that data into a SQL
table and now want to base the pivot table on that external data
source.

When I go to the change data source option in the pivot table menu, in
the resultant dialog box, the radio button to connect to external data
source is grayed out. What am I missing?

Bart

Hi there,

Does anyone know why I get the following error when exporting data from my
VB app to Excel 2007 ONLY WHEN the default save file format in Excel 2007 is
set to "Excel 97-2003 workbook (*.xls)"?

"Excel cannot insert the sheets into the destination workbook, because it
contains fewer rows and columns than the source workbook. To move or copy the data to the destination workbook...of anmother workbook. (Code: 1004)"

No error is obtained using the same process when the default save file as is
the default 2007 "xlsx" or "Excel 5.0/95 Workbook (*.xls)"...
--
thanks in advance.
smacd...

Can I customize my own set of conditional formatting icon in Excel 2007? If so, how?
thanks in advance.

Hello,
Does anyone knows how to create icon sets for conditionnal formating with
Excel 2007 ?

In Excel 2007, new feature of conditional formatting is the "icon set" in
which you can assign an icon on a particular value or range of values. i just
what to know how does the default condition of icon set work? by default,
excel is using the 67% and 33% for 3 iconset but i want to know 67% of what
or 33% of what? how did they compute for that? how did they get it or how did
they compute it? i tried several ways of computing it using the 67% and 33%
but still it doesnt fit the default conditional formatting for icon set. is
it the 67% or 33% of the total of the range or what? im really confused. i
really need to know the answer coz for sure my students will ask me about it.
hope someone can help me about this...

thanks! ^_^

In Excel 2007, new feature of conditional formatting is the "icon set" in
which you can assign an icon on a particular value or range of values. i just
what to know how does the default condition of icon set work? by default,
excel is using the 67% and 33% but i want to know 67% of what or 33% of what?
how did they compute for that? i tried many ways but i cant get it. i hope
someone can enlighten my confused mind. thanks!

^_^

Hi,

I wonder if you can help?

At work I use Excel 2007 on my PC which is connected to a windows 2003
server.

I have opened up all the Macro/VBA security settings on Excel 2007 but I
can still not run macros or visual basic editor. The related icons on
the Developer tab on the ribbon remained greyed out.

I have asked the network guy at work if there is something on the
windows 2003 server that is preventing me from running Macros or the VBA
editor on my PC.
All I got from the network guy was a blank sustained glare.

Is there some secret setting 'somewhere' on the PC or the Server that is
preventing me from running Macros or VBA editor in Excel 2007?

Thanks for your help

Martin

Hi,

I am trying to program Icon Sets in excel for a dashboard and am having a hard time trying to work it out. I have looked at MSDN and have been working with an example but everything I am doing seems to either break the program or does not have the desired impact

Sub CreateIconSetCF()
Dim cfIconSet As IconSetCondition

' Fill cells from C1 to C12 with sample data.
With ActiveSheet
   .Range("C1") = 55
   .Range("C2") = 92
   .Range("C3") = 88
   .Range("C4") = 77
   .Range("C5") = 66
   .Range("C6") = 93
   .Range("C7") = 76
   .Range("C8") = 80
   .Range("C9") = 79
   .Range("C10") = 83
   .Range("C11") = 66
   .Range("C12") = 74
End With

Range("C1:C12").Select

' Create an icon set conditional format for the created sample data range.
Set cfIconSet = Selection.FormatConditions.AddIconSetCondition

' Change the icon set to a 5-arrow icon set.
cfIconSet.IconSet = ActiveWorkbook.IconSets(xl5Arrows)

' The IconCriterion collection contains all the icon criteria. By indexing into
' the collection, you can modify each criteria. The following sections set
' the criteria for each of the arrows in the set.
With cfIconSet.IconCriteria(1)
   .Type = xlConditionValueNumber
   .Value = 0
   ' XlFormatConditionOperator enumeration that specifies "greater than 
   ' or equal to."
   .Operator = 7
End With
With cfIconSet.IconCriteria(2)
   .Type = xlConditionValueNumber
   .Value = 60
   .Operator = 7
End With
With cfIconSet.IconCriteria(3)
   .Type = xlConditionValueNumber
   .Value = 70
   .Operator = 7
End With
With cfIconSet.IconCriteria(4)
   .Type = xlConditionValueNumber
   .Value = 80
   .Operator = 7
End With
With cfIconSet.IconCriteria(5)
   .Type = xlConditionValueNumber
   .Value = 90
   .Operator = 7
End With

End Sub
The first thing I want to do is change this to traffic lights - xl3TrafficLights2

I have managed to make this change but what i dont understand is how to assign a number to a particular icon - so, if i had 25 it would be red, if it was 50 it would be yellow and if 100 it would be green, also can this be done for a range so between 50 and 60?

Could someone put an example together which I can look at? I think the above code is set for the arrows and when changing code it gives errors related to objects and number ranges.

Any help would be appreciated.

Thanks.

I am using Excel 2007 on my work PC, and I would like to ask if there is a way to set the preferences for files in general. I have a file and I want the borders to be visible around all the cells. Yet when I copy some information from another file, the cells in the file I'm using are not visible, and I have to set their visibility with each new pasting of the info from the original file. It's really frustrating, especially since the border icon on the original file shows that all of the borders are supposed to be visible, but they arent.

Is there any way to set a feature for a file so I don't have to do this every time I copy and paste information?

So corporate has changed us all over to 2007. I'm decent enought w/ 2003 but getting headaches trying to learn the conversions.

Currently I can't figure out icon sets using percentages. All I'm trying to do is a stop light functionality based upon percentages.

Using conditional formating on a single cell (A1) I set the following

>90 is green, >80 < 90 is yellow,

I want smaller icons in the ribbon bar!!!

I've been working with Excel 2007 now for a week, finding my way around, but I really would love to be able to configure it so that the Icons displayed on the ribbon bars are smaller... I don't need the big old images and they just take up space. (this would apply to Excel and the other Office 2007 apps)

Any ideas???

Hi.
Wondering can anyone help me. I am doing graphs in Excel 2007 where they used 2003 before. I need to keep the colour scheme for the colum graphs that they had before.It will take me a long time to go in each time and change the colours. How can I set up a template so every time I use the graph the set colours I want come up. Please help as trying to do reports and have only started the job here. THanks a million

I am trying to figure out how I can create an macro icon in Excel 2007. I used to be able to create one in Excel 2003 and drag it to a toolbar. I have searched the help in Excel 2007 and cannot find anything that describes how it can be done. Is this ability no longer available in Excel 2007? If it is, any direction provided would be appreciated.

I recently installed Excel 2007 and I am having trouble finding certain features. Can any one direct me on how to set a print area? Thanks!

I want to change the the defualt directory when I open or save a file in Excell 2007. Right now it's using "My Documents" as the default directory.

In the previous version (2000) I could specify a default directory by..

Clinking on "Tools"
selecting "Options"
Going to the "General" tab
and populating a path in the "default file location"
I could also set my default fonts and number of sheets on the same page

In the new version (Excel 2007) I can locate the "default fonts" and "number of sheets" in the Excell Option section (which you get to by clicking on the MS Office Icon on the top left hand corner of Excel 2007, clicking on Excel option at the bottom right corner) but the "define file location" field apparently has been moved. I'm sure it must still be available, somewhere, but where?

Any help here would be appreciated.

I have a problem with workbooks from Excel 2003 that I am now using in 2007 where the print area is set to a column range and within the range there are shapes and textboxes etc. If I do a print preview (or print) then when the worksheet is displayed again the shapes and textboxes are hidden. However, if I highlight part of the worksheet that contains the shapes with the mouse I can see that the shapes are still there. If the print range is set to a range or cells rather than a column range then there is no problem. ( I need to use a column range as there is external data in the worksheet and the number of rows can vary. There are also parts of the worksheet that I do not want printed).

To illustrate this behavior : in a new Excel 2007 worksheet insert a shape and then select some columns including those that the shape appears in and then use the Set Print Area option. The shape disappears ( well it does on my pc). If you use the Clear Print Area option it becomes visible again.

Anyone any ideas what's going on or how to keep the shapes visible?

Can some one please help with this urgent issue.

We are using excel 2007.
Workbook is saved as a xlmt (excel macro enabled template)

I need code that ensures that when a certain sheet in the workbook is printed it will always print from the same designated tray from our printer (tray 1 in this case) which has yellow paper.

All other sheets in the workbook are either A4 or A3 plain paper.

When using an excel 2007 template, excel does not remember the settings when the template was last used and the same occurs when another user uses the template from their computer.

Please help
I thank you in advance.
Springbrook

Using the user interface, I can set the transparency of a line on a line chart to say 0.5. Recording a macro no longer works for this type of thing. I want to set it from VBA but cannot figure out how to do it.

Could someone please tell me what object is getting its transparency set to 0.5? I've tried ActiveChart.SeriesCollection(i).transparency=0.5 and ActiveChart.SeriesCollection(i).border.transparency=0.5 but neither one is accepted. I've searched the archives here and googled to no avail.

Excel 2007 on Windows XP SP2

Thanks,

In Excel 2007, there does not appear to be a method to edit the icons that
can be assigned to macros. Has anybody found a path in Excel 2007 into the
edit button feature of Excel 2003? Alternately, is there an add-in for
editing icons/buttons?
Thank you,
David

My Excel 2007 file opens with all the sheets' formatting set to Date. I will
reset the sheet format to General, set the cell formats to either date or
currency as desired, and then save the file. When I reopen it, all the cells
that I did not specifically set will be reverted to the Date format again.
I would like the file to open with the General format as default, but it
appears to open with the Date format as default. I have checked the options
but cannot find something that will do this.
Note that I converted this file from an Excel 2002 format.
This does not happen with other Excel files.


No luck finding an answer? You could always try Google.