Live inventory to block out sold out styles or sizes.??

Where would this form be looking up data from? What does the form look like? I suggest attaching a sample workbook.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

I will assume that the item codes are a pivot table.

We have to look up against the returned results of the pivot table. One way to do this is to overlay the returned results with named dynamic ranges. Please note, that if your pivot table has filters or is returned to a different column than what is shown in the sample, you may have to adjust the offset commands. It does not matter how many rows the pivot table returns.

Here is a more complete explanation of named dynamic ranges: http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges.

I'll explain the specific ranges used here
Lookup_Item =OFFSET(itemCodes!$B$4,0,0,COUNTA(itemCodes!$B:$B)-1,1)
What this name means is: on the ItemCodes sheet
- Start in cell B4
- Go down zero rows
- Go right zero columns
- From that point, give me a range COUNTA(B:B)-1 rows deep and 1 column wide

The reason for the minus 1 is that we literally do not want to count the header.

Lookup_Color =OFFSET(Lookup_Item,0,1)
This name means, same as Lookup_Item, only one column to the right. Lookup_Size and Lookup_Inventory have similar meanings.

On the order form sheet, I have helper columns in columns M:Q. These columns may be hidden.

Columns M&N merely "regurgitate" the Item and Color, The formula for O:Q is =SUMIFS(Lookup_Inventory,Lookup_Item,$M7,Lookup_Color,$N7,Lookup_Size,O$6)>=$H7
This formula uses SUMIFS to get the inventory using the named dynamic ranges and it compares it to the ordered quantity.

I extended these formulas down an arbitrary number of rows.

Then I put conditional formatting on the Small, Medium, Large columns extended down the same arbitrary rows. =AND($M7<>"",E7<>"",O7=FALSE). It looks at column M to see if there is a value there. It also looks at the current cell to see if you have a value there and then looks at the helper column to see if you have enough inventory to cover the order. I shaded the cells red it these conditions are met.

I think I might be able to arrange what you want with a data validation. You will be able to enter any quantity up to or equal to the amount in inventory. What I don't think I can do (without resorting to VBA) is tell you how much is in inventory when you enter the cell or try to enter too much. I'll take a look but I am wrapped up with a project at the moment.

You have complicated things immensely by using merged cells. I am going to propose a new form based on Tables.

I am using tables because tables automatically copy down formulas and validations.

The helper columns in Columns N:S calculate the amount of inventory for the Style, Color and Size. These columns may be hidden. Each cell under the size has a data validation that compares the number entered into the cell with the inventory. An error message pops up if you are trying to enter a quantity greater than is in inventory.

It is a custom validation with the formula: =N6>=E6 which means it will only allow entry if the amount in inventory is greater than or equal to the amount being entered. If not, a dialog box appears with the message "There is not enough inventory for the quantity being ordered."

Data validations are good, but they can be destroyed or bypassed with copy / paste.

I added an additional data validation on the Style #. You can only select styles that are on the master worksheet. I would also like to add data validation for color, but limit the color to those available for the style selected. However, to do this, I need access to the table so I can build a pivot table.

I would also do validation on the Names but I have no idea where you got those from.

I'll explain in more detail if you have questions.