Hello, I am trying to create a production calendar. I want one sheet of the work book to have the following columns: Customer, PO #, Model #, and Ship Date. This way, I can sort by any of these fields. The next sheets should be calendars, by month, for the rest of the year. I would like to set it up so that the ship date from the first sheet will pull all of the pertinent information for that transaction(Customer , PO #, Model #, and Ship Date) to the appropriate date on one of the calendars.
So, if I entered
Customer: PO# Model # Ship Date
Abc company 123 B 3/7/2007
Then on the calendar, on March 7th, the above line of information would appear on that date.
Help please! Thank you so much for your expertise!
2007-02-07 10:52:49 · 3 answers · asked by humble.earthling 2 in Computers & Internet ➔ Software
Yikes. I don't know what VLOOKUP codes are. I am a novice...
2007-02-07 11:24:25 · update #1
You can use pivottables for that. Pivottables will generate the calandar for you. The left column of the pivotables will show the customer details. The top rows will show the shipping dates. See example below
2007-02-07 13:54:56 · answer #1 · answered by unnga 6 · 0⤊ 0⤋
I agree with Steve, you would be much better off using Access. It can be done in Excel, but it would be really hard for you to create and maintain if you don't have that much experience. Not to mention, a complete formatting mess.
If you must have it in Excel, forget the calendars. Use only one sheet, and filter or pivot on the date column to look up future ship dates and the corresponding customer information.
If you have a lot of business, you're going to run out of rows in Excel, so you maybe want to consider taking an Access class and investing in the software.
2007-02-07 12:23:10 · answer #2 · answered by xytzews 1 · 0⤊ 0⤋
Every cell in each of your calendar would have to use a combination of VLOOKUP formulas ... the formula should reference the array of orders (or potential orders) on your first sheet, and be looking for the date for that cell in the orders array in the first sheet.
It might be a bit of a hassle to get all set up at first.. but probaly worth it in the end.
Just realised that this is a less than ideal solution as it will only return the first matching date, if you have more than one order shipping on the same date, then you wont get both.
better off using a database like access to accomplish what you want...
2007-02-07 11:06:09 · answer #3 · answered by Steven K 1 · 0⤊ 0⤋