Hot answers tagged microsoft-excel-2003
12
The logical operations are represented by formulae:
AND( condition1 , condition2 , ... )
OR( condition1 , condition2 , ... )
NOT( condition )
Each condition can be pretty much anything with a logical evaluation, meaning you can nest logical operations by nesting the formulae as required.
So in your case you need:
=IF( AND( myval>=minval , myval ...
11
I think the closest you can get from native Excel functionality is Save As | Formatted Text (Space Delimited) (*.prn). It will automatically determine the widths and insert spaces to pad to that width as necessary.
Beyond that you need to have a macro or other add-in that will let you do more.
10
Just set the cell to be shown in numerical format and it will show a decimal number.
The integer part corresponds to the date value (i.e. the day) and the decimal part to the time of the day.
If you multiply the numerical value of 1:45 (which is 0,07) by 24x60 (the number of hours in a day and the number of minutes in an hour) you will obtain the value in ...
8
Some numbers are displayed in exponential format if the column is too narrow and you can fix this by increasing the width of the column.
However, very large numbers in Excel are stored in floating point format and cannot be represented in Excel exactly. You may be able to override their display using cell formatting but the true values will still be stored ...
7
Short answer:
There's no way to have a date without a year, it's simply a required part of a date. Just like the second is a required part of the time of day - if you enter, say, "1:15", the actual value will be "01:15:00" (or something like that, depending on your regional settings). The only thing you can do is format the column as text, use a date format ...
6
This happens because of the limited precision of floating point numbers representation on computers and cannot be fixed because it is an intrinsic problem of the way number are represented on a computer. There might be workarounds, such as round() or a clever implementation of the formulas to reduce error propagations.
Excel uses 8-byte (64 bit) floating ...
6
I have placed the data from "the first excel" on Sheet1, and "the 2nd excel" on Sheet2.
The key to this solution is the VLOOKUP() function. First we insert a column.
We then use the VLOOKUP() function to lookup the value of "1" in Sheet2. We specify 2 as the value of the third parameter, meaning we want the value of the 2nd column in the array. Also ...
6
Click cell you want to restrict value for.
Go to Data->Validation
On the dialog that comes up, for Allow, select List, and for Source, put in "Yes,No".
Now your cell will have a dropdown with Yes and No. User can also type these values without clicking the dropdown button.
5
This issue was discussed and answered on Microsoft Office for Developers Forums on April 14, 2011.
Question posted by Vershner:
I originally posted this in the IT
Professionals forum but they told me
to post it here because the issue was
by design.
When I click undo in the quick access
toolbar it undoes the last action in
Excel, not the ...
5
The way 'around' it is to open multiple copies of Excel, one per worksheet. This is really annoying, but there is no other way. I have special shortcuts for my main spreadsheets, just to open them explicitly. To do this:
Make a NEW shortcut - right-click on your desktop, New, Short cut.
Browse to the excel program ( "C:\Program Files (x86)\Microsoft ...
5
Using OpenOffice is probably the easiest option IMO because there's no file format conversion necessary and OpenOffice is freely available.
There is a BSD licensed Microsoft Office 2003 plugin that is available, OpenXML/ODF Translator Add-in for Office. I don't have Microsoft Office so I'm unable to try it.
5
Be aware that dates and times in Excel are stored internally as an amount of days. You can use this fact to simplify conversions of time amounts.
If you have an amount of hours in A1 you can calculate an amount of days using =A1/24 then display it as hours and minutes using the custom number format [h]:mm. Follow these steps:
In the desired cell, enter ...
5
You need to create a new default template that will load at startup. They didn't make it easy to do in Office 2007! But here's how:
Open a new blank worksheet.
Insert > Hyperlink > Paste in any link
Home > Styles > Right click on the 'Hyperlink style' > Modify > Set your font options
You probably will want to do the same for the 'Followed Hyperlink' style
...
5
iReport will not function correctly without a connection to JasperServer. If you're looking for easy reports your best tools are really excel and access. As far as end-user simplicity the buck stops there.
If you're looking for something a bit more robust then I would take a look at any of the following for open source solutions.
If you want to go ...
5
I would suggest if you have that much data you need to consider moving to a database rather than a spreadsheet. If your version of Office came with MS Access, this is a reasonable solution for the immediate future. If your data continues to grow you'll eventually need to migrate to a different DB because Access eventually tops out as well (around 2GB of data ...
5
The code is too clever for it's own good. It uses the Text property, which is what is displayed in a cell, to get a text representation of a cells value. The problem as mentioned in another answer is this is limited to 1024 characters. You can change the code to use the Value property but this might not work on all cell value types.
Change this line:
Print ...
4
Quite simply: You can't get there from here.
You could try to bodge something together by splitting the data across multiple worksheets, but only you will know how well that's going to work.
If you want to go past 65K rows, you either upgrade to Excel 2007 or newer, which supports over 1 million rows (probably 1,048,576 or 2^20) or you quit using Excel.
...
4
This should do it:
Sub PutMyNameInA1()
Dim w As Worksheet
For Each w In ThisWorkbook.Worksheets
w.Range("A1") = "My Name"
Next w
End Sub
The code should be put in a standard code module. (Alt+F11, Insert>Module, paste this in, change name in quotes, Run/F5 to execute)
4
If the files are static (on your desktop, My Docs, etc) then you can reference this link:
http://www.techonthenet.com/excel/questions/instance.php
If you have random files (from the Internet, E-Mail, etc) then you can do this to change the way it opens all XLS files to open in a new instance:
...
4
A good place to start would be the compatibility documents from Microsoft, including:
Convert a workbook to the Excel 2010 file format and Excel 2007 and Excel 97-2003 features that are not supported in Excel 2010, both part of the larger Use Office Excel 2010 with earlier versions of Excel document.
4
When you open the csv, you'll have the option to specify the delimiter and data type for each column. The text import wizard has 3 steps. Note that my screen shots are from Excel 2010, but this will work in exactly the same manner in Excel 2003.
Step 1: select delimited (instead of fixed width)
Step 2: select comma as your delimiter
Step 3: select each ...
4
This is a simple version of what I described in the comment.
Sub SwitchSheets()
Dim NextSheet As Worksheet
Set NextSheet = ActiveSheet.Next
If NextSheet Is Nothing Then
ThisWorkbook.Sheets(1).Activate
Else
NextSheet.Select
End If
End Sub
From the View tab click on Macros, select SwitchSheets, go to Options and set a ...
3
In Excel 2003 use SUMPRODUCT:
=SUMPRODUCT(C:C*(A:A="CP")*(B:B=1))
SUMPRODUCT works like an array formula without being one, iterating through the columns one cell at a time. If the conditionals are TRUE they are equivalent to 1, while if FALSE they are equivalent to 0, so it only ends up summing the values where both conditions are TRUE.
A simple way in ...
3
If you're willing to try a solution that doesn't involve pivot tables:
Remove the Pivot Date column. Arrange the date range in headers and use the AND() formula to determine if that date falls within the student's tenure. Your data will look something like this:
Here's how the formula in D2 looks like (note the absolute references). Just drag or copy ...
3
Here's a quick step-by-step break down of what I've done, which I think achieves what you're after.
First, prepare data and select the relevant columns:
Create the Pivot Table, I've done so in a seperate sheet, it doesn't really matter where.
Drag the Date field to the "Drop Row Fields Here" box on the left.
Drag the Value field to the "Drop Data ...
3
As far as I know, this isn't possible. You can format the cell as mm/dd but if it's a date, it has 3 parts: month, day, and year. Excel will add the year for you when you enter just the month and day. You will see it as mm/dd but since a date by definition has to have a year in it to perform date arithmetic, you don't have a choice really. The only way I can ...
3
An easier way to do it is to use the .LinkSources method.
For example, the below code will print a list of all links to Excel files.
Sub PrintLinks()
Dim v() As Variant, i As Integer
v = ThisWorkbook.LinkSources(XlLink.xlExcelLinks)
For i = 1 To UBound(v)
Debug.Print v(i)
Next i
End Sub
3
If you really want the functionality of changes updating quickly, then you will have to use a version of office that supports collaboration this way (like Excel Web Apps) and then use different spreadsheets or something similar to do the references.
If you want to skip the real-time (or anything like it time) update, you can use: ...
3
I'm using Excel 2010 so I'm not sure where the equivilent option is in Excel 2003.
Select the cells you want to restrict.
Format the cells with the appropriate number format "hh:mm".
In the "Data" ribbon (menu, maybe?), select "Data Validation".
Under "Settings", "Validation criteria", set:
Allow = Time
Ignore blank = Yes
Data = between
Start time = 0:00
...
3
The best way is to create the algorithm in Excel VBA. You can open up VBA and type this
Public Function Test1(x as Integer, y as Integer)
Test1 = x*y
End Function
You can call this function like any other function from the formula bar
In formula bar for A1
=Test1(2,4) with a result of 8.
Change the algorithm in VBA to get the results ...
Only top voted, non community-wiki answers of a minimum length are eligible
