Search results

No, should be same thing for 2007...

I think you are looking for Dynamic Named Ranges

Also, =IF(ISNUMBER(MATCH(B1,$A$1:$A$100,0)),"Yes","")

It still works, yes, but I figure the OP is long gone and doesn't need this anymore... although, it might be useful for others...

How about just? =INT(A1) where A1 contains the date/time you posted. You can then format to any date format you wish. ooops... just noticed this is a 2006 thread! :eek:

I am not sure I understand your request. Can you elaborate on it a bit? My assumption from what I read so far is you want something like: =IF(A1=B1,"ABC",IF(A1=C1,"DEF",""))

In another column add formula: =A1&REPT(" ",10-LEN(A1)) copied down Then you can copy this column and paste special >> values over the original column, if desired.

Assuming your values are in A1, A2 and A3 as posted, then go to Insert|Name|Define and enter new name: Evaluation and in the refers to field enter =Evaluate(A1&A2&A3) Then in A4 enter: =Evaluation The formula can be copied across the columns in a relative manner...

So what's happening is that the formula looks at cell C5 and first takes everything the the left of " / " which is blank, slash, blank and it concatenates that with an underscore and then with what is found in cell M1. It looks for that new string in a table called TCA_lOOKUP. If you are...

You can't show a result and the formula at the same time. You can copy the formula into an adjacent cell and precede it with an apostrophe so that it becomes a text string. or you can toggle between formula and result by holding the CTRL key and hitting the tilde (~) key above the TAB key.

A couple of things... If you are referring to another workbook, you should have the extension in the workbook name (i.e. .xls or .xlsx), also, the MATCH() function usually takes on another optional argument to get the exact match... esp. if your data is not in sorted alphabetic order.... eg...

So, it's not the actual initial spreadsheet with the query that is the problem, it's another sheet that uses the queried info that is the problem? Is that right? Is the other sheet a separate workbook? Can you give details on what formulas are being used to get the info into the second sheet?

The query needs to be refreshed by force... right-click|Refresh query. Did you do that?

You are welcome. I probably should have also mentioned that you can reference cells containing the start and end dates, so that you don't have to manually change the formula all the times. So instead of: =SUMIF(H2:H572,">="&DATE(2009,1,1),I2:I572)-SUMIF(H2:H572,">"&DATE(2009,6,30),I2:I572)...

I am not sure that the SUMPRODUCT as you wrote will work. I think it should be more like: =SUMPRODUCT(--(H2:H572>"1/1/2009"+0),--(H2:H572<"30/6/2009"+0),I2:I572) and to ensure the formula works in both Europe and North America, I like to employ the DATE function instead of hard coded date...

Try: =IF(OR(COUNTIF(Sheet1!$E$2:$E$300,A1),COUNTIF(Sheet1!$G$2:$G$300,A1),COUNTIF(Sheet1!$I$2:$I$300,A1)),"Present","Not Present") or probably even, just: =IF(COUNTIF(Sheet1!$E$2:$I$300,A1),"Present","Not Present") If the columns between won't contain the horse's name anyways....

Here's a google search result: form controls vs. activex controls

If your start/end times are in B2:C2, respectively, then to calculate hours past midnight, try: =MAX(0,(C2<=TIME(7,0,0))*(C2-TIME(24,0,0))*24) this formula assumes that "after midnight hours" happens until no later than 7:00 AM you may have to play around with the TIME(7,0,0) part depending...

Assuming cells B5:H5 are of interest, try: =SUMIF(B5:H5,">"&SMALL(B5:H5,2))

Have a look here: http://www.rondebruin.nl/calendar.htm