Dynamic Form in Excel

I need to do a form in Excel and want to condense it to one form for several
departments to use instead of a separate one for each. I was thinking of
having a sheet with the customer data (name address, etc) a sheet with the
inventory data and a sheet showing just the shipping data which pulls from
the customer and inventory sheets. I am doing just a link for the customer
data which works just fine, but don't know the best way to handle the
inventory info. On the inventory data sheet it is just a table of every
part # available, description, qty, cost, price. On the shipping sheet I
only want to include the inventory items w/ qty's to be shipped. Is this
possible in Excel?

I was told that I could use VLookup or Conditional Formatting to do this, but I can’t figure it out.

Here’s what I have

Sheet 1 contains customer name and address

Sheet 2 contains a list of part #’s and names w/ a blank column to enter the qty.

Sheet 3 is the order which is a link to sheet 1 showing only the customer shipping address AND (this is the part I can’t figure out) the part #’s and names that have a qty greater than zero.

I have Excel 2003. Thank you in advance for your help.

How do you tie the customer information with the product information?

Or does each workbook have only data pertinent to a single customer?

If you could post a workbook that has a little bit of dummy data in it, it would be a lot easier to be helpful, I think.

Just save the workbook as a zip file before you try to upload it to this forum.

Each workbook is for a separate customer. The form will only be used when a customer places an order. Sheet 3 should look just like a simple order form, but with so many inventory items I only want the inventory items that are ordered to show on sheet 3. This will keep the form down to one page (they are doing this manually) and prevent shipping errors.

Included is a sample of the form.

Thanks for your help.

Lisa

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.

Lisa,

After re-reading your requirements, I got a better grasp of what is was you are doing, and I realized that everything you are asking for can be accomplished with the use of simple filters and all on one tab. Check out the attached file. One tab has all the items, with the number ordered moved to the right of the unit price. You change the number ordered, and the shipping costs, and then either manually change the filter to hide the items not ordered, or click the "Hide Items not Ordered" button. The form is already formatted to print. It will run to multiple pages if necessary, still showing only the items ordered and repeating the headers at the top of the page (you can try this by ordering one of each item, hitting hide, and then printing the preview). When you want to reverse this and see everything again, just click "show all" and it untoggles the filter. All this can be done manually with the filters also, but it's not as nifty.

Second issue: unavailable items. There is a another filter for available items. you can click the "Hide Unavailable" button to hide the items which can't be shipped because of lack of inventory. The "show Unavailable" button untoggles that and shows all again. You can do all these things with the autofilter buttons if your boss absolutely insists on no macros.

Now, how's that? Granted, not fancy, but I think this may do what you want with a minimum of fuss.

Erik

Just so you could see the file, I deleted the macros. You can set the filter over Number ordered to be "greater than" 0 and that will hide the empty rows. You set the filter for this to all to show everything again.

For the unavailable items, you can set the filter over Available to "all" to see everything, whether it's available or not, and you can set the filter to "greater than" 1 to show the items in stock.

The macros which are used in the previous post manage all this for you.

Regards,
Erik

This is an excellent post and makes you realise how much we depend on macros to do things!!!!

undoubtedly, the filters etc are the only way to go if macros cant be used, i personally always work on the principal that data should only be entered once, the rest should be an automatic copy / manipulation (but that only works with macros!) and the user does not have to intervene again.

the suggestion from MSP using INDEX and MATCH is powerful magic and is something i am sure i have seen in a "data validation" formula list some place (will have to trawl my notes!) but it is also something have never quite got mastered in my head.

May i request from MSP an explaination (in "normal english") of how these 2 functions work as i have also seen them combined with SUMPRODUCT to produce some very powerful formula's / data manipulations.

thanks

Two points of clarification:

1. The Match/Index pairing is NOT being used for the cell validation list (i.e., in-cell drop down box).

2. While I am a proponent of using Match/Index in place of VLOOKUP, I am no where close to being the "inventor" of this pairing. I see it suggested on this forum often. Using in a comment such as "for this you could use VLOOKUP or MATCH + INDEX".

Explanation of how MATCH + INDEX works ...
Excel has 4 functions for look up: Lookup, VLookUp, HLookUp, and Match.

Lookup is the least used because it requires that data be sorted and it returns a "close match" if the lookup does not find an exact match.

VLookUp can be used with a sorted list or a non-sorted list, and it has an option of returning a "close match" or only an exact match. If you choose the "close match" option, then the list MUST be sorted. If you choose the exact match option, the list does not need to be sorted.

HLookUp is similar to Vlookup, except you are looking across a columns in a single row (Horizontal) instead of down rows in a single column to find the match.

All of the functions with Lookup in the name make a match on one column (or row) and return results from another column (or row). Match is different. Match returns only the position in the lookup array where the match was found. It has the same options as VLookUp of returning a "close match" or only an exact match; and, it has the same requirements of VLookUp about sorting ... i.e., if you want a "close match", the data must be sorted; if you specificy an exact match, the data need not be sorted. Match can operate on a row (across columns, like HLookUp) or on a column (down rows, like VLookoUp). Therefore, Match is the most flexibly; but, it returns only the position in the array where the match was found.

To take a specific example, if you make the search range an entire column, then Match will return the row number where the first match was found (if a match exists).

Index has 2 formats. I will cover the simplier one. You give Index a range (containing one or more cells) and which member of the array you want, and it returns that member of the array. The range can be a row or a column. It does not work so well if the range is not continuous.

So, taking Match and Index together, you would use Match to determine where in the range the match was found; this gives you the row number (or more generall, the number of rows the match is offset from the first row in the search range); then you use Index on the column from which you want the corresponding entry and as the row number, you pass it the results of the Match function.

Thanks MSP for taking the time to explain in more detail, your explaination has helped clear the muddy water, I will practice a little with both of them as they certainly are more flexible than Vlookup and Hlookup.

now to Lisa's problem..............................

i have attached a file which shows the required information on sheet3.

the user just completes the numbers in sheet2 and does not need to do anything on sheet3 as it is automatically showing the items with an order qty >0.

all i have done is linked the cells from sheet2 to sheet3 and then applied a conditional format which looks at the cell in column E (qty) and if the value is <1 the font colour is set to white so it "disappears".........

its a very simple approach and produces a form which shows the desired information, although i have to admit i do not like forms like this as i hate to see blank lines, however, in the absence of macros its the only way i can think of that is automatic (ie requires no input on sheet 3) and can be emailed without any issues.

let me know if this gets you what you need..........