Posts by Kieran

• Sharing a word doc

slick255,

Word was not intended as a shared medium like that.
Specifically word will prevent you from updating a document that some one else has edited.

There are some Microsoft Sharepoint services that may get around this, but I have no experience with them.

• copy and past macro

Erik123456789,

One quick way is to place the totals above the hedings of the column, and set the formula to sum all the rows below. eg sum(b3:b1000)
It will then automatically include any addtional data.

• copy only from a cell with data in it

JoeH

I am not sure if I understand you correctly, but try this as a start.
You could copy all of the data and then paste it to sheet 2 but use the paste special method and select the transpose option.

• SUMProduct with times?

EBeth,

It may be possible that we are counting blanks too.
Amend Tom's formula to =SUMPRODUCT((A4:A100<D4)*(C4:C100<D9)*(A4:A100>0)*(C4:C100>0)) and let us know the result.

BTW - Tom's formula will be faster to execute, so it is probably better ot leave the arry formula solution alone.

To set the graph ranges for the x axis, double click on the axis. A dialog box will appear that will allow you to set the scale. Set the minimum value to 0.3333333.

• Converting time to decimal flaw?

RichM,

The error is due to the manner excel stores time and decimal conversion as you seem to have guessed. Excel is not 'wrong' just too pedantic at times.

Try =ROUNDDOWN(J17+0.0001,1) as it adds a small margin to allow for the numerical error.

As an aside, you could have added up all of the hours without converting them to decimal. This would avoid any conversion errors in the calculation. you could then convert the totals to a decimal hour figure and reduce any net error.

• SUMProduct with times?

EBeth,

The array formula =SUM((A2:A4<E2)*(C2:C4<E3)) will work. It assumes that cell E2 contains 11:00 and E3 contains 0:31 (the registration time & max wait criteria) move them somewhere appropriate in your workbook. Remember that array formaulas must be entered using ctrl-shift-enter, not the normal enter.

Another way to analyse the data, would be to graph the wait time against the registration time. Use the the xy scatter diagram whre the x values are the registration time and the y values are the wait time. You will very quickly see any groupings in the data.

• Multiple lookup

The attached workbook shows one solution using array formula.

• Excel Automated Actions!

JamesChris,

It is possible, but I think that you are talking system/network actions, not excel itself.
It is possible to schedule tasks such as copy and print, however they are normally associated with system level events (as excel would need to be running to wait for the specified time).
Some thing like http://www.tucows.com/preview/195754.html or similar seems to be appropriate.

• Nested If function to split cell data

ER-1,

I don;t kmow aabout the nested if functions but if you data was in cell A1 then =LEFT(A1,FIND("/",A1,1)-1) will give you the first half, and =RIGHT(A1,LEN(A1)-FIND("/",A1,1)) will give you the second half.

Does that help?

• Displaying a message when a certain worksheet is selected.

Gops

Try this code int he affected worksheets.

Sub Worksheet_Activate()
If Not (ActiveSheet.Index Mod 2 = 0) Then
MsgBox "This is an odd sheet"
End If
End Sub

• I have an error in my code

Keli,

I normally use this to select a region to the last non blank cell.

Sub SelectCellsC4Down()
Range([D4], [D4].End(xlDown)).Select
End Sub

It has the advantage of speed, as the inbuilt routine avoids you having to loop througheach cell.

• VLookup & Paste

Mitchy,

The formula below will do what you want.

=VLOOKUP(B2, INDIRECT(C1& "!a2:c14",TRUE),3, FALSE)

Basically it will look in the sheet named in c1, in the area a2:c14 for the matching value in b2.

If you put the differnt sheet names in c2 to c5 and copy the formula across it will work.
(remember to set the corect lookup area instead of !a2:a14, and also to select the correct return column form this area, I have is curently set to 3.

• Inputbox

PJA,
try the following

• ignore blank cells /& match w/multiple results

kimberly,

I'll answer the first question here, I will have to think a bit about the multiple name retreival.

Assuming the rates of pay are in range d2:d45 then the following formula will reurn the lowest non-zero rate.

=SMALL(d2:d45, COUNTIF(d2:d45,0)+ 1)

• macro and if statement

opeyemi1,

Could you please let us know where the 'debug error' occurs in the code and the details of the error message. It it difficult wiothout this information to offer any suggestions.

• Xl 97 - Sheet Change Event on DataValidation

Greg,

Could you post the change event routine so that it can be reviewed.
Also are you exiting the cell after you select the new value from the validated list? The change event is not triggered until you complete the editing of a cell - ie you move to a new cell etc.

• make Excel clever to know what i need

Paste the following formula into b2 and copy down.

=INDIRECT("A"&(ROW()-1)*8)

• VBA : Userform Questions

This isn't really answering your post, but you may find that FlexFind at http://bmsltd.ie/MVP/Default.htm is an addin that has all the features you describe.

• Formulas : select greatest of three numbers

grdrops1,

to find th greatest of a series of numbers use the =MAX() function.

eg the max value of cells a1, a2, and a3 is =MAX(A1, A2, A3) or simpler =MAX(A1:A3)

Yes it is possible to make a cell or cells to blink, however the solution will cause excel to behave in a jerky manner as the flashing is done by code.