Search results for query: *

Hi columbiaDiver: In the simple example I posted in the preceding, I can also use the following formula (regular non-array) ... =MAX(EXACT(A$2:A$5,D3)*B$2:B$5) Yogi Anand, D.Eng, P.E. Energy Efficient Building Network LLC http://www.energyefficientbuild.com

Hi Gavin: Here we go ... 1) I took a screenshot of the part of the spreadsheet that I wanted to display with a free utility such as MWsnap3 2) I saved this as a .gif file 3) I uploaded it on my website www.energyefficientbuild.com -- but it can be instead uploaded on a site such as box.net...

Hi columbiaDiver: Here is a little play I had with case sensitive exact match. With my LookupTable in cells A2:B5, my LookupValue in cell D3, then the array formula for my resulting value from column 2 of the LookupTable is ... =MAX(IF(EXACT(A$2:A$5,D3),B$2:B$5)) see the following image for...

Hi crmorgan: I suggest you clarify the following ... 1) what do you mean by updating 'File: 04-04-09abc will update with file 04-04-09'? 2) are the various files that you reference in your post ...worksheets of the same workbook, or are these in different workbooks? Yogi Anand, D.Eng, P.E...

Hi minjulep: My take on this is that if we take an array of TRUE and FALSE values these will have to be coerced into 1s and 0s whether you use the SUM function or SUMPRODUCT function. By the way, I can coerce TRUE and FALSE into 1 and 0 by using not only --() but also ()+0 ()*1 ()/1 ()^1...

Hi JohnOB: How about considering using the validation DropDown for the starting value of a row of entries ... and the remaining entries of the row could be formulas where the formula will result into a blank if there is no entry in the starting value of the row. I know it sounds pretty...

Hi TomYC: In addition to the suggestion by SkipVought of using the CODE function, you may also consider alternatively using the FIND function, or the EXACT function along with the use of the MATCH and the INDEX functions. Yogi Anand, D.Eng, P.E. Energy Efficient Building Network LLC...

Hi asoto6: You did not say how the formulation I suggested ... =IF(F6<=2.0049,25,your IF formula here) would not do. In your solution you posted that you said does work, is not different from my proposed solution except for some reason you have decided to change the reference to cell F7...

Hi asoto6: Assuming that your current IF formula is working correctly add to it the following part shown in red colored font ... =IF(F6<=2.0049,25,your IF formula here) Yogi Anand, D.Eng, P.E. Energy Efficient Building Network LLC http://www.energyefficientbuild.com

Or ... =COUNTIF(A1:A10,">12/31/2007")-COUNTIF(A1:A10,">12/31/2008") Yogi Anand, D.Eng, P.E. Energy Efficient Building Network LLC http://www.energyefficientbuild.com

Hi jerichardson: As it has already been stated ... you have actually two conditions to format. However, if you want to use three condions, follwing is one way: 1) key-in OK NV Z MU MD ML MR in cells E1:E7 2) then for cell A1, use the following formulas for ConditionalFormat Condition1...

Hi Skip: With Prayers and Best Wishes! Yogi Anand, D.Eng, P.E. Energy Efficient Building Network LLC http://www.energyefficientbuild.com

Hi VincentCrimmins: You may also want to try ... Set refRange = Range("B2:B" & LastRowDailyPerf) ActiveWorkbook.Names.Add Name:="yRefRange", RefersTo:=refRange Cells(20, 7).FormulaArray = "=Average(if(yrefRange>0,yrefRange))" Yogi Anand, D.Eng, P.E. Energy Efficient Building Network...

Hi mcauliff: Try ... =MATCH(12345,ProdITG!E:E,0) and preferably ... =MATCH(refCell,ProdITG!E:E,0) where refCell houses the entry to be matched in the column number and sheet number of interest. Yogi Anand, D.Eng, P.E. Energy Efficient Building Network LLC http://www.energyefficientbuild.com

Hi Scott: If your data as shown in your post is in cells A1:E4, and G2 houses John Deer then use the following formula ... =INDEX(A:E,SUMPRODUCT((A1:E4=G2)*ROW(A1:E4)),1) to get Bill as the result. Yogi Anand, D.Eng, P.E. Energy Efficient Building Network LLC http://www.energyefficientbuild.com

Hi newfrontiers: The suggestion by Skip to use MS Query is the way to go. However, for a formula based approach, with your original data in columns A:E starting with row 1 down, for the new data to be formulated in columns G:I, use the following formula in cell G1 ...

hi elsenorjose: If the original entries are in columns A and B, and the newly arranged entries are in columns D to F, then column D houses Unique entries from column A (using AdvancedFilter), and the formula in cell E2 is ... =INDEX($B$2:$B$10,MATCH($D2,$A$2:$A$10,0)) and F2 is ...

Thanks John! The case of upper case and lower case pm, AM, etc., can be fixed by using ... =SUBSTITUTE(MID(UPPER(A1),FIND("-",A1)+1,255),"PM"," PM")-SUBSTITUTE(LEFT(UPPER(A1),FIND("-",A1)-1),"PM"," PM") Yogi Anand, D.Eng, P.E. Energy Efficient Building Network LLC...

Sorry ... of course, you are right Skip! Yogi Anand, D.Eng, P.E. Energy Efficient Building Network LLC http://www.energyefficientbuild.com

Hi sagobogger: Assuming your data starts in cell A1, how about using the following VBA code ... Range("a2").Select Do While ActiveCell.Value <> "" If ActiveCell.Value <> ActiveCell(0, 1) Then ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell End If...