Multiple IF's look up table

[Rhysgally]

So I'm not even sure that title describes my needs.
I'm putting together an automated pricing spread sheet and I need help with the last row...
so I've got two tables that need to be looked up by earlier inputs. if the process is 'X' I need it to look at one table, if its 'Y' I need it to look at another table.
next is if the material is X Y or Z, it needs to look at a corresponding Colum from the specified table. and lastly the thickness will look up the corresponding row


can anyone help me??

 

[L. Howard]

Hard to understand what needs to happen where and to what.

If you can, post a link to a exact sample of your workbook and restate you needs/wants, referring to specific cells, rows, column, table 1 or table 2 and the such.

You cannot attach a file here, but you can post a lint to a workbook using one of the link utilities. I use Drop Box, but there are others.

I will be glad to at least take a look at it.

Howard

 

[Rhysgally]

Thanks mate.

https://www.dropbox.com/s/04ndjdlhgxdjdov/Conglomerate Spreadsheet.xltx?dl=0

Here's a link hopefully to drop box.
You'll see in the data there are two tables labeled laser and standard. What I'm trying to achieve is an automatic input of the cut rate cell in the rectangle and disc and flange pages. So based on process type and materials aswell as thickness, it'll reference the correct cut rate.

Hope that expand it a bit better.

Cheers

 

[L. Howard]

Lets see if I am on the right track.

On Data sheet:

I named the following cells as such.

Lindex is a named range for cells M8:O17 *Laser Index
Lthick is a named range for cells L8:L17 *Laser Thickness
Lcas is a named range for cells M7:O7 *Laser Carbon, Aluminium, Stainless


On sheet Rectangle cell O6 I entered this formula, (I only did one cell to start), which does the following:

Looks at H6 for the thickness, and at C6 for the material, and returns the cutting charge for that material and thickness (using the Laser chart on Data sheet).

And then multiplies the charge times the Quanity in cell E6, if less than $45, it returns $45, otherwise returns the product of E6*the cutting charge if larger than $45.

=IF(INDEX(Lindex,MATCH(H6,LThick,0),MATCH(C6,Lcas,0))*E6<45,45,(INDEX(Lindex,MATCH(H6,LThick,0),MATCH(C6,Lcas,0))*E6))

Is this on the correct path?

And to be noted the formula is for the Laser cutting chart only. There is still the need to respond to either Laser or Standard as selected in column B of Rectangle sheet.

Howard

 

[Rhysgally]

Sounds to me like you're one the right track mate. But how would you combine the two lookups? For laser and standard?

 

[L. Howard]

Try this TESTER version,

https://www.dropbox.com/s/ty4t49njyis6s4g/Conglomerate TESTER.xlsm?dl=0

Where you will go to Sheet Rectangle and:

Select a Process in column B.
Select a Material in column C.
Enter a Quanity in column E.
Enter a Thickness in column H.

See result $$$ in column O. Also see the formula in column O.
You can change Process and/or Material and the formula will update to correct Data sheet Table.

The formulas are the result of a change_event macro in the sheet module. It responds to the entry in column B and applies the correct formula to column O.
There is nothing (no formula) for the selection of Guillotine in column B, and the macro will not respond to that selection.

If you clear the Columns B through H, you should clear the O column formulas also. But if you make new selections in B, the formulas will be re-written to the correct selection.


Also, notice the first four rows formulas in columns L, N, and S. I amended with IFERROR to eliminate the #VALUE errors in these columns. If you like, then pull the formulas on down the columns as far as needed.

Give it a good test run and see what we need to fix or adjust.

Howard

 

[Rhysgally]

? thanks so much. All done. What a forum! !

 

[L. Howard]

&#55357;&#56832; thanks so much. All done. What a forum! !

Okay, good to hear. I was sure there would be some tweaking or whatever, may be latter. If so, then post back.

Howard