Complex auto fill or order sheet based on column values.

Hi All!

You guys have been great in the past in helping me learn and succeed. BUT, I have a really good one for you. I am not even sure what I am asking is possible...

I have attached the worksheet. There are two tabs, the first an order sheet, the second with the values I need to auto-fill into the order sheet based on column headers.

Each order broken down by Style #, Color #, size, and quantities on the second tab. The quantities need to auto fill in the the sheet highlighted in green area.

The sizes are broken down into a key based on the values in column K.

Example style 1698181 sizes are in row 10. So if I wanted to order 33 pieces of 1698181 in color 378 in size xxs then cell L12 should show 33 as the value.

I am assuming there is some vlookup code you guys could write, and man if it is even possible I would die to see this one solved.

Sorry if I over explained it a bit, any help would be greatly appreciated. I have titled the worksheet, The Enigma

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[ deleted ]

In D2 of sheet 1 put this formula and drag down

=B2&C2&VALUE(A2)

In the L11 put this and drag across and down to fill the green space

=IFERROR(VLOOKUP($E11&L$10&$H11,Sheet1!$D$2:$E$374,2,FALSE),"")

This works for the sizing options in row 10 but do you want it to work for any combination of sizing options?

Try

L11

Formula:

Please Login or Register  to view this content.

That's better but how do I treat an XXL as it exists in both sizing charts?

[edit]
and while you're at it why is there 2 x '3/6' in the same sizing row?

How should the '2T' , '3T' etc in the order list be read in regards to size in your chart?

We're getting closer

this in D1 gets a better match

=B2&C2&VALUE(TRIM(SUBSTITUTE(SUBSTITUTE(A2,"t",""),"T","")))

and this in L11

=IFERROR(VLOOKUP($E11&L$10&$H11,Sheet1!$D$2:$E$460,2,FALSE),"")

Of the 2770 garments in the order sheet 2158 are now matched. I added a 'one size' as you have a number of these in your order list but the style numbers in the order sheet don't all appear in the 'the enigma' sheet so there's no direct matches for these

A very quick check showed you don't have any colour 15 in you Enigma sheet yet it is on your order sheet so that accounts for 4 items.

I found a style match for each style and I found a match for each size but some of the sizes in the order sheet are entered with a trilling space like "xs " instead of "xs". I tidied this up in the formula (see attached for latest amendments).

I'm now getting 2268 matches from the 2302 in the order sheet.

While the size and the style combinations are all accounted for (because one is a vertical and one is a horizontal component of your matrix) I'm reasonably comfortable that we have those combinations covered but there may be missing colours (like the 15) and more so you may not have every colour and style combination as these are both listed vertically it requires a degree of repetition to get these right.

woops here's the file (see above post)

its getting a bit tricky using multiple lookup rows. I've tried with a few nested iferror statements. Is this what you want?

=IFERROR(IFERROR(IFERROR(VLOOKUP($E11&L$10&$H11,Sheet1!$D$2:$E$374,2,FALSE),VLOOKUP($E11&L$6&$H11,Sheet1!$D$2:$E$374,2,FALSE)),VLOOKUP($E11&L$9&$H11,Sheet1!$D$2:$E$374,2,FALSE)),"")