Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  • Users: yogia
  • Order by date
  1. yogia

    VLOOKUP exact match (case sensitive) problem/Question

    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
  2. yogia

    VLOOKUP exact match (case sensitive) problem/Question

    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...
  3. yogia

    VLOOKUP exact match (case sensitive) problem/Question

    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...
  4. yogia

    VLOOKUP auto-update

    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...
  5. yogia

    - - is a double unary operator that coerces the calculation.

    ...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 Yogi Anand, D.Eng, P.E. Energy Efficient Building Network LLC http://www.energyefficientbuild.com
  6. yogia

    Cell Validation only if previous cell not blank

    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...
  7. yogia

    ascii code conversion table?

    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...
  8. yogia

    Nested IF Statements

    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...
  9. yogia

    Nested IF Statements

    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
  10. yogia

    performing a function on a variable cell

    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
  11. yogia

    HELP! Excel Conditional Formatting!!

    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...
  12. yogia

    Brief Absence

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

    Worksheet Function - Calculations

    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...
  14. yogia

    reference cell in Match

    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
  15. yogia

    Return Value from Named Range

    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
  16. yogia

    Transpose information in Excel

    ...in cell G1 ... =INDEX($A:$E,ROW()/2+VLOOKUP(MOD(ROW(),4),{0,0;1,0.5;2,1;3,-0.5},2),IF(COLUMNS($A:A)=1,1,INT((MOD(ROW(),4+(MOD(ROW(),2)=0)))/3)*2+COLUMNS($A:A))) and copy this to G1:I whatever Yogi Anand, D.Eng, P.E. Energy Efficient Building Network LLC http://www.energyefficientbuild.com
  17. yogia

    Create new column based on duplicate row

    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 ...
  18. yogia

    Subtracting TIme in Excel

    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...
  19. yogia

    Excel Page Break depending on row values

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

    Excel Page Break depending on row values

    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...

Part and Inventory Search

Back
Top