Lookup table with exceptions?

**martindwilson** · 04-07-2009, 06:32 AM

see what i posted again there is a fixed version there
you must enclose the "and"
arguments in brackets
if(and(D6="B",F6<16,"B Thin",if(
should be
if(and(D6="B",F6<16),"B Thin",if(
and so on

**gavster** · 04-07-2009, 06:45 AM

Martin and Donkey,

Firstly let me apologis for the confusion I have caused.

Martin you appear top have solved the problem one way using the code I was trying to enter into a sinlge cell on the spreadsheet, whereas Donkey has solved the problem after looking at the code attached to the page I am using. I apologise, I didn't realise the code was actually entering a result into that cell as well.

This may explain my problems.

Entering the formula into the cells is the most straightforward for me, as I dont fully understand all the code, as the generous usersd of this site wrote mosty of it for me. But If I enter the formula into the cells it gets overwritten by the code. If I remove the last section of code, the column jump stops working. I am confused.

Donkey, can you show me how it should look in the code format so that the cells can be left empty?

Thanks,
Gavin.

**DonkeyOte** · 04-07-2009, 07:12 AM

Gavin, I must confess I'm struggling to follow what it is you want to do ...

However, before doing anything... remove all lines in the Change event where you have either of:

Application.EnableEvents = False

Application.EnableEvents = True

Re-insert the False command line once at the very beginning of the Routine (ie pre IF test)
Re-insert the True command line once at the very end of the Routine prior to the End Sub statement.

If you want:

=IF(F6="",IF(D6="A","A program",IF(D6="B","B "&IF(F6<16,"Thin","Thick"),LOOKUP(ROUNDUP(F6/LOOKUP(D6,'Calc Sheet'!F:F,'Calc Sheet'!H:H),0),'Calc Sheet'!$K$1:$K$44,'Calc Sheet'!$J$1:$J$44))))

in R1C1 notation then based on the Change event:

If Target.Column = 6 Then
    With Target.Offset(0, 1)
        .FormulaR1C1 = _
            "=IF(RC6="""","""",IF(RC4=""A"",""A Program"",IF(RC4=""B"",""B ""&IF(RC6<16,""Thin"",""Thick""), " & _
                "LOOKUP(ROUNDUP(RC6/LOOKUP(RC4,'Calc Sheet'!C6,'Calc Sheet'!C7),0),'Calc Sheet'!R1C11:R44C11,'Calc Sheet'!R1C10:R44C10))))"    
        .Value = .Value  'comment out this line such if you wish for formula to persist in G
    End With
End If

If you opt to paste just the result to Column G (.value = .value) then you will need to ensure the above fires whenever any value that may impact the result alters... so if D changes the above must calculate also not just when F changes as is presently the case.

Disclaimer: I've not sanity checked or reviewed any other code...