Creating a document tracker...
 

I'm sending this out there in the hopes that someone who has vast more Excel
knowledge can help...

I am attempting to create a master tracking sheet that I can use at work for
the various things that I need to keep an eye on for my job.

I track three basic types of documents: Applications, Checks, and Mailings.
What I would love to set up is a series of options that opens up in the
following columns depending upon the document type selected.

For example, I sent an "application" out on X date, I want the next column
to have a list of common applications, and then the following column to
populate the date to follow up (which is always three days out).

On the row underneath however I could enter that I sent a check to corporate
on X date and a similar but different set of options would populate such as
type of account deposited into and amount.

The date for follow up calls is pretty constant at three days, so I am sure
I can figure out how to create a formula for a column that pre-populates the
date+3 to follow up.

If someone would please just point me in the right that would be great. So
far, all my Google searching has got me to creating lists within a column by
using data validation. I hope that this is on the right track!

Thank you in advance for anyone who responds. I greatly appreciate it as
getting this put together would save my work life a whole lot of stress as I
currently have four different trackers for four different types of documents!

~genevieve

Answer: Creating a document tracker...
 

Hi Genevieve,

It sounds like you're on the right track with using data validation to create lists within a column. Here are some steps you can take to set up your master tracking sheet:

1. Create a new Excel workbook and name it something like "Document Tracker".
2. In the first row of your worksheet, create column headers for the following: Document Type, Document Name, Date Sent, Follow-Up Date, Account Type, Amount.
3. In the "Document Type" column, use data validation to create a drop-down list of your three document types: Applications, Checks, and Mailings. To do this, select the cells in the "Document Type" column, go to the "Data" tab in the ribbon, and click on "Data Validation". In the "Settings" tab, choose "List" as the "Allow" option, and then type in your three document types separated by commas in the "Source" box. Click "OK" to close the dialog box.
4. In the "Document Name" column, use data validation to create a drop-down list of common applications, check types, or mailing types depending on the document type selected in the previous column. To do this, you'll need to create separate lists for each document type. For example, if the document type is "Applications", you could create a list of common applications like "Job Application", "Credit Application", etc. To create the lists, you can use the "Data Validation" dialog box again and choose "List" as the "Allow" option, but this time you'll need to use a formula in the "Source" box that references the appropriate list based on the document type selected. Here's an example formula for the "Document Name" column if the document type is in cell A2:
   Formula:

   ---

   =IF(A2="Applications",ApplicationsList,IF(A2="Checks",ChecksList,IF(A2="Mailings",MailingsList,""))) 


   ---

   . Replace "ApplicationsList", "ChecksList", and "MailingsList" with the actual range names of your lists.
5. In the "Date Sent" column, you can simply enter the date that the document was sent.
6. In the "Follow-Up Date" column, you can use a formula to calculate the follow-up date based on the date sent. For example, if the date sent is in cell C2, you could use the formula
   Formula:

   ---

   =C2+3 


   ---

   to add three days to the date sent.
7. In the "Account Type" and "Amount" columns, you can use data validation to create drop-down lists of account types and amounts depending on the document type selected, similar to step 4.
8. Finally, you can format your worksheet to make it easier to read and navigate. For example, you could use conditional formatting to highlight rows where the follow-up date is approaching, or you could use filters to sort and filter your data by document type, date sent, or other criteria.

I hope this helps you get started on your master tracking sheet! Let me know if you have any questions or need further assistance.

Creating a document tracker...
 

Sounds like you're on the right track. Data Validation to create your
initial list is probably a good way to go.
You might have a look, if Google hasn't led you there already, at
http://contextures.com/xlDataVal02.html
If that doesn't exactly fit your bill, look at
http://contextures.com/tiptech.html
down in the D's for various other data validation methods.

As for your 3-days out issue, the formula is pretty simple: just add 3 to
the date you entered for the date of the initial action. Assuming that date
was entered into cell A4, then the formula would be =A4+3, but a better one
to keep weird dates from appearing before you actually enter an activity date:
=IF(A4="","",A4+3)

"~genevieve" wrote:

I'm sending this out there in the hopes that someone who has vast more Excel
knowledge can help...

I am attempting to create a master tracking sheet that I can use at work for
the various things that I need to keep an eye on for my job.

I track three basic types of documents: Applications, Checks, and Mailings.
What I would love to set up is a series of options that opens up in the
following columns depending upon the document type selected.

For example, I sent an "application" out on X date, I want the next column
to have a list of common applications, and then the following column to
populate the date to follow up (which is always three days out).

On the row underneath however I could enter that I sent a check to corporate
on X date and a similar but different set of options would populate such as
type of account deposited into and amount.

The date for follow up calls is pretty constant at three days, so I am sure
I can figure out how to create a formula for a column that pre-populates the
date+3 to follow up.

If someone would please just point me in the right that would be great. So
far, all my Google searching has got me to creating lists within a column by
using data validation. I hope that this is on the right track!

Thank you in advance for anyone who responds. I greatly appreciate it as
getting this put together would save my work life a whole lot of stress as I
currently have four different trackers for four different types of documents!

~genevieve