Filling in data on a certain date in Excel

Microsoft Excel 2002 (full product)
August 24, 2010 at 11:53:29
Specs: Windows XP
Hello,

I am wanting to be able to enter invoice information into the first sheet of my excel file. This info will include order number, client, product name, date ordered, estimated ship date, and actual ship date.

Once the information is entered, I would like to have a calendar in excel format be automatically updated with the three different dates and the order information.

For example, let's say that I enter the following: Order number: 6894, Client: Bob, Product name: 5500 Series, Date ordered: 08/24/2010, estimated ship date: 09/30/2010, actual ship date: 10/03/2010. I would like the order number, client, and product name to show up on each of the three dates. So, on 8/24/2010 it would say ORDER RECEIVED, Order #: 6894, Client: Bob, Product: 5500 Series. On 9/30/2010, it would say ESTIMATED SHIP DATE, Order #: 6894, Client: Bob, Product: 5500 Series. And so forth.

What would be the best way to go about doing this? I am using Microsoft Excel 2002. Thank you very much for your help!


See More: Filling in data on a certain date in Excel

Report •

#1
August 24, 2010 at 12:28:53
Do you already have a "calendar in excel format"? To be honest, I'm not even sure what a "calendar in excel format" is.

Excel is not a "format", it's an application.

Before we can offer any help, we would need to know how you expect your calendar to be laid out.

While you're working on that, consider this question also:

What do you expect to happen if more than one event occurs on the same day?

For example, what if 3 orders all come in on 08/24/2010?

What if an order comes in on 9/30/2010 which also happens to be the ship date for the order that came in on 8/24/2010?

I'm not quite sure how you would structure a calendar on a spreadsheet, not knowing how many entries could occur on any given day.

Do you have general idea as to how you would want to handle multiple events?


Report •

#2
August 24, 2010 at 13:11:58
Thank you for your reply. I have a calendar laid out in excel right now, and each month is a separate sheet. Each day of the month has 1 cell for the date and 5 cells that are blank (to use for entering data). We usually do not have several orders on the same day, although if we did, I was thinking that instead of having the client name and product name on there, I could just have the order number and link the number to the file that the order form is on. That way, I could just open up the calendar and see that on 8/24/2010 order number 8800 came in and then I could just click on the 8800 and it would load up the .doc that contains the order information. Maybe the three date categories could be differentiated by using different colored cells or something to save space in case multiple activities did take place on the same day. Thanks again for your help!

Report •

#3
August 24, 2010 at 13:40:20
If you already have a calendar set up, you're going to have to tell us, in detail, how it is laid out.

Since the only solution I can think of is by using a macro (VBA) we're going to need to know how the calendars are laid out before we can write any code that will place the data in the correct cells.

We are also going to need to know the layout of the order entry sheet so we know where to get the data from.

However, there is one major drawback to to a VBA solution provided via a forum such as this:

Who is going to maintain the code for you?

Let's say we write code to place the data for a single order on a given day. Then a few months from now you realize that more than one order is being placed on certain days or orders are being placed on days that other orders are shipping, etc.

That is going to require modifications to the code, even perhaps a complete re-write. Unless you plan to learn how to write VBA code, or hire someone who does, I'm not sure that you want to base your entire order entry system on code that you got from a free-help forum.


Report •
Related Solutions


Ask Question