SchnikeesOK
New member
- Joined
- Apr 21, 2011
- Messages
- 6
- Reaction score
- 0
- Points
- 0
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
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