Best way to get data options to a total report...Help, what formula do I need???

I need to enter this data:
Capture1.JPG


From these options and data:
Capture2.JPG


Which comes here as well:
Capture3.JPG


What is the best way to get all the shirts sizes and colors to fill in on the first picture/sheet to quickly fill the orders???

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?).

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

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

(note: this change is not optional )

And PLEASE include 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.

Originally Posted by RoosR

why will a picture of it not suffice? this is so difficult...i just need help please.....

Not putting a fine point on it, because nobody is going to take the time to type in all that data especially if you already have it. Otherwise the best we can offer is "Air Code" - meaning we think it might work, but without a workbook, it's untested.

Is it too difficult for you to attach the workbook here?

Let me see if I am interpreting the MDB Orders Sheet Properly. Each row is a customer, and the columns across are what the customer ordered. It looks like you also want to keep track of how much money was spent on each order.

And then what you want to do is summarize how much you sold.

First, I need to confirm that I understand the requirement correctly.

Are you willing to rethink the organization of this worksheet. I can envision a VB version where you fill out an order sheet, and click a button. The data are transferred to a normalized data table. I'll even record the data in case you want to track sales by week or month. If you had a table that has the price for each item, I could also do those calculations for you as well.

Once the data are in a normalized table, you can print invoices or run just about any report you could possibly think of using pivot tables.

Would you be willing to do this?

OK got it.

Here is what I came up with.

We will start with the Lookups sheet. This is where you define the sizes and colors for data validations. If you add a new size or color the data validation will expand to include it.

Next is the order form sheet. I tried to add a level of protection by protecting the sheet, but then the table would not work.

The tan fields contain formulas. Do NOT delete the data in these fields and do not enter anything into these cells. In fact, hide the rows and you won't do it accidentally.

Fill in the customer name, the method of payment and the amount paid in the green shaded cells.

Enter in a line number on row 14 and fill in the color size and quantity.

If there is another shirt to be ordered enter in the line number on row 15. Actually it doesn't matter what you enter in the cell in column A. The purpose is to force the table to open up a new row and copy down the drop down validations for columns B and C. Column D has a validation for a non-negative whole number.

You can enter in an unlimited number of line numbers.

When you click the Post Order button, you will be given one last opportunity to look at the order before committing it to the database. Once the order is posted, the Order Form is cleared.

The Post order feeds two tables: a shirt table and a paid table.

The shirt table keeps track of shirts ordered and the paid table keeps track of payments. The common link is the order number which may come in handy in the future if you need to coordinate payments with what was ordered. The date is also included in the tables, although it is not used now, but it might be important in the future. It's free, so why not add it in?


Both these tables are "normalized" data which means they are suitable for pivot table analysis.

The pivot sheet does that analysis The one in column A:B shows the total amount paid by each person - this could be broken out by date or by order.

The other pivot table does what I think you want the whole order form to do. This pivot table will grow as more orders are placed.

These are only two of the many possibilities, For example, you could pull up an employee and see what that person ordered or you could pull out colors and / or sizes and see who ordered them. You could do top 10 or bottom 10 filters to get the most / least popular ...

A couple of PS's

I left your old sheets in the workbook. I don't use them, so you can delete them if you wish.

There is very little "safety net" in the application. For example, I do not do a check to see if all the required fields are filled in. I could add some of these features but not until you confirm that this workbook does what you want.