Recent content by yogia

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