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: *

  1. ScorchedLemonade

    Hiding partial rows (but not full columns)

    I suspect this is not possible but I figure I might as well ask. Is it possible in Excel to hide part of a row (without hiding the full intersecting columns). Obviously there'd be a problem with the cell/axis labelling but I thought that maybe it could be done in a specific situation: namely...
  2. ScorchedLemonade

    Conditional number formatting

    That's better yet. Rather than hide the columns, I've moved them to another part of the sheet (as I have other stuff in those columns) and then referenced them as you suggested. Thanks!
  3. ScorchedLemonade

    Conditional number formatting

    Thanks - it does work now. The counter has flown up - even after I set calculation to manual at the start of the sub and then to automatic at the end - and I'm afraid my sheet is pretty much crippled. Nevertheless I am thankful for your help - I know it can be done now. Thanks again!
  4. ScorchedLemonade

    Conditional number formatting

    Calculate is set to Automatic. I did find one big mistake though: although I put the Worksheet_Change in the right place (and this is the part that is executing when I type over) I put the Worksheet_Calculate in the wrong place embarrassingly. Having fixed that massive error I now get...
  5. ScorchedLemonade

    Conditional number formatting

    I tried this: with a break point at "Next i" (I also added the a=1/0 later as it didn't break.) If I type over the cells in J then the worksheet executes and I get the format change (but it doesn't add one to the counter cell and it doesn't break and neither does it complain about the a=1/0). If...
  6. ScorchedLemonade

    Conditional number formatting

    I tried that but it didn't do anything from the automatic update. If I manually wrote over the cell, then it did change - but that was also true with Worksheet_Change. Private Sub Worksheet_Calculate(ByVal Target As Range) For i = 5 To 14 If Target.Address = "$J$" & i Then...
  7. ScorchedLemonade

    Conditional number formatting

    I've got the following situation: a list of 3 columns x1 y1 z1 x2 y2 z2 x3 y3 z3 ... ... ... x10 y10 z10 The x1-x10, y1-y10, z1-z10 are pulled from a larger set of lists via formulae like: =MIN(INDIRECT("B4:B"&$B$1-4))...
  8. ScorchedLemonade

    Combining several arrays into one formula

    Not a problem anymore. It was figured out by a guy here at work. Thanks anyway to anyone who may have spent time on this. (Ultimately the solution involved writing a custom function to take care of part of the array problem.)
  9. ScorchedLemonade

    Combining several arrays into one formula

    Hi, I'm wanting to write a formula of the following type: =sum(if(Sheet1!C1:C200&Sheet1!D1:D200=A1&B1,Sheet1!B1:B200*index(Sheet2!B1:B100,MATCH(Sheet1!A1:A200,Sheet2!A1:A100,0)),0)) or alternatively...
  10. ScorchedLemonade

    Autoclose at specific times

    Hi, I've got what I presume to be a fairly straightforward problem but none of my solutions has worked so far: here are a couple of attempts by way of example. Private Sub Workbook_Open() With Application ' sometimes Windows crashes and the workbook gets set to calculate manually, so this...
  11. ScorchedLemonade

    Detecting if a cell contains a function or just a value

    Thanks - very nice solution.
  12. ScorchedLemonade

    Detecting if a cell contains a function or just a value

    Thanks all. I decided to go with IsntFormula = (CStr(rng.Value) = CStr(rng.Formula)) which worked, but then I changed to IsntFormula = Not(Rg.HasFormula) which also works.
  13. ScorchedLemonade

    Detecting if a cell contains a function or just a value

    Hi, I'm curious as to a way to do this in Excel: suppose A1 and A2 hold the same value but are calculated in different ways, namely A1 just has 5 entered A2 has something like =max(B2,C2)-D2 where B2=7, C2=1 and D2=2 I want to be able to detect that A1 is not calculated using references to...
  14. ScorchedLemonade

    Detecting if a cell contains a function or just a value

    Thanks - that does indeed work (*). I guess I went to the wrong forum though (as I'd wanted to do it via conditional formatting). (There didn't appear to be a one just for Excel - when I looked I got this, Microsoft Office and something curiously about squaring the circle - an impossible feat if...
  15. ScorchedLemonade

    Detecting if a cell contains a function or just a value

    Hi, I'm curious as to a way to do this in Excel: suppose A1 and A2 hold the same value but are calculated in different ways, namely A1 just has 5 entered A2 has something like =max(B2,C2)-D2 where B2=7, C2=1 and D2=2 I want to be able to detect that A1 is not calculated using references to...
  16. ScorchedLemonade

    I need to make calculations with numbers of at least 40 characteres

    Addition - it's not done by convolution but I put a C in front anyway as Sum is already used Function CSum(a As String, b As String) As String k = Len(a) l = Len(b) d = &quot;&quot; If k < l Then m = l Else m = k Do While Len(a) < m a = &quot;0&quot; & a Loop...
  17. ScorchedLemonade

    I need to make calculations with numbers of at least 40 characteres

    Sorry, the two lines carry = Left$(c, Len(c) - 5) should read If Len(c) > 5 Then carry = Left$(c, Len(c) - 5) Else carry = 0
  18. ScorchedLemonade

    I need to make calculations with numbers of at least 40 characteres

    I couldn't get things like d=c&d to work, but I was able to use Join. Here's a multiplication utility. It should be good for fairly large numbers - eventually it would break down but I think it would be good to multiplicand and multiplier each around half a million digits. Addition is much...
  19. ScorchedLemonade

    I need to make calculations with numbers of at least 40 characteres

    I was slightly remiss in not getting back so far on multiplication. There's a technique of multiplication based on convolution and a macro could be based on this, pulling out individual characters. Essentially, it works like this (longer strings can be handled similarly so I'll just use a...
  20. ScorchedLemonade

    Rank with array formulae

    I feel rather dumb at not having thought of the suggested solution, which works very well (though I had to amend a little part of it as some parts contain text (of the #N/A ... type) SUM(IF(ISERROR(B263/C263+B3:B263/C3:C263),0,IF(B263/C263>B3:B263/C3:C263,1,0)))+1 ), but in any event the first...

Part and Inventory Search

Back
Top