Lookup table with exceptions?

[gavster]

I'm currently using a lookup table to determine programs to run on a machine, however I have 3 exceptions to this and can not get my code to work.

Currently the code looks at column D for the type, then column F for the thickness of material. It then calculates the etch time required using the rate (all shown as 1 currently) on the cal_sheet and then rounds this up to a whole number. This is then compared to a second table where the program details are listed against etch times.

It will make sense when you look at the attached workbook and read the code, honest!

Currently using the following code:

=IF(F5="","",LOOKUP(ROUNDUP((F5/(LOOKUP(D5,'Calc Sheet'!F:F,'Calc Sheet'!H:H))),0),'Calc Sheet'!$K$1:$K$44,'Calc Sheet'!$J$1:$J$44))

This works for everything except my exceptions.

My proposed new version is:

=IF(F6="","",(if(D6="A","A program",(if(and(D6="B",F6<16,"B Thin",(if(and(D6="B",F6>16,"B 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))))))))))

But I keep getting an error, highlighting the second LOOKUP.

Can anyone help please, it probably a simple problem, I just cant see it.

Thanks,
Gavin.

[martindwilson]

",(if(and(D6="B",F6>16,"B Thick",LOOKUP(ROUNDUP
remove that bracket
",if(and(D6="B",F6>16,"B Thick",LOOKUP(ROUNDUP
i cant see where this formula is supposed to go (well i just chucked it in an empty cell on pad expose)

edit in fact remove all brackets before the if's
and close the and arguments with brackets

then you get

=IF(F6="","",IF(D6="A","A program",IF(AND(D6="B",F6<16),"B Thin",IF(AND(D6="B",F6>16),"B 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)))))

[gavster]

Mybe I've not understood your reply properly Martin, but it looks like you are suggesting the following:

=IF(F6="","",if(D6="A","A program",(if(and(D6="B",F6<16,"B Thin",if(and(D6="B",F6>16,"B 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))))))))

I also tried:

=IF(F6="","",if(D6="A","A program",if(and(D6="B",F6<16,"B Thin",if(and(D6="B",F6>16,"B 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))))))))

These still generate the same error, with the following highlighted as the point of error:

,LOOKUP(ROUNDUP(F6/((LOOKUP(D6,'Calc Sheet'!F:F,'Calc Sheet'!H:H))),0),'Calc Sheet'

Any other ideas?

Gavin.

[martindwilson]

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]

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]

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

[DonkeyOte]

Gavin,

It's hard to find the code -- for the sake of others it's the final line in the Sheet object Change event, correct ?

Before getting on to the question...

-- you will find naming your Modules makes your life easier in the long run (set via Properties window (F4))
-- remove Modules you don't need (there are lots of empty modules)

To the question...

In formula terms:

=IF(F6="","",(if(D6="A","A program",(if(and(D6="B",F6<16,"B Thin",(if(and(D6="B",F6>16,"B 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))))))))))

Should be (I think)

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

You seem to know what you're doing in terms of R1C1 so I have no doubt you can adapt the above to fit in with the relative positioning (based on column F).

[gavster]

Donkey,

Thanks for the final reply.

With your not inconsiderable help I've solved it. The code you supplied needed a slight adjustment to match the columns on the lookup sheet, but other than that its was great.

Thanks also to Martin who solved the problem, but in a different manner.

Gavin.