Recent content by ScorchedLemonade

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

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!

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!

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

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

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

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

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

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

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

Thanks - very nice solution.

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.

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

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

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