Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

Vlookup with Validity cell

Hello everyone. I have been working at this issue for a while. I currently have a workorder book for my small business that I use for billing. It is made up with a sheet titled 'workorder' and a second sheet titled 'materials'. The focus of my question has to do specifically with the material section of my 'workorder'. This section is divided into four columns: Item, Serial Number, Quantity, Price. Item column is a validity list from the 'materials' sheet, allowing me to select from the list of materials I use (about 1200 entries). Serial Number is broken and is what I am trying to solve. Quantity is obvious for the quantity of materials used. Price has the formula =IF(ISNA(VLOOKUP(A21,$'Material Stock'.A$3:E$1197,5,0)),"",VLOOKUP(A21,$'Material Stock'.A$3:E$1197,5,0)*F21) - This allows me to select an item from the drop down menu, add a quantity and a price will auto fill into the cell. My problem is that I have such a long list of materials at this point, it is miserable trying to scroll through all of them to find the one I want. My goal is to have both the ability to use the drop down menu or input a serial number and have the cells fill out. My dilemma is that when using validity in a cell, you are not able to add my vlookup formula to it. I am thinking I will need to find a macro that will auto-choose from the drop down based on input from the Serial Number cell.

click to hide/show revision 2
None

Vlookup with Validity cell

Hello everyone. I have been working at this issue for a while. I currently have a workorder book for my small business that I use for billing. It is made up with a sheet titled 'workorder' and a second sheet titled 'materials'. The focus of my question has to do specifically with the material section of my 'workorder'. This section is divided into four columns: Item, Serial Number, Quantity, Price. Item column is a validity list from the 'materials' sheet, allowing me to select from the list of materials I use (about 1200 entries). Serial Number is broken and is what I am trying to solve. Quantity is obvious for the quantity of materials used. Price has the formula formula

=IF(ISNA(VLOOKUP(A21,$'Material Stock'.A$3:E$1197,5,0)),"",VLOOKUP(A21,$'Material Stock'.A$3:E$1197,5,0)*F21)  - Stock'.A$3:E$1197,5,0)*F21)

This allows me to select an item from the drop down menu, add a quantity and a price will auto fill into the cell. My problem is that I have such a long list of materials at this point, it is miserable trying to scroll through all of them to find the one I want. My goal is to have both the ability to use the drop down menu or input a serial number and have the cells fill out. My dilemma is that when using validity in a cell, you are not able to add my vlookup formula to it. I am thinking I will need to find a macro that will auto-choose from the drop down based on input from the Serial Number cell. cell.

Vlookup with Validity cell

Hello everyone. I have been working at this issue for a while. I currently have a workorder book for my small business that I use for billing. It is made up with a sheet titled 'workorder' and a second sheet titled 'materials'. The focus of my question has to do specifically with the material section of my 'workorder'. This section is divided into four columns: Item, Serial Number, Quantity, Price. Item column is a validity list from the 'materials' sheet, allowing me to select from the list of materials I use (about 1200 entries). Serial Number is broken and is what I am trying to solve. Quantity is obvious for the quantity of materials used. Price has the formula

=IF(ISNA(VLOOKUP(A21,$'Material Stock'.A$3:E$1197,5,0)),"",VLOOKUP(A21,$'Material Stock'.A$3:E$1197,5,0)*F21)

This allows me to select an item from the drop down menu, add a quantity and a price will auto fill into the cell. My problem is that I have such a long list of materials at this point, it is miserable trying to scroll through all of them to find the one I want. My goal is to have both both the ability to use the drop down menu or input a serial number and have the cells in the row fill out. out (material name and price based on qty). My dilemma is that when using validity in a cell, you are not able to add my vlookup formula any formulas to it. I am thinking I will need to find a macro that will auto-choose from the drop down based on input from the Serial Number cell.