How to filter a cell, dependant on 3 other cells.

[Kenny_K]

Hi, I am new here, I am reasonably proficient with excel, also done some C# programming a while back. But I am stumped on this. This is my most ambitious excel project to date. My workbook should be attached. Here is a description of what I have done so far and what the end goal is. I have created a workbook which has 9 tabs, the 6 green ones relate to each different products data set. There is a "Lists" sheet, for my named ranges. The blue tab is home to my main table, this table contains all the product data in it's entirety. At first I thought there is probably a way to access this using formula's than using the lists, but I was not able to do it. The red tab has the start of the quote form for the products. This is the where the lookup formula's would be instigated from. You will note that I have set this table up the same as "Dr List". The results are all to come from drop down lists, which are mostly dependent on others.

So, I can filter the "Dr List" table from left to right to arrive at the data required on the quote form, just don't know how to get there using formulae.
What I have done on the quote form is derived the answers from the "Lists", but then got stuck when it came to the "H" cell. Because this cell is dependent on what is contained in all 3 of "Model", "Type", and "D". In that order, I just don't know how to do it?

Once I have that sorted, I should be able to work out the "KG" cell formula.

Price has to come from the individual product tables to the "Dr List" table, then onto the quote form. At least that is how I see it ATM.

Any insight would be much appreciated. I am really struggling.

Cheers,
Kenny_K

[FDibbins]

Your file did not come through

[Kenny_K]

Should be there now.

[menem]

I'm not sure did my solution is the right way.

1. I need to sort all field of your 'Dr List'
2. Create formula to get avilable list
** I didn't use unique function due to my excel version. **
- IDX criteria : condition for create index number
- IDX prefix : prefix leading of each group
- IDX no : prefix & running number
- List : element to show
- IDX Merge : Group all IDX no to connected cells.
- List Merge : List for dropdown
3. in th CSS Quote , I've change data validation
B2
=OFFSET('Dr List'!$O$6,MATCH(B$1&"*",'Dr List'!$N$6:$N$414,0)-1,0,COUNTIF('Dr List'!$N$6:$N$414,B$1&"*"))

C2
=OFFSET('Dr List'!$V$6,MATCH($B2&"*",'Dr List'!$U$6:$U$414,0)-1,0,COUNTIF('Dr List'!$U$6:$U$414,$B2&"*"))

D2
=OFFSET('Dr List'!$AC$6,MATCH($B2&"_"&$C2&"*",'Dr List'!$AB$6:$AB$414,0)-1,0,COUNTIF('Dr List'!$AB$6:$AB$414,$B2&"_"&$C2&"*"))

E2
=OFFSET('Dr List'!$AJ$6,MATCH($B2&"_"&$C2&"_"&$D2&"*",'Dr List'!$AI$6:$AI$414,0)-1,0,COUNTIF('Dr List'!$AI$6:$AI$414,$B2&"_"&$C2&"_"&$D2&"*"))

G2
=OFFSET('Dr List'!$AQ$6,MATCH($B2&"_"&$C2&"_"&$D2&"_"&$E2&"*",'Dr List'!$AP$6:$AP$414,0)-1,0,COUNTIF('Dr List'!$AP$6:$AP$414,$B2&"_"&$C2&"_"&$D2&"_"&$E2&"*"))

H2
=OFFSET('Dr List'!$AX$6,MATCH($B2&"_"&$C2&"_"&$D2&"_"&$E2&"_"&$G2&"*",'Dr List'!$AW$6:$AW$414,0)-1,0,COUNTIF('Dr List'!$AW$6:$AW$414,$B2&"_"&$C2&"_"&$D2&"_"&$E2&"_"&$G2&"*"))

Note : not change formula in F2 due to no information about width in Dr List.

Regards.

[Kenny_K]

WOW!! Menem, that's excellent. No wonder I couldn't do it, all that IDX stuff I have not seen before.
Thank you so much.
Next thing is to get the prices to populate. It would be awesome if you would help me with that now.
The prices are found in the various tables in the green tabs. Typically the orange, green, black, and grey formatted tables.
I don't know how but the "Dr List" table should perhaps have a formula in the price cells to lookup these prices?
Then from there they need to find their way onto the Quote form. This may be the most complex part?
What are your thoughts?
Cheers,
Ken.

[menem]

Please provide me how to get the price.
Just only know that green sheets are refered to model.
BTW, these are maybe the problems.

-height , it's seem not in the same way with Dr List
-table structure seem not in symmetric
-green sheet not refer direct to model (for sample MET should be Metabox)

Regards.

[Kenny_K]

Hi Menem, I have attached something to help you understand, hope this does the trick.
If you need anything else just ask.
Cheers,
Ken.

[menem]

I need you to do somthing.

-rename green sheet in Model name
-every table must start at column L (or anyothers but must the same to all sheets)
-provide every table into symmetric as 20 rows x 12 columns (it just for sample , I think it might be enough)
-Height-Weight must be at the top left of table
-depth must be number

I've adjust ANT to Antaro
and modify table of M-30 and M-30-IN
for your sample.

Then you can use this formula in I2
=IFERROR(INDEX(
OFFSET(INDIRECT("'"&$C2&"'!"&ADDRESS(MATCH($G2&"-"&$H2&IF($D2="Inner","-IN",""),INDIRECT("'"&$C2&"'!L:L"),0),CODE("L")-CODE("A")+1)),0,0,20,12),
MATCH($F2/1000,OFFSET(INDIRECT("'"&$C2&"'!"&ADDRESS(MATCH($G2&"-"&$H2&IF($D2="Inner","-IN",""),INDIRECT("'"&$C2&"'!L:L"),0),CODE("L")-CODE("A")+1)),0,0,20,1),0),
MATCH($E2/1000,OFFSET(INDIRECT("'"&$C2&"'!"&ADDRESS(MATCH($G2&"-"&$H2&IF($D2="Inner","-IN",""),INDIRECT("'"&$C2&"'!L:L"),0),CODE("L")-CODE("A")+1)),0,0,1,12),0)),0)



Regards.

Note , you may change L:L to L$1:L$-the last row number of sheet as you need , for increase speed/performance of the worksheet.

[Kenny_K]

Thanks very much Menem, I will get to work on that.
You have been so very helpful I really appreciate it!

[nick.williams]

You can use dynamic arrays with FILTER and UNIQUE nicely here. e.g.

=UNIQUE(FILTER(DrList[Depth],(DrList[Brands]='CSS Quote'!B2)*(DrList[Models]='CSS Quote'!C2)*(DrList[Type]='CSS Quote'!D2)))

The list in Data validation then refers to the dynamic cell, e.g. ='Dr List'!N2#

See attached for example, I'm sure you can figure out the KG and Price. I've put the lookups on the DR List sheet, but they can be moved if you prefer.

[Kenny_K]

Awesome Nick, I will check that out.

[Kenny_K]

Nick that is what I was originally trying to do, but where I kept falling over was I couldn't get the validation box to accept the code. I must not have been typing it in correctly I'm thinking now. At the time I thought that I must not be able to put that sort of code in there. So thanks again Bud.

[Kenny_K]

Ok, I have made a lot of progress. Done some reformatting and thanks to Nick got all the looking up working. The thing that has me stumped now is getting the price into the quote sheet. I have a formula in there which is throwing an error, although I have another spreadsheet which I used the same basic formula in which works a treat. I have also added a column to "Dr List" table with the name of the price table for each drawer, I thought this would simplify the price lookup.
Now if someone could have a look at the index match formula I have in there that isn't working, and correct it for me, or tell me what I have done wrong and what the solution is I would be much appreciative.
Also most of the price grids are "Named Ranges" I converted 2 of them to tables thinking this is necessary for my formula to work. So keep that in mind. The 2 that are tables are in the "Antaro" tab.
Thankyou.

[JohnTopley]

Try

=INDEX(M_30,MATCH(F2,M_30[Width],0),MATCH(TEXT(E2,"000"),M_30[#Headers],0))

Headers in TABLES are always TEXT so you need to "convert" E2 to text for the match

[menem]

Kenny,

I think your formula in the last file (DrawerCostingN.xlsx) in the 'Dr List' at range K1:R8 just only relate to
'CSS Quote'!B2. So, you might got some problems if Brand in the other rows are different from B2.

But it's will be ok, if all of them are the same.

Regards.

[nick.williams]

Or use E2&"" to force it to text if they won't always be 3 characters long.

[Kenny_K]

John I tried your formula, it worked until I substituted the table names in the formula to the cell references that hold them. This is what I was originally trying: =INDEX(I2,XMATCH(F2,I2[Width]),XMATCH(E2,I2[#Headers]))
In your formula I swapped M_30 to I2, it didn't work. Probably a simple answer, but it has me stumped.

[Kenny_K]

Thanks Menem, those have some of the new formulae that are only available in 365. Won't work in your 2016 version.
I really appreciate what you showed me earlier. Lot less work if you use 365 though.
Bless you mate!

[Kenny_K]

Or use E2&"" to force it to text if they won't always be 3 characters long.

=INDEX(M_30,MATCH(F2,M_30[Width],0),MATCH(TEXT(E2&""),M_30[#Headers],0))
Tried this Nick, but it only caused an error. To few arguments. With it as it was, it was returning 6 characters, like 57.455 for instance.

[JohnTopley]

Try

=INDEX(INDIRECT(I2),MATCH(F2,INDIRECT(I2 &"[Width]"),0),MATCH(E2&"",INDIRECT(I2 &"[#Headers]"),0))

Use INDIRECT to reference the value in I2: I have included Nick's formula (bold)

[Kenny_K]

So, just to reiterate. This formula works: =INDEX(M_30,MATCH(F2,M_30[Width],0),MATCH(TEXT(E2,"000"),M_30[#Headers],0))
What I need to change, is instead of the table name M_30 being in the formula. I need the formula to pull whatever table name is referenced in the cell in column I. If I substitute M_30 for I2 in the formula it doesn't work.

[Kenny_K]

Oh Yeah!!!!!!!!
It works!
Thanks so much guys! I really appreciate your help.
I can go to bed now with my mind at ease.

[JohnTopley]

Before you nip into bed, can you please mark as solved! ("Thread tools" at top of page)

[menem]

Oh thanks, seem I need to try

Regards.