Dynamic Form in Excel

another option for you.
i have added a macro that copies the data from sheet 2 to sheet3 but only if the order qty is > 0
it runs of a button, just fill in the information on sheet2 and hit the button.

the info will be copied to sheet3.
(the button is on sheet2 and 3, but it does the same thing)

hope this helps
let me know how you go

Thanks for the help. It looks like the validation list will work - but I've never done that before in Excel. The only problem is the Part # is completely meaningless to the person who is filling out the form, but means everything to the person shipping the order. I need to change the drop down list to the item description and then do the VLOOKUP on everything else.

I am new to the VLOOKUP function and was hoping that you can explain what the formula actually means. I know =VLOOKUP(Sheet3!A17,Sheet2!A8:G36,2,FALSE) where the first item is item that needs to be looked up (the part #) the 2nd item is the range of the lookup table (the inventory part # list) the 3rd item is the column # of the list and the last item I don't have a clue what it means.

I couldn't get the macro to work because of the security on their system. I'm just a temporary contract employee and they have everything very limited for me.

Thanks again for the great suggestions.

Lisa

Oh, one more thing that I need to consider is the drop down list will work fine for now, but when the part # list becomes larger it won't work too well. Is the macro the only other way to do this - since the form will be emailed and w/ the security restrictions I will have to talk w/ IT about how to allow attachments w/ macro's enabled to these departments. Also, to consider is the form will be used by sales people, warehouse and shipping employees, whose knowledge of Exxcel is limited (the sales people wanted me to do it in Word - NOT) and do not have MS Access.

I was thinking that I could just find a formula to do this and it would be simple, but maybe not. The formula I was thinking of was If the range on Sheet 2 in column E is > zero then fill with the values of the row row on Sheet 2 in the first row on Sheet 3.

Earlier I had tried an advanced filter and VLookup and couldn't get either of them to work. Although Pharm hand was able to do the VLookup, but not sure how it would work without a drop down list or if it's even possible.

Thanks again!

Lisa

I am new to the VLOOKUP function and was hoping that you can explain what the formula actually means. ... the last item I don't have a clue what it means.

The last item tells Excel what to do if there is not an exact match. False means you want an exact match or to return nothing (an error value).
In general, to get help on a function that is already in a cell ... if you have the fx button on your toolbar, press it; if not, click Insert >> Function and the function dialog will come up for that function. You can click in any of the function entries to see a short description, or click the "Help on this function" link at the bottom left to see a longer explanation of the function and its parameters.

but when the part # list becomes larger it won't work too well

I will need to look at the workbook (which I haven't done yet) to see. And if it is not set up to automatically grow as the list grows, I'll tell you how to change it. In principle, it's pretty simple to have the list automatically grow without using a macro. You use a Name for the range, and set the Name's referTo as a the OFFSET formula. Don't know if that will work in this case; depends on whether you have the same list item multiple times, in which case a macro might be the best solution. But, it is not the only solution ... there might be trade-offs to consider ... macro vs. an attractive list.

OK, I see why the macro. And, here is your trade-off ...
You only want the list to populate with items that are in-stock (column E is a number greater than 0 and not blank), right? Not possible without a macro. (Or, at least, let me say, it is beyond my knowledge how to do that.)

But, before deciding that you need a macro, let me make an argument that what you say you want is not really what you want. If I'm an employee, and I know we carry this item, I am going to be very upset if I do not see it on the list. I would rather see it in the list, but get some clue that slaps me in the face and tells me that the item is out of stock. That's what I have implemented, using a drop-down list in column B, and conditional formatting in column D.

Instead of VLOOKUP, I used MATCH and INDEX. Why? Two reasons. Reason One is that VLOOKUP requires that the column you are using as the look up basis is the first column in the array and everything else is to the right of it. But, your Sheet2 has Part# to the left of Oracle Description. You could change Sheet2 to fix that problem. But, why bother? Reason Two is that MATCH and INDEX has more flexibility, so I use it all the time and I never use VLOOKUP unless someone explicitly asked me to show them how to use it.

To make the drop-list grow automatically with the Sheet2 data, I based the validation list on the OFFSET set formula, as mentioned in the earlier post. If you look at the validation formula for cells in column B, sheet3, you will see that the list is "=Oracle_Description". To see what that means, click on Insert >> Name >> Define, then click on the Name "Oracle_Description" and look at the "Refers To" at the bottom of the dialog box. You will see this formula:
=OFFSET(Sheet2!$B$9,0,0,COUNTA(Sheet2!$B:$B),1)

Purist would tell you that this formula is incorrect because it counts too many rows (since you have some text above the table in column B on Sheet2. That assessment is correct; but, the validation is set up to ignore blank rows at the bottom of the list, so Excel is automatically compensating for my laziness.

Hope this is closer to what you need. If not, tell the customer they need to accept a macro!

You are kind of right - I didn't explain myself very well I guess. When the employee places the order they already know what it is stock. This form is not to track inventory at all - it is basically a list of part #'s and descriptions and the reason I don't just want to do a list where the employee will fill in the qty of the item ordered is that it is several pages when shipping prints it. I only want shipping to see what is being shipped NOT every item on the list. So the employee filling out the form will see the entire list of part #'s, fill in the qty the customer wants to buy and then shipping will print out the shipping sheet (Sheet3) and it will only show them the ship to address and ONLY the items that need to be shipped.

It seems like this is harder than it should be. I know it's totally do-able in Access, but they don't have it. It looks like the drop down list or a printed form that is several pages (even though only a few items are being shipped) are the best possible options so far. I really appreciate all the suggestions and have learned a lot of different methods that I never even thought of.