Self updating schedule based on worksheets

[SchnikeesOK]

Hello,
I've inherited a workbook that includes 6 worksheets. Each worksheet represents one of our salespeople. The worksheets are very basic and do not include formulas. The columns have headings like "Customer Name" "Project Description" "Work Order #" And each row represents a different customer.

I am trying to make a 7th worksheet that will be our installation schedule. It will pull all of the customer information from the other 6 worksheets and be sorted by the "Installation Date" cell on those other worksheets. But if there is not a value in the "Installation Date" cell on their worksheets, then it shouldn't show up on the schedule.

Here's the formula from cell A2 on my schedule

=IF(Sarah!M48="","",Sarah!M48)

If Sarah enters a value in M48 (the "Installation Date" cell), then that date shows up in A2 on the schedule. And then to fill in the rest of row A, I use formulas like this depending on what info I need from Sarah's worksheet:

=IF(A2="","",Sarah!F48)

If the schedule has a date in A2, then I want the info from Sarah's F48 in this cell.

I like that the data entered into the sales worksheets is automatically updated on the schedule. I don't like that I have to resort the schedule when ever a new install date is added. I have to select the worksheet and "sort by" column 1 anytime a new row shows up.

Also, when the salespeople delete a job, I get the #REF! message on my schedule. Should I train them to write over an old job instead of deleting it?

Bonus Question - is there a way to make my schedule a template with date headings like "Monday, May 2, 2011" and have the customers information show up under their appropriate date heading whenever they are entered?

And I wonder if I should be using MS Access for this, but I don't know how to work it and I don't think all of the sales force has Access.
Thanks for your help.
Will

[Jon von der Heyden]

Hi Will

Welcome to Excel Guru forums

It be easier to advise you if you attach a small sample. It might be worth using a macro here to consolidate the data instead.

From what I can see however you might want to avoid direct references in favour of other methods. For instance you can return a list of dates, ordered ascending, using the SMALL worksheet function. So if your dates are in column M of the Sarah worksheet then:

=SMALL(Sarah!M:M,1)

The above will yield the smallest date from column M.

=SMALL(Sarah!M:M,2)

The above will yield the 2nd smallest date from column M.

Conversely you can use the LARGE worksheet function to return the dates ordered descending.

So for a dynamic list of dates you can exploit the ROW function. The following example entered in A2 of your consolidation sheet and copied down:

=SMALL(Sarah!A:A,ROW($2:2))

The next step would be to use VLOOKUP's for instance to return the values for the remaining fields. It's hard to suggest how exactly which is why I suggest you provide a sample.

Hope this helps.

[SchnikeesOK]

Thanks for the help Jon.
I've attached a small version of what I've been working with. The sheet labeled "beta" is my cumbersome attempt, but it does update itself. And the sheet marked "schedule" is the format I'm going for, but I have to update it by cutting and pasting from the other worksheets.
Thanks again for your input,
Will

[Ken Puls]

So... question for you...

Are you married to the approach of having every user get their own sheet for data entry?

The reason I ask is that if you could change the data entry point to a single table for all records, then you could hang a variety of Pivot Tables off the source table to show the views any way you like. So you could have a page for Emily, Sarah, Holly and Krystalyn that is always in sync with the source table. You could also then work your schedule out from the main table as well.

I've attached a really rough go at it, which consolidates your data on one worksheet (adding a column for the clerk), and done a very rough repro of the Emily worksheet. The formatting would need to be done to make it look prettier, but that's minor.

The bonus here is that you could write a smalll macro to update all pivot tables in the workbook very easily, keeping the output sheets in sync with the data.

It does take bending your mind to the logic though, but if you can do it, this might be the easiet way to accomplish what you're after.

[SchnikeesOK]

Wow Ken, that is very interesting. Your version will enable me to do a lot more with this and other data, things I hadn't even thought of.....
Regarding the schedule, how would I go about pulling the data so I could produce an installation schedule that only pulled certain select info, and it was formatted by days of the week.

Monday, June 6th

Project No - - Customer - - etc
Project No - - Customer - - etc
Project No - - Customer - - etc

Tuesday, June 7th

Project No - - Customer - - etc
Project No - - Customer - - etc

[Ken Puls]

I'm out tonight, so will need to follow up with you tomorrow on this if someone else doesn't get there first.

[Ken Puls]

Okay, so here's the steps I used to set up a report similar to what you described. (This is in Office 2007... I'm not sure what version you're using.)

1. Go to the data table and select a cell in it
2. On the Insert tab, click PivotTable
3. Verify the range covers the entire table and choose to create it on a new sheet
4. In the field list (should show at right) do the following
   1. Drag Install Dates to the row labels field
   2. Drag WPRP.O.# to the row labels field (under install dates)
   3. Drag Cutomer to the row labels field (undder WPRP.0.#
   4. Add whatever other fields you like there too
5. Go to the PivotTable Tools - Design tab, and
   1. Choose Report Layout -> Show in Tabular Form
   2. Choose Subtotals -> Do not show subtotals
   3. Choose Grand Totals -> Off for rows and columns
6. On the pivot table itself, change the install dates drop down (cell A4 if you used default settings) and uncheck "Blanks" from the list
7. The last thing I did is hide the last 6 columns of the report as they contain the data area, which we are not using

Again, whenever you update the original source table, you come back to this tab, right click the pivottable and choose "Refresh" to update it. You can also uncheck other items from the date field to only show the items you want.

Bascially, that's the gist of PivotTables. Try creating a few and dragging items in/out of the row labels fields to see how it looks. If you want to count/sum things, then put those items in the Values field.

(And if you need the pre-2007 steps, let me know.)

Cheers,

[SchnikeesOK]

I am using 2002, yikes. Those pre-2007 steps would be helpful.
Thanks!

[Ken Puls]

I am using 2002, yikes. Those pre-2007 steps would be helpful.

Sorry for the late reply here. Things have been a bit nutty.

These steps are for 2003, which I believe should be consistent with 2002. It's not as pretty, but you'll get the idea:

1. Go to the data table and select a cell in it
2. On the Data menu, click PivotTable and PivotChart Report...
3. You should have "Microsoft Office Excel list or database" pre selected, as well as "PivotTable". Click Next
4. Verify that the range covers your entire data table
5. New worksheet and Finish
6. In the field list (should show at right) do the following
   1. Drag Install Dates to the row labels field
   2. Drag WPRP.O.# to the row labels field (drop it on the line between the row fields area and the data area)
   3. Drag Cutomer to the row labels field (beside WPRP.0.# )
   4. Add whatever other fields you like there too
7. For each column
   1. Click somewhere in the column
   2. On the PivotTable Toolbar click PivotTable
   3. Uncheck PivotTable-> Subtotals

Cheers,

[SchnikeesOK]

Wow, thanks a bunch Ken, this is working out very well. The PivotTable was the way to go.
There is one thing that seems strange to me, how come I can change the data in the PivotTable cells directly? A couple of times I made the mistake of manipulating the data in the PivotTable instead of the actual data source table. Once I did that to a particular cell, the changes I made in the source table were no longer reflected in that cell in the PivotTable. Is there a way to lock the Pivot Tables so they can only be altered by changing the source data table?
Thanks again for all the help.