Recent content by iGrant

I get a non-deliverable report (with error codes 5.5.0 and 5.3.4 when I try and send a mail over about 10MB. I've set limits at 100,000KB in each of 'Global settings', 'SMTP Connector settings', 'SMTP virtual server settings', and 'User mailbox settings'. Exchange server message tracking shows...

Try pressing f8 to turn off 'Extended Selection' mode

Try this formula in the Process worksheet: =indirect("'Dept MIS'!G2") When you copy and paste this into another workbook, it will refer to cell G2 of that workbook's sheet 'Dept MIS'

You mention that the city name is a variable length, can we assume that the state and zip are fixed lengths (2 letters for state, not sure how many letters you have in a zip code - being from the UK an' all...)? If there are 2 letters for state, a space, then say 6 digits for zip code, then try...

Have you checked if you have rows or columns fixed to repeat on each page? If the fixed rows (or columns) take up more space than a whole page, you can end up with the result that you are experiencing (ie Excel wanting to print on lots of pages).

You could also use SUMIF. At the end of your table of data, use the formula =SUMIF($A$1:$A$20,2001,$C$1:$C$20) Where your 'year' data is in column A, and frequency is in column C. This will then total all the frequency items that appear in a row with 2001. Copy the formula for each other year...

Do any of the paragraph formats in the cells have 'keep with next' checked? This might force one row onto another page, if the following paragraphs are too big to fit on the current page.

Thanks for the star - appreciated. Just had a thought, though - if you have two percentages in your list that are both the same, and the largest, then each will have 0.1% (or whatever difference) added to them and hence throw out the maths. Is this eventuality likely?

If the entries in your cells, such as 20020911, are just numbers, you'll need to chop them up into a date. Try this formula to convert the number 20020911 in A1 to a date: =DATE(VALUE(LEFT(A1,4)),VALUE(MID(A1,5,2)),VALUE(RIGHT(A1,2))) Then you should be able to do =int(now()-A2) to give...

Keep the formula that you have in column B. Then in column C, against each row, copy the following formula: =IF(B1=MAX($B$1:$B$3),B1+100%-$B$4,B1) It will add the difference between your summed total (of 99.99 or whatever) and 100% to the largest percentage in the list, and thus not throw the...

This is a bit long winded, but =IF(YEAR(C17)>YEAR(B17),SUM((YEAR(C17)-YEAR(B17))*12,MONTH(C17),12-MONTH(B17)),MONTH(C17)-MONTH(B17)) seems to work OK. BUT it won't work if the date in column B is earlier than that in column A - if this is likely to happen then some more work will be required...

You can use Ctrl + End to have Excel go to the cell that it thinks is the bottom right hand corner of the spreadsheet. If you have selected a single row and 'merged' it by mistake, this will prevent the insertion of columns (because it will have affected the cell in column IV, as Geoff suggests...

To pick a cell from a range, you can use the Index function =INDEX(NamedRange,2,1) will refer to the cell in the 2nd row of the 1st column of the named range. Any use? To address the issue of your linked sheets, I'd be tempted to have the sheets structured with a column of data with the...

In Outlook 2000 this works: Select View menu - Current View - Customise Current View... then click the 'Other Settings...' button Then in the 'month' area of the dialogue box, uncheck 'Compress weekend days'

This can also be caused by setting a high number of rows to repeat at the top of every sheet.